Tutorial Gateway

Export Column Transformation in SSIS

by

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

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

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

Export Column Transformation in SSIS Example

In this SSIS example, We will export images from the SQL Server table to a local hard drive. The 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 in SSIS 0

The Export Column Transformation in SSIS 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.

Export Column Transformation in SSIS 1

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

STEP 2: Drag and drop OLE DB Source, Export Column Transformation from the toolbox to the 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.

Export Column Transformation in SSIS 2

STEP 3: Click on the 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

Export Column Transformation in SSIS 3

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

STEP 4: Double click on SSIS Export Column Transformation to edit and configure. It opens the window, as shown in the below screen.

Export Column Transformation in SSIS 4

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 of SSIS Export Column Transformation.

  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 the local hard drive.
  2. File Path Column: Select file paths or file names from the list of available input columns. It is the path where files 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, and you can change it 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, and you can change if you require by checkmark 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 only print if the data has the DT_NTEXT or DT_WSTR data type.

Let’s look at our destination path before we run the SSIS Export Column Transformation package

Export Column Transformation in SSIS 5

Let us run the Export Column Transformation in SSIS package

Export Column Transformation in SSIS 6

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

Export Column Transformation in SSIS 7

Well, we did it.