SSIS Package: How to export data to CSV file

Share on FacebookTweet about this on TwitterShare on Google+Share on LinkedInShare on RedditShare on Tumblr

Tools used: SQL Server Data Tool for Visual Studio 2013 and Microsoft SQL Server 2014 Express

Create New Business Intelligence Project

Open SQL Server Data Tools and create a new Business Intelligence Project. Navigate to File > New > Project and select Integration Services Project from Template > Business Intelligence > Integration Service. Name the project ExportCSV and choose the location where to save it.
SSIS Package - New Project

Design Control Flow

From the SSIS Toolbox drag the Data Flow Task to the design surface of the Control Flow tab. This is the design area in SSIS Package where you handle the flow of operations. Right click on the task and rename it Export to CSV.
Control Flow

Design Data Flow

Double click to go to Data Flow design area. This is where you can extract, transform and load the data. The information is extracted using Data Flow Sources like OLE DB Source, Raw File Source, Flat File Source, Excel Source etc. After this you need to apply different transformations in order to comply with business rules. Finally the data is written using Data Flow Destination like OLE DB Destination, Flat File Destination, Excel Destination, DataReader Destination, ADO NET Destination etc.
Drag the Source Assistant. A new window opens and you have to select an already existing connection or to create a new one. Select SQL Server from the left side (Select source type) and double click on New (Select connection managers).
Destination Assistant
This will open a new window where you have to set up the following information: Server Name and Log on to the server option.
Connection Manager OLE DB
After fill in, test connection to make sure that everything is correct. The message will be Test connection succeded.
Rename the task MsSQL Database Source and double click on it to configure the properties used by the Data Flow to obtain data from the OLE DB provider.
By default the OLE DB connection manager is set (to one that you recently created). If not, please select one. Choose the Data access mode. If you are using the SQL command access mode, specify the SQL command either by typing the query or by using Query Builder. In this example I choosed Table or View. Next step select the table that you want from the list below. Click Preview… in order to check if the retrieved information is correct. At the end press OK button.
OLE DB source editor
From SSIS Toolbox section Other Destinations drag Flat File Destination. Connect the Blue coloured arrow mark from Source data to Flat File Destionation.
Data Flow
As you can see now the Flat File Destination Task has a small red error icon. In order to fix this you have to configure the connection manager for a flat file. In this case a CSV file. Double click on it to open Flat File Destination Editor.
Flat File Destination Editor
Create a new Connection Manager. The File Flat format has to be Delimited – the columns are delimited by commas, except the last one which is delimited by new line character.
Flat File Format
Select an empty CSV file and specify the file properties and file format. Mark the checkbox for Column names in the first data row.
Flat File Connection Manager Editor
In Advanced section you can configure the properties of each column. As best practice click on Suggest Types… This will help you select the correct Data type based on retrieved sample data.
Test the package to see if data is extracted to CSV.

SSIS Package Configuration

The final step is to set the SSIS package configuration by adding a dtsConfig file. This will allow you to change package settings (SQL connection string, initial catalog, server name, username, password, CSV file path and many others) without ever opening it with SQL Data Tool.
Go to Control Flow, right click and select Properties (Alt+Enter). In Properties panel select Configuration …
Package Properties
This will open Package Configuration Organizer. Check Enable package configuration and add a new configuration file. Click Add… button, choose the location where to save the file, type the name of the file and press Save button.
Package Configuration Organizer
Choose XML configuration file, press Next and select the properties you want to configure using the dtsConfig file.
Package Configuration Wizard
Package Configuration Wizard Properties to Export
dtsConfig XML

Execute SSIS Package

Please read the following article that describes how you can execute a SSIS Package using DTEXEC utility (SSIS Tool).