Export Column Transformation in SSIS

-
b
Fig 1: The Export Column Transformation
In this post I will be covering the Export Column Transformation. The sample package can be found here for 2005 and guidelines on use are here.

What does it export, and to where?

It exports Binary Data – which means things like Images, Documents and other media – which have been stored in a relational database. It exports them out to the file system. In the example package, I demonstrate exporting image data from Adventureworks into a file folder.
b
Fig 2: Configuring the Export Column Transformation
There’s not much to configure – you need to decide which column(s) you are going to export to the file system, and which column you will use to specify the full file path and file name (you cannot just set a folder globally and use a file name only). MSDN shows the full range of outcomes if you set the Allow Append or Force Truncate flags, but essentially:
  • Allow Append – Will create a new file if none exists, otherwise it will add the data to the end of an existing file
  • Force Truncate – If a file exists, it will overwrite it
You also get the option to set a Byte Order Mark (or BOM), which as far as I can tell is a Unicode end of file marker, but if anyone can enlighten me, please do so.
The other thing to note is that this component can be the terminating point of a data flow – an output is available but not compulsory.

When would you use an Export Column?

The main use for this would be for extracting items stored in the database, or for placing them as files as you move them from point to point in or between data flows.

Comments

Post a Comment

Popular posts from this blog

SharePoint 2007 - Simple Task Dashboard

-
Image
example of creating a simple Task Dashboard. This is a pretty basic example of using all OOTB (out of the box) features and combining them to quickly build a solution. Scenario: We want to provide a way for users to easily see a summary of the current project tasks. We want them to be able to see the following information: Open Tasks Assigned to Them Overdue Tasks Tasks Due This Week Task Calendar Tasks Assigned to Others Solution: To build this solution we will be using several different tools available in SharePoint, all included in the list below: Task List Task List Views We will create views for each of the different ways we want to see the data displayed on our dashboard. Web Part Page List View Web Parts Custom Navigation The first thing we will do is create our task lists and then configure all the views that we need. To create the list, go to View All Site Content, Create and then select the Task list. Next, we will need to configure our custom views. To do this, w
Read more

MERGE transformation in SSIS

-
Image
In this post we are gonna discuss about MERGE transformation. MERGE in SSIS is equal to UNION ALL in SQL Server. This transformation unions two datasets/tables.The merge Transformation combines two  sorted  dataset into single dataset. Highlighted the text SORTED in last statement because “It is not possible to use MERGE when the inputs are NOT SORTED”. There is one more transformation which is very similar to this i.e UNION ALL. The Merge Transformation is similar to the union all transformations. Use the union all transformation instead of the merge transformation in the following situations. The transformation inputs are not sorted The combined output does not need to be sorted. The transformation has more than two inputs. MERGE takes ONLY TWO inputs where as UNION ALL can take more than two. Now lets see how to configure MERGE transformation with an example. I created TWO tables with names MergeA and MergeB and inserted few records into each table as shown below.
Read more