Create and write in text file with SSIS

Question

I am totally new to the SSIS. I have a requirement to create a text file on specified location and write the data in it which is selected from SQL table.

How to do that?

Is there any example which will help me?

Answer 1

The below steps should help you to get a basic SSIS package working:

1. Create new Integration Services Project in Business Intelligence Development Studio. There should be a default SSIS package in it called Package.dtsx. Open the package.
2. In the bottom of the screen, there should be an area for connection managers. Right click on it to get a list of different connection managers.
3. Click on New OLEDB Connection. This will be the connection manager for your source database. Set the data source (server or instance name), authentication mode and credentials and the initial catalog (database name).
4. Again right click in the connection managers area and select New Flat File Connection.This will be the connection manager for the destination flat file. You can create a text file (.txt or .csv) and add in the headers for your data e.g. EmployeeID,EmployeeName,EmployeeSalary. Check the "column names in the first data row" checkbox to make sure your headers are identified as such.
5. Now select a Data Flow Task and drag it to the Control Flow screen.
6. Open the Data Flow tab. Drag 2 items from Toolbox to the screen - OLEDB Source and Flat File Destination.
7. In the Source, use the OLEDB connection manager created earlier. Then change the Data Access mode to SQL command. Now you can type your own command to select data. Make sure that the header data in your flat file is the same as the column headers from your select query.
8. In the Destination, use the Flat File connection manager created earlier. Go to the Mappings section and map the columns from your query output to the headers in your text file.

Now save and run the package. You should see that the destination text file is populated with data from your query.