SQL Server Integration Services (SSIS) - Export Column Transformations in SSIS

Introduction:

In this article we are going to see how to use the Export column transformation in SSIS packaging. The Export column transformation task is used in cases where we need to read the data from the data flow in the package and save the information to a file. Say for example if we want to get some information of a product or an order to be saved in a file, like product image for sending mail to the user we can use this task. Unlike the other transformations this task does not require a destination task to create a file. Let's jump start to the section on how to do that using a sample package.
You can look into my series of article on SSIS at the url - http://f5debug.net/all-articles/

Steps:

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on an integration services project. Once the project is created, we will see how to use the Export Columns control. Once you open the project just drag and drop the Export Column control and a source provider as shown in the below image.


Now configure the source provider by mapping to the correct database and the table as shown in the below screen:


Now we need to configure the Export Column task, to configure double-click on the control; that will open the window as shown in the below screen.


Here we have option to select the column where the path to be places as. You can also find checkboxes at the right side.

• Allow Append – Will create a new file and add the data to the end of an existing file
• Force Truncate – Will overwrite the file if it already exists.

Now once the package creating is completed, press F5 to start the build and the execution of the package. Once the package is executed you can find the screen as shown in the below image

Conclusion:

So in this article we have seen how to use the Derived Column Transformation to do some manipulation and transform data to a new column.