Export Column Transformation in SSIS

Export Column Transformation in SSIS is useful to export images, binary files, media or any sort of large documents from SQL Server to file system. Export Column Transformation reads the path or location present in the source column and transfer the data to that particular path.

For example, if we have product description stored in the text file and the text file is stored in one column. We have a situation to send product description only to any specific store then, We can use the Export Column Transformation to transfer and save those text files to the path we specified.

Export Column Transformation is exactly opposite to Import Column Transformation, Please refer Import Column Transformation in SSIS article to understand How to Import Images, Text file or any huge file to SQL Server Database.

Export Column Transformation in SSIS Example

In this example, We will export images from the SQL Server table to local hard drive. Following SQL table is the one we are going to use in this example.

SELECT [PhotoID]
      ,[PhotoSource]
      ,[Photo]
FROM [SSIS Tutorials].[dbo].[PHOTOSOURCE]

Export Column Transformation will store the images present in the Photo Column and store them in the location or path specified in the [PhotoSource] column.

STEP 1: Drag and drop the data flow task from the toolbox to control flow and name the data flow task as Export Column Transformation.

Double click on data flow task will open the data flow tab.

STEP 2: Drag and drop OLE DB Source, Export Column Transformation from toolbox to data flow region.

Double click on OLE DB source in the data flow region will open the connection manager settings. Here we are selecting [SSIS Tutorials] Data Base and PHOTOSOURCE table as OLE DB Source as shown below.

STEP 3: Click on columns tab to verify the columns. In this tab we can uncheck the unwanted columns also.

Here we are unchecking the PhotoID key column. Because there is no point in saving the Identity column as output at this time

Click ok, and Drag and drop the blue arrow from OLE DB source to Export Column Transformation to perform the transformations on the source data.

STEP 4: Double click on Export Column Transformation to edit and configure. This 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 check boxes at the right side.

1. Extract Column: Select text or image data columns from the list of available input columns. In this example, We selected Photo because we are exporting images from SQL to local hard drive.
2. File Path Column: Select file paths or file names from the list of available input columns. This is the path where file or images will be saved (Here Images).
3. Allow Append: Specify whether you want the Export Column Transformation to add data to existing files or not. The default is false, you can change if requires.
4. Force Truncate: Specify whether you want the Export Column Transformation to delete any of the existing files before writing the new data. The default is false, you can change if you require by check mark the option.
5. Write BOM: Specify whether you want the Export Column Transformation to write a byte-order mark (BOM) to the file. A BOM is only written if the data has the DT_NTEXT or DT_WSTR data type.

Let’s look at our destination path before we run the package

Let us run the Export Column Transformation in SSIS package

Let’s see the destination folder whether we succeeded or not.

Well we did it.

Thank you for Visiting Our Blog