Export Column Transformation in SSIS

Fig 1: The Export Column Transformation

In this post I will be covering the Export Column Transformation. The sample package can be found here for 2005 and guidelines on use are here.

What does it export, and to where?

It exports Binary Data – which means things like Images, Documents and other media – which have been stored in a relational database. It exports them out to the file system. In the example package, I demonstrate exporting image data from Adventureworks into a file folder.

Fig 2: Configuring the Export Column Transformation

There’s not much to configure – you need to decide which column(s) you are going to export to the file system, and which column you will use to specify the full file path and file name (you cannot just set a folder globally and use a file name only). MSDN shows the full range of outcomes if you set the Allow Append or Force Truncate flags, but essentially:

• Allow Append – Will create a new file if none exists, otherwise it will add the data to the end of an existing file
• Force Truncate – If a file exists, it will overwrite it

You also get the option to set a Byte Order Mark (or BOM), which as far as I can tell is a Unicode end of file marker, but if anyone can enlighten me, please do so.

The other thing to note is that this component can be the terminating point of a data flow – an output is available but not compulsory.

When would you use an Export Column?

The main use for this would be for extracting items stored in the database, or for placing them as files as you move them from point to point in or between data flows.