SQL Server Integration Services (SSIS) - Import Column Transformation in SSIS

Introduction:

In this article we are going to see how to use an Import Column transformation in SSIS. Import column is used to import data from a file to the data flow and do some manipulations and then forward the data to the destination; here the data includes a binary file, an image, a media, or any sort of document which is huge to transfer. If we want to move a huge file from one location to the other using the package we can use this to import them using this transformation.
A practical exposure to this transformation is for example you have a product with some customer reviews; when we need to archive the product (include product image) with the customer reviews then we can use this task. Let's jump start to the actual use and see the steps to do the configurations.
You can look into my series of articles on SSIS at the url - http://f5debug.net/all-articles/

Steps:

Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on an integration services project. Once the project is created, we will see how to use the Import Column Transformation task. Once you open the project, just drag and drop the Import Column control and a source provider as shown in the below image.


Now we need to create a table which has the file location. I have created a table as shown in the below script with a record having the file path.

Scripts:

Create table ImportColumnSample
(
PhotoName varchar(50),
Photolocation varchar(200)
)
Insert into ImportColumnSample  values('File1′,'D:\Books\Book1.pdf')
Select * from ImportColumnSample
Now we need to configure the source provider as shown in the below screen:


Once the source is configured we are ready to handle the import. Now we need to configure the import column transformation. To configure that, double-click on the control' that will open the configuration window as shown below:


There are 3 different tabs we need to configure. Let's see the different sections of configuring each and the import sections that should be done when we configure.
The first tab has the basic information about the transformation on having a unique id for the transformation; the name and description we have no need to take care of.
The second tab looks like below:


This is the most important configuration; we need to select the exact path where the file is located. Here Photolocation is the file path where the file is located.
Now if you see the main screen it will show as if an error is in the package as shown in the below screen:


This is mainly because we have not configured the output column upon which the transformation has to happen. So double-click once again the Import column transformation. It will open the same window, now navigate to the 3^rd tab as shown below:


Here navigate to the Import Column Output in the tree view and select Output Columns and then click on Add Column. Here we only need to create an output column as shown in the below screen:


We need to note 2 things in the above image; LineageID is autogenerated, in our example it generated it as 144 and the Name which we have given is sampleoutputcolumn.
Now move to the ImportColumnInput and navigate to the path as shown in the below screen and we will find a property FileDataColumnId and give the value 144 which its generated in the output column as shown in the below screen:


Once this configuration is over then we are ready with the data flow which has the file and the location where it resides. Now your screen looks like below:


We can use any destination as per our business to access the file and do the necessary transformations across the flow. If we run the process now it will execute the package and show the output as shown below:

Conclusion:

So in this article we have seen how to use the Import Column task and the key configurations used in order to use this task handy.