Floating FB popout byF5debug

SQL Server Integration Services (SSIS) – Part 44 – Export Column Transformations in SSIS

 

This is part 44 of the series of article on SSIS

Introduction:

In this article we are going to see on how to use the Export column transformation in SSIS packaging. Export column transformation task is used in cases where we need to read the data from the data flow in the package and save the information to a file. Say for example if we want to get some information of a product or an order to be saved in a file, like product image for sending mail to the user we can use this task. Unlike the other transformations this task does not require a destination task to create a file. Let’s jump start to the section on how to do that using a sample package.

You can look into my series of article on SSIS at the url - http://f5debug.net/tutorial/ssis.aspx

[more]

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 Export Columns control. Once you open the project just drag and drop the Export Column control and a source provider as shown in the below image.

clip_image001

Now configure the source provider by mapping to the correct database and the table as shown in the below screen

clip_image002

Now we need to configure the Export Column task, to configure double click on the control will open the window as shown in the below screen.

clip_image003

Here we have option to select the column where the path to be places as. You can also find checkboxes at the right side.

  • Allow Append – Will create a new file and add the data to the end of an existing file
  • Force Truncate – Will overwrite the file if it already exists.

Now once the package creating is completed, press F5 to start the build and the execution of the package. Once the package is executed you can find the screen as shown in the below image

clip_image004

Conclusion:

So in this article we have seen on how to use the Export Column Transformation to do some manipulation and transform data to a new column.

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

Comments (4) -

Shailesh
Shailesh
1/29/2013 1:31:43 AM #

Hi,

Thanks for the post. I need to export varbinary data to file. But, when I do it using Column Transformations in SSIS, the exported files are corrput. There are few junk characters at the start of the file. On removing them, the file opens fine.

A similar post for BCP, (http://bytes.com/topic/sql-server/answers/844661-bcp-out-image-files-not-working) says that these characters specify the data length.

Would like to know how to address this issue in SSIS?

Thanks

Sandeep
Sandeep
7/6/2013 12:45:21 AM #

Hi
Your post is so good.But I have one doubt,at the 3rd image, in Export Column you have written Homepage but in my case i can't write it because it is a drop-down.Help me..

Priya
Priya
10/23/2013 12:43:07 PM #

I have the same situation as Sandeep's

I also have a drop down for the Extract Column,
After selecting the File Path Column  and checking the Force truncate Option(Extract Column is empty as do not know how). I see a Error in the bottom of the Export Column Transformation Editor as "All rows ,except the last one,must have defined values "File Path Column" and "Extract Column"."

What can I do. Please let me know.

Thank You

Everett Ghil
Everett Ghil
8/31/2013 5:50:15 AM #

Way cool! Some very valid points! I appreciate you writing this post and also the rest of the site is really good.

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