Home > SSIS > Export Data to files – Export Column Transformation

Export Data to files – Export Column Transformation


In my previous article Import column transformation, I have explained how to load data from file to SQL Server database using SSIS.

Today, it is time to export stored data in the database back to files. To do so, I am going to us Export Column Transformation in SSIS.

Export Column Transformation

Export Column Transformation reads DT_TEXT, DT_NTEXT, or DT_IMAGE data from database and write it a file with the name and path given in the another column of the same row. The transformation can be used to overwrite and append existing files.

This transformation could be used to create file such binary, text, and Unicode text. It is very simple to implement. So, the source table should have file name and the content to write.

Scenario

Read “import data from files” article before reading this. I have already stored the text content with the file name in a table called BookStore (As given in the previous article). Now, I am going to design a package to read the data from the table and write data to files in my destination folder.

Implementation

Step 1: Create a package and Add OLE DB source task and connect to BookStore table.

Step 2: Add Derived Column task to customize file path. Because, I have stored only file name and file content in the source table.

Step 3: Add Export Column task and open the Export Column Transformation editor.

  1. Extract Column should have the column that contains file content.
  2. File Path Column should have the column that contain file name.
  3. If we do not check any of the check box in the row then it creates new file else execute based on the option selected.

Step 4: Execute the package.

Output is in F:\Testing\

Thanks for reading.

Advertisements
  1. Eloi
    June 5, 2014 at 7:12 am

    Thanks for your article. It has been very useful for me.

  1. June 24, 2015 at 9:34 am

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: