How to save data of field in a file of given column path SSIS: Export column transform

Export columns transform is used to store text\image\binary content as a file at given location in different column of a table. For example:

Step 1: Create a new table and populate with some data:

CREATE TABLE tblDocument (

     ntDocumentID BIGINT

     ,vcDoucumentName VARCHAR(100)

     ,vcData VARCHAR(MAX)

)

INSERT INTO tblDocument

VALUES

     (1,'Math','I am learning mathematics.')

     ,(2,'Che','I am learning chemistry.')

     ,(3,'Phy','I am learning physics.')

Step 2:

Drag any data source adapter in data flow task area. In this example in is ADO .NET source adapter. It is connected with Sql server:

Export column source adapter

Step 2: It is option step. In this step we are going to concatenate physical file path in the column named vcDoucumentName by using derived column transformation:

SSIS Export column file path

Step 3: Now drag export column transformation and connect it with derived column transformation. Double click on it and will pop up export column transformation editor. In column tab:

 

Extract column: From drop down choose a  column which keeps content of file.

File Path column: from drop down choose a column   which keeps physical path of the file. 

Allow append: If it is selected and file is exists then it will appended the data in same file.

Force truncate: If it is selected and file is exists then existing will be deleted and a new file will be created. 

Export column SSIS configuration

Now execute the package. You will find for each row there is a file at specified location:

Run export column SSIS