Import column transformation example SSIS

Using import column transformation data can be loaded from file system task (content of a file) to database or any other destination adapter.  For example:

Let's assume we have a table named document. We want to load content of file to vcData column according path specified in other column (vcDocumentName). 

CREATE TABLE tblDocument (

     ntDocumentID BIGINT

     ,vcDoucumentName VARCHAR(100)

     ,vcData NVARCHAR(MAX)

)

INSERT INTO tblDocument

VALUES

     (1,'Math',NULL)

     ,(2,'Che',NULL)

     ,(3,'Phy',NULL)

Step 1:

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

Import column source Adapter

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

Import column transformation Physical Path 

Step 3: Drag import column transformation and connect with derived column transformation. Double click on it which will pop up advance editor.  

SSIS Import column input file path

In Input columns tab select column which keeps file path.

Step 4: Now click on input and output properties tab. In the output Columns node add a new column. In this example it is vcFileData and selecting DT_NTEXT as a data type from drop down. Copy the LinenageID which is 297 in this example. 

Getting file content Import Column SSIS

Step 5: In input columns node, select vcDcoumntName (columns which keeps file path) and enter the lineage ID of the output column in FileDataColumnID as shown in below diagram:

Linking File Path to File Data SSIS Import Column 

Step 6: Drag OLEDB command and connect with import column transformation. Create a new OLEDB connection. Double click on OLEDB command transformation. Under the connection managers tab select new OLED connection from drop down.

Updating File data SSIS Import Column 

Step 7: Under Component Properties tab enter SQL Command:

UPDATE tblDocument

SET vcData = ?

WHERE ntDocumentID = ?

As shown in below diagram: 

Update query to Update with file data SSIS Import column

Step 8: Under column mapping tab, map the destination column with input column.

Column Mapping Import column transformation SSIS 

Step 9: Now execute the package!!

Run Import column Transformation SSIS

And check your table:

ntDocumentID	vcDoucumentName	vcData
1	Math	I am learning mathematics.
2	Che	I am learning chemistry.
3	Phy	I am learning physics.