How to Import the Files from a SQL Server Table to the File System using SSIS Package

SSIS Package to Import the Files from the SQL Server Table to a Folder using Export Column Transformation
The Export Column Transformation in SSIS is used to export binary files, images, media or any kind of documents(DT_TEXT, DT_NTEXT,DT_IMAGE) from SQL Server Table to the File System/Folder. The Export Column Transformation reads the File Path from the Column and Exports the Files to this Path from the Column where the File are stored in Binary Format.
The Export Colum Transformation works exactly opposite to Import Column Transformation
Scenario :
Suppose we have a Source Table "Tbl_FilesStorage" having the File Path and File details as follows..

Here in FileName column having the Fully Qualified File Name ;

FileObject Column having the File details with binary format.

Now if we use the Export Column Transformation, then it will Export the Files to below Folder Path :

T:\My_Tech_Lab\SSIS_Lab\SSIS_Proj1_Sol1\Source\MyFiles\

Now lets design the Package to fulfill the above scenario.

--------------------------------------------------------------------------------------------------------

Phase I : Add the Data Flow Task to Control Flow and Design the Flow

--------------------------------------------------------------------------------------------------------

A) Add the Data Flow to the Control Flow

B) Add the OLE DB Source Inside the DataFlow

 C) Next make the Database Connection and Select the Source Files Table

 D) Next select the Source Columns FileName (having File Path) and FileObject (having File details )

Next Say OK.

--------------------------------------------------------------------------------------------------------

Phase II : Next Connect the Export Colum Transformation to OLE DB Source and do the Colum Mappings

--------------------------------------------------------------------------------------------------------

A) Add and Connect the Export Column Transformation to OLE DB Source

B) Next map the Source Columns at Export Column Transformation Editor 

Here..

Extract Column: For this column we will map the text or image or binary data column.

In this example, We selected FileObject because we are exporting the Files from this Column of SQL to a File System.

File Path Column:  For this column we  will map the source Column which has the File Plath, whre the files will be saved.

Allow Append: This property/option specifies whether you want the Export Column Transformation to add data to existing files or not. The default is false, you can change if requires.

Force Truncate: This property/option specifies 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.

Please note that we can use either one of the option "Allow Append" or "Force Truncate".

Write BOM: This property/option specifies 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.

Next Say OK.

C) Finally Run the Package

--------------------------------------------------------------------------------------------------------

Output :

Now the Files have Exported from SQL Table to a Folder as follow..

--------------------------------------------------------------------------------------------------------
Thanks, TAMATAM ; Business Intelligence & Analytics Professional
--------------------------------------------------------------------------------------------------------