If you are new to SSIS as I am, you have likely experienced confusion regarding the SSIS metadata types.  I often run into metadata mismatches between column data types in SQL Server and the data type in SSIS.  Fortunately there is a simple way to determine the data types associated to columns in SSIS.  In addition, I have found a few resources online that have helped me resolve issues with data type mismatches.

Determining the Data Type of a Column in the SSIS Data Flow

The following diagram represents a simple data flow in SSIS:

To view the columns and associated data types in the data flow, right click on a line connecting the data flow tasks.  The following menu  is displayed.  Click ‘Edit’. 

The ‘Data Flow Path Editor’ will launch.  Next, click ‘Metadata’ in the panel on the left hand side.

The path metadata is displayed.

Mapping SSIS Column Data Types to Database Data Types

Once you know the SSIS column data type, there are several good online resources that assist in mapping SSIS data types to the compatible data types in SQL Server (and other RDBMS).

The following website provides a table that maps the SSIS data types to the respective data base data types.  It also provides an explanation of the SSIS data types.  http://msdn.microsoft.com/en-us/library/ms141036.aspx

comments powered by Disqus