SSIS Output columns

7
votes

I am not clear about the difference between Output Columns and External Columns in SSIS. I have looked for explanations online but I am still unclear. For example, this blog entry: http://consultingblogs.emc.com/jamiethomson/archive/2006/05/23/SSIS_3A00_-The-difference-between-output-columns-and-external-columns.aspx and this question: http://social.msdn.microsoft.com/forums/en-US/sqlintegrationservices/thread/f5fa8785-46cc-44e1-9251-8503d2725523/.

For example, if you have an SQL OLEDB source with this query:

SELECT ID, Name FROM Person

The results are then written to a text file. Then there is an output column and an external column for each. On some web pages it says the external columns hold meta data. For example, if Name is a VARCHAR(30) in the SQL OLEDB data source and it is a VARCHAR(50) in the text file then what are the values for the output columns and external columns?

Can anyone point me to an MSDN article that clearly explains the differences? I have searched for over an hour this morning.

1
sql-serverssis

1 Answers

4
votes

I haven't found whole MSDN article about it, but I think that fitst figure of this MSDN article shows it clearly.

In general data source have external columns which store information about data structure in source and output columns, which store structure of information it sends through data path. It copy data from external columns to output columns. Data destination have input columns, which store information about structure of date it receives from data path and external columns which describes structure in destination. It copy data from input columns to external columns.

In you case it should be:

Source:

External columns: 
Name Type     Length
Id   DT_I4    0
Name DT_STR   30

Output columns:
Name Type     Length
Id   DT_I4    0
Name DT_STR   30

Destination:

Input columns:
Name Type     Length
Id   DT_I4    0
Name DT_STR   30

External columns:
Name Type     Length
Name DT_STR   50