Export Data to Flat File using SSIS Package

This SQL Server SSIS tutorial shows how to export data stored in database table into a flat file by using SSIS package. Using SQL Server Business Intelligence Development Studio (BIDS), SQL Server BI developers can easily create SSIS package to transfer table data to a text file.

SQL Server Integration Services enable Business Intelligence developers to to automate tasks like to export data, to import data, etc. Microsoft Business Intelligence Development Studio (BIDS) provides a Visual Studio IDE environment for developers to create reusable packages (SSIS packages) that will be executed by SQL Server Integration Services.

Let's create SSIS package using BIDS and execute this package to export data from SQL Server database table into a text file (or flat file).

Open SQL Server Business Intelligence Development Studio.
I've SQL Server 2008 R2 Business Intelligence Development Studio installed on my computer so I'll be working on SQL Server 2008 R2 and its tools for this SSIS example.

Create new SSIS project following BIDS menu options "File > New > Project..."
Then choose "Integration Services Project" from Visual Studio installed templates. Name the SQL Server Integration Services project as "SSIS Tutorial".
The Integration Services project will create a default empty SSIS package titled Package.dtsx under SSIS Packages folder in Solution Explorer window. You can rename it by a right click on the SSIS package and choosing Rename in context menu.

On the left side of the Business Intelligence Development Studio, you will see a Toolbox containing tools developed for specific tasks. These tools can be configured for solutions of common tasks like bulk insert, executing sql scripts, executing FTP commands, send mail, back up database, rebuilding index, or even shrink database task.

Since our aim in this SSIS tutorial is to export data from SQL Server to flat file, we will choose Data Flow Task
Double click or drag-drop Data Flow Task object on to the Control Flow surface of the SSIS package in design view.

data flow component in SSIS package for data export

When you double click on Data Flow Task object on design view a new tab "Data Flow designer surface" will be active to configure data flow task. First we'll configure data source component, which is SQL Server database in our SSIS tutorial. Since we will directly export data from SQL Server table to text file without any transformation, we will not use any of the Data Flow Transformation tasks. As last step, we need to identify the destination component which is the flat file in our sample SSIS package.

Let's configure source component as SQL Server for sample SSIS package.
Choose OLE DB Source component from Tools and drag-drop it onto Data Flow designer surface.

OLE DB Source component in SSIS package for data export

Double click on OLE DB Source object to configure the data source for reading. I will not demonstrate here how OLE DB Source object will be configured for database connection to SQL Server. SQL Server BI developers can easily create new SQL Server data connections for the data source to export to flat file.

I'm using SQL Server 2008 R2 sample database AdventureWorks2008R2 for the SSIS package tutorial as data source.

I also plan to export all data stored in database table HumanResources.Department, I choose "Table or view" in the "Data access mode". It is also possible to define a SQL query as the source of export data task by choosing the "SQL command" data access mode.

data access mode for SQL Server data source

Continue the SSIS package source editor by selecting the table or view name

OLE DB Source editor screen enables developers to preview source data. BI developers can exclude columns from export list in Columns tab by clearing checkbox beside column names or rename the output column name

By pressing OK buton, the configuration of the OLE DB Source component is complete. This means we have configured data source for SQL Server export task. Now we can configure the destination component for exporting data from SQL Server to flat file or to text file.

Among Data Flow Destinations components in Toolbox, choose Flat File Destination for this SSIS tutorial which will get data from SQL Server and write it into a flat file in text format.

data flow destinations in SSIS package design

When the Flat File Destination object is displayed on the designer surface, connect two objects. You can create a connection between two objects from data source to flat file destination object. All you have to do is click on green arrow of OLE DB Source component and then to click on the Flat File Destination component.

export data from SQL Server to text file

After the connection is build, we can now continue to SSIS tutorial by configuring the Data Flow Destination component Flat File Destination. Double click on the item on Business Intelligence Development Studio design surface. We have to configure the Flat File connection manager. Choose New buton. In Flat File Format screen, you will be requested to choose flat file format among available formats: delimited, fixed width, fixed width with row delimiters, and tagged right.

I choose Delimited file format for target text file of the data export task. The next step is identifying the flat file name and path in Flat File Destination Editor screen.

export data to flat file using SSIS package

Point to the file folder and type the name of the flat file where you want to export SQL Server table data, and click OK to end SSIS package developing

An important configuration setting for Flat File Destination component is the overwrite settings. If you want to overwrite data which might be already in the text file when the export data task is executed, mark "Overwrite data in the file" checkbox. But if you want to append export data at the end of the flat file each time the SSIS package is executed, then make sure that you clear the "Overwrite data in the file" checkbox.

Now our sample SSIS package is completed. We can now test if we can export data in SQL Server database table to flat file in selected file folder. To test the SSIS package, use F5 (Start Debugging) or Ctrl+F5 (Start Without Debugging) options from Debug menu.

After the SSIS package execution in Business Intelligence Development Studio (BIDS), I see that data stored in sample database table is exported to text file HumarResources-Department.txt in target folder. As you see SQL Server Integration Services services along with Business Intelligence Development Studio will help SQL Server developers provide easy and fast solutions for many operational tasks like exporting data from SQL Server to flat file. I hope SQL Server BI professionals like this SSIS tutorial.