Welcome to EMC Consulting Blogs Sign in | Join | Help

SSIS Junkie

SSIS: The difference between output columns and external columns

The following question was asked today on the SSIS forum:

"What is the difference between external and output columns?" The poster seemed to be under the impression that external columns were another form of output column - that is not the case.

Output columns are used by all data-flow source adapters and transformations. They are not used by destination adapters. Put simply, they represent the metadata of the data flowing out of the component.

External columns represent the metadata of external data sources and destinations. As such, only source & destination adapters have external columns because these are the only components whose buffered pipeline data interacts with these external sources and destinations.

 

So why are they both there? Well, its simply so that SSIS can validate the metadata of the data-flow against the external sources/destinations so that it knows at design-time whether or not the data-flow will have problems in processing data between these sources/destinations and the SSIS data-flow.

Here's a simple example. Imagine a scenario where a DT_STR in the data-flow was attempted to be inserted into a column in a database table of type INT. If SSIS were not aware of the metadata of that external table then it would not be able to warn the developer that the package will fail upon execution. You can try this for yourself - you will get a warning at design-time. Something like:

"Warning 1 Validation warning. Populate OrderDetail: OrderDetail [53]: The external metadata column collection is out of synchronization with the data source columns. The column "OrderHeaderID" needs to be updated in the external metadata column collection. 20060518Split_OrderHeader_OrderDetail.dtsx 0 0 "

Bob Bojanic replied to the original thread as well with some extra information: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=419393&SiteID=1.

Hope that helps.

-Jamie

 

Published Tuesday, May 23, 2006 9:09 PM by jamie.thomson

Comments

 

SSIS Junkie said:

This is a post I've been meaning to write for some time but as you may have noticed my blogging output

October 9, 2007 4:39 AM
 

thomas said:

Any solution for viewing external columns cross different databse with SSIS? thanks

December 25, 2007 4:58 PM
 

jamie.thomson said:

thomas,

Sorry, I'm afraid I don't understand the question.

-Jamie

December 28, 2007 4:54 PM
 

Tom said:

Another case where the error message doesn't help much.  "Out of Synchronization?"  How about - "Hey buddy, check your datatype mapping..."

July 16, 2008 10:26 PM
New Comments to this post are disabled

This Blog

Syndication

Powered by Community Server (Personal Edition), by Telligent Systems