Floating FB popout byF5debug

SQL Server Integration Services (SSIS) – Part 47 – Import Column Transformation in SSIS

 

This is part 47 of the series of article on SSIS

Introduction:

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.

[more]

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

 

Steps:

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.

clip_image001

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.

clip_image002

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

clip_image003

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

clip_image004

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

clip_image005

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

clip_image006

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

clip_image007

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

clip_image008

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

clip_image009

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

clip_image010

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

clip_image011

Conclusion:

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.

About Author: Karthikeyan Anbarasan, Microsoft MVP (Most Valuable Professional) in ASP.NET/IIS Architecture. He is the Founder and Chief Editor of the www.f5debug.net website and has authored books on Windows Phone and Business Intelligence(SSIS). He is also a Passionate Speaker and a Blogger on Microsoft Technologies.

You can Join Me On: Facebook, Twitter, Google Plus, LinkedIn

Add comment

  Country flag

biuquote
  • Comment
  • Preview
Loading

Recent Posts

Random Posts

Most Viewed

URL Shortner

Create your own short urls!!!

Site Counter

free counters

Disclaimer

This is a personal weblog. The opinions expressed here represent my own and not those of my employer. For accuracy and official reference refer to MSDN/ TechNet. I have documented my personal experience on this blog.

Protected by Copyscape Web Plagiarism Finder

Site Meter