SSIS Lookup Transformation for SQL Server datetime type and DT_DATE

How do you perform a Lookup transformation using an input column of type DT_DATE (e.g. from a spreadsheet input) and a SQL Server table column that has the datetime type? Dragging the former onto the latter results in the error message "One or more columns do not have supported data types, or their data types do not match".

The answer is to create a Data Conversion transformation earlier in the flow. In this task, convert your input column to the type "database timestamp [DT_DBTIMESTAMP]". Make a note of the output alias you have entered. Now go back to the Lookup transformation and drag the new column (i.e. <output alias> of the Data Conversion transformation) onto the datetime column in the lookup table.

 

Advertisements
This entry was posted in SQL Server. Bookmark the permalink.

One Response to SSIS Lookup Transformation for SQL Server datetime type and DT_DATE

  1. Arun says:

    Thank you for the valuable information of Lookup transformation. You will get more information regarding the API and programming stuffs for Lookup transformation for SSIS in the below link.http://www.sqllion.com/2009/05/programming-lookup-transformation/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s