This is part 47 of the series of article on SSIS
In this article we are going to see on how to use Import Column transformation in SSIS. Import column is used in order to import some 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 handle across. 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 say 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 go with this task. Let’s jump start on how to use this task in real time and see the steps to do the configurations.
You can look into my series of article on SSIS at the url - http://f5debug.net/tutorial/ssis.aspx
Follow steps 1 to 3 on my first article to open the BIDS project and select the right project to work on integration services project. Once the project is created, we will see on 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.
Create table ImportColumnSample
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 now in place to handle the import. Now we need to configure the import column transformation. To configure that double click on the control will open the configuration window as shown below
There are 3 different tabs we need to configure. Let’s see the different sections on how to configure each and the import sections that should be taken care when we configure.
The first tab has the basic information on about the transformation on having a unique id for the transformation; name and description which we no need to take care of.
Second tab looks like below
This is the most important configuration where we need to select the path where the file is exactly 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 there 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 3rd 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 only we need to create an output column as shown in the below screen
We need to note down 2 things in the above image, LineageID this is autogenerated in our example it generated it as 144 and the Name which we have given it as sampleoutputcolumn.
Now move to the ImportColumnInput and navigate to the path as shown in the below screen and we can 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 get 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
So in this article we have seen on how to use the Import Column task and the key configurations used in order to use this task handy.