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 |
No comments:
Post a Comment