Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SSIS Output columns

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.

like image 354
w0051977 Avatar asked Sep 01 '12 10:09

w0051977


People also ask

How do you derive columns in SSIS?

To start using the Derived Column transformation, create an SSIS package. Next, drag a Data Flow component in the Control Flow, double-click the Data Flow component and drag a data source component. Then, configure the data source to connect to your database. Finally, add a Derived Column transformation component.

What is external columns and output column in SSIS?

In simple words: External Columns: represent the metadata of external data sources. Output Columns: represent the metadata of the columns used within the data flow task.

How do you derive a column in SQL?

Select a derived column from the list. Choose whether to add the derived column as a new output column, or to replace the data in an existing column. Type an expression or build one by dragging from the previous list of available columns, variables, functions, and operators.

How do I use Findstring in SSIS?

FINDSTRING works only with the DT_WSTR data type. character_expression and searchstring arguments that are string literals or data columns with the DT_STR data type are implicitly cast to the DT_WSTR data type before FINDSTRING performs its operation. Other data types must be explicitly cast to the DT_WSTR data type.


1 Answers

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
like image 78
Piotr Sobiegraj Avatar answered Sep 19 '22 21:09

Piotr Sobiegraj