Apr 20, 2015

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
     (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

No comments: