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.
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.
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).
This will open a new window where you have to set up the following information: Server Name and Log on to the server option.
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.
From SSIS Toolbox section Other Destinations drag Flat File Destination. Connect the Blue coloured arrow mark from Source data to Flat File Destination.
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.
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.
Select an empty CSV file and specify the file properties and file format. Mark the checkbox for Column names in the first data row.
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 …
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.
Choose XML configuration file, press Next and select the properties you want to configure using the dtsConfig file.
Execute SSIS Package
Please read the following article that describes how you can execute a SSIS Package using DTEXEC utility (SSIS Tool).