CODEWITHSUNDEEP
ssissql-server-2012
Stuart
Stuart

Reputation: 1602

SSIS Lookup Transform Fails on Date Column

We have a Lookup transform that does a date lookup. The input column data type is DT_DBDATE and so is the matching lookup column. Both are DATE data types in SQL Server 2012. This is an SSIS 2012 package.

The lookup works fine in BIDS (the designer). When deployed and run through the SSISDB UI or through our custom sproc, this lookup fails with the following error:

Error: Lookup Status Key.Inputs[Lookup Input].Columns[status_dt] and reference column named "status_dt" have incompatible data types.

We also see synchronization warnings around all of our DATE columns, those these warning don't result in a failure.

Has anyone else seen this behavior?

UPDATE: The workaround is to cast the columns to DATETIME (DT_DBTIMESTAMP in SSIS) or to change the underlying columns to DATETIME. Both get around the problem. Both are annoying.

My guess is that the source of the problem is in the OLE DB provider. There is probably limited or flaky support for the date only data type.

Upvotes: 3

Views: 3468

Answers (1)

Stuart
Stuart

Reputation: 1602

I have an answer. The problem was that I did not specify a provider on my connection string. When I added Provider=SQLNCLI11.1;, the problem was resolved.

Upvotes: 3

Related Questions