How to Export Data From SQL Server to Flat File in SSIS?

Last Updated : 06 Jun, 2021

In this article, we will learn how to Export Data From SQL Server To Flat File in SSIS. For this, we are going to create a package to select the data from the SQL server database and export SQL Server table data to the Flat File in the local drive.

Prerequisites:

Make sure Visual Studio 2019 is installed.
Ensure that SQL Server Management Studio is installed.
Create a table and insert some data into it in the SQL server as shown below.

Creating Tables & Adding Data:

Create a sample table named “Employee” using the below query:

CREATE TABLE EMPLOYEE
(
EmpCode   INT,
EmpFName  VARCHAR(15),
EmpLName  VARCHAR(15),
Job     VARCHAR(45),
Manager   CHAR(4),
HireDate  DATE,
Salary   INT,
Commission INT,
DEPTCODE  INT
);

Now you can add data to the above created Employee table using the below query:

INSERT INTO EMPLOYEE
VALUES (1, 'ROB', 'STARK', 'SOFTWARE ENGINEER', 7902, '2012-12-17',20),
  (2, 'JON', 'SNOW', 'SALESMAN', 7698, '2014-02-20', 30),  
  (3, 'SANSA', 'STARK', 'MANAGER', 7839, '2011-04-02', 20),
  (4, 'CATEYLN', 'STARK', 'SALESMAN', 7698, '2010-09-28', 30),
  (5, 'ARYA', 'STARK', 'MANAGER', 7839, '2013-06-09', 10),
  (6, 'PAUL', 'SMITH', 'ANALYST', 7566, '2015-12-09',20),
  (7, 'ALFRED', 'KINSLEY', 'PRESIDENT', 7566, '2017-11-17',10),
  (8, 'ARNOLD', 'TIMOTHY', 'SALESMAN', 7698, '2019-09-08',30),
  (9, 'RAMIN', 'ASGHAR', 'SOFTWARE ENGINEER', 7788, '2020-01-12',20),
  (10, 'LUCY', 'FULLER', 'TECHNICAL LEAD', 7698, '2018-12-03', 20),
  (11, 'STEVE', 'FAULKNER', 'ANAYLYST', 7566, '2012-12-03',10),
  (12, 'KAREN', 'ROGERS', 'SOFTWARE ENGINEER', 7782, '2010-01-23',20),
  (13, 'BRUCE', 'LEE', 'SALESMAN', 7698, '2001-02-22',30),
  (14, 'NEIL', 'SWAN', 'MANAGER', 7839, '2005-05-01',30),
  (15, 'ATHENA', 'WILLIAMS', 'ANALYST', 7839, '2003-06-21',50),
  (16, 'MATHEW', 'HUETTE', 'ANALYST', 7839, '2006-07-01', 50);

Now let’s move on to the important part.

Creating SSIS Package:

Follow the below steps to create an SSIS package:

STEP 1: Drag and Drop the Data Flow Task from the toolbox to the control flow region. Add a name to it of your choice.

STEP 2: Double-click on the Data Flow Task. This will redirect us to a new window known as Data Flow the purpose of this is to load the data from source to destination. Drag and drop the OLEDB source and Flat File Destination from the toolbox menu.

Adding Configurations for Source:

Follow the below steps for configuring the Source:

Double-click on the OLEDB source a menu will pop up. Enter the Database server name in the connection manager then select the table which contains the data.

After selecting the following options we can then go to the Columns option. We can ensure whether the columns are loaded properly click the Ok option below.

Connect both the OLEDB source and Flat file destination as shown below.

Adding Configurations for Destination:

Follow the below steps for configuring the Destination:

Double-click on the Flat File Destination a menu will pop up. Select as shown below:

Create a text file in a path to view the processed data. Now configure the path of the text file in the window.

Verify the Mappings option make sure it is the same as below and then click ok to continue.

STEP 3: We can now run the package with the run option present in the main menu. When we press the Start button then the package will start running, and you can view the status of your SSIS package.