Import Column Transformation in SSIS 2008R2 Example

The Import Column transformation reads data from files and adds the data to columns in a data flow. Using this transformation, a package can add text and images stored in separate files to a data flow. For example, a data flow that loads data into a table that stores product information can include the Import Column transformation to import customer reviews of each product from files and add the reviews to the data flow.

A column in the transformation input contains the names of files that hold the data. Each row in the dataset can specify a different file. When the Import Column transformation processes a row, it reads the file name, opens the corresponding file in the file system, and loads the file content into an output column. The data type of the output column must be DT_TEXT, DT_NTEXT, or DT_IMAGE.Now let start step by step, Here I am moving a text file from a flat file source to a table in the database.For the importing a text file into a database, we must have a table that hold the text file value and the path of the files. Let’s create a table named as Text_test.
Now take the Import Column and connect it to the Flat file source.
Configure the Import Column Transform
In the first page I mean Component Properties Tab you don’t need to do anything ,select Input Column tab where you will see source available columns check the filepath column.
Import Column Input is already set to the selected source columns input.
Select Import Column Output and where add an output column by clicking Add Column.
We have to do one important step here we have to put this LineageID 29 highlighted in above screenshot. Into the Import column Input’s Filepath. Now press ok .

Here I am having some text file and file called path, where it has all the files path.

Let start the BIDS and select a data flow task .

Click on the data flow task and then drag a Flat file source .

Now configure the Flat file source and connect to the file that has all the files path as I connected to path.txt which have all the files path.

You can see the data present in the file in Column tab.

Now select Input and Output Properties Tab where you will see these three option.

I created an output column with the name of TextFileColumn.

Now let’s configure the OLE DB destination and set the connection to the database where you created a table called Text_test.

Now mapping tab map the import Column output to the table Text_test Column and press “OK”.

Now execute the task.

The task executed successfully. Let’s check the output table in SSMS.

Here you can see all the files are saved to the Text files Column.

This is it guys .. Give a try !!