Can someone please explain the difference between Output and External columns? I can't fathom why "Output" columns aren't good enough. In other words, what is there a need or value in having two types of "output" columns?
TIA,
Barkingdong
Does this help: http://blogs.conchango.com/jamiethomson/archive/2006/05/23/3984.aspx ?
-Jamie
|||External columns help to make a snapshot of the external metadata. It is used as reference for future changes in the external source and internal metadata (output columns).
For example, let's say we do not have external columns and at certain point of time you have 2 output columns in the collection and 3 columns in the linked external table (or flat file, etc). That does not give us enough information to determine if we ignored one column at the source or the table was updated with an additional column.
With external columns we have a reference to compare against, and that allows automatic updates of changed metadata, through the ReinitializeMetadata mechanism, without disrupting the downstream components.
HTH.
|||Your answers (including Jamie's too) surprised me. But I need to slow you down a bit (that's the problem with experts, you know) to better understand your answer. Here are some of my thoughts. Please comment on any portion, in any manner, you wish.
1. So "external" columns relate to metadata used at design time to verify, among other things, that the source and destination mappings are in "sync" or appropriate.
2. I don't remember much discussion of metadata in sql 2000 DTS. Was it there but hidden, did I simply miss it, or is this a sql 2005 enhancement?
3. "...External columns help to make a snapshot of the external metadata. "
Suppose I have a Flat File Source Connector and an OLE DB Destination connector. What does "external" refer to here in SSIS talk? Are the "external columns" metadata references from the Flat File connector that refer to the inputs of the "external" OLE DB Destination connector?
4.I found this BOL reference:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/8f5bd3ed-3e79-43a4-b6c1-435e4c2cc8cc.htm
"
When a component is disconnected from its data source, you can validate the columns in the input and output column collections against the columns at its external data source by using the IDTSExternalMetadataColumnCollection90 interface. This interface lets you maintain a snapshot of the columns at the external data source and map these columns to the columns in the input and output column collection of the component.
Implementing external metadata columns adds a layer of overhead and complexity to component development, because you must maintain and validate against an additional column collection, but the ability to avoid expensive round trips to the server for validation may make this development work worthwhile.
"
That is interesting. Disconnected, ahh...
While I can drag and drop input/output fields between tasks, I gather that I wouldn't normally adjust (unless I was programming in .NET) external\internal metadata columns. In a sense I shouldn't be too concerned with metadata on a daily basis. It's part of the SSIS infrastructure but something that I generally won't touch.
TIA,
Barkingdog
|||barkingdog wrote:
Your answers (including Jamie's too) surprised me. But I need to slow you down a bit (that's the problem with experts, you know) to better understand your answer. Here are some of my thoughts. Please comment on any portion, in any manner, you wish.
1. So "external" columns relate to metadata used at design time to verify, among other things, that the source and destination mappings are in "sync" or appropriate.
Yeah, that's a good way of looking at it!
2. I don't remember much discussion of metadata in sql 2000 DTS. Was it there but hidden, did I simply miss it, or is this a sql 2005 enhancement?
Very much a sql 2005 thing.
3. "...External columns help to make a snapshot of the external metadata. "
Suppose I have a Flat File Source Connector and an OLE DB Destination connector. What does "external" refer to here in SSIS talk? Are the "external columns" metadata references from the Flat File connector that refer to the inputs of the "external" OLE DB Destination connector?
Nothing as sinister as that. Its quite simply the metadata of the thing that SSIS is going to connect to.
4.I found this BOL reference:
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/dtsref9/html/8f5bd3ed-3e79-43a4-b6c1-435e4c2cc8cc.htm
"
When a component is disconnected from its data source, you can validate the columns in the input and output column collections against the columns at its external data source by using the IDTSExternalMetadataColumnCollection90 interface. This interface lets you maintain a snapshot of the columns at the external data source and map these columns to the columns in the input and output column collection of the component.
Implementing external metadata columns adds a layer of overhead and complexity to component development, because you must maintain and validate against an additional column collection, but the ability to avoid expensive round trips to the server for validation may make this development work worthwhile.
"
That is interesting. Disconnected, ahh...
While I can drag and drop input/output fields between tasks, I gather that I wouldn't normally adjust (unless I was programming in .NET) external\internal metadata columns. In a sense I shouldn't be too concerned with metadata on a daily basis. It's part of the SSIS infrastructure but something that I generally won't touch.
That's a safe bet. Let SSIS do the grunt for you - you just use what it gives you!!
-Jamie
No comments:
Post a Comment