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