SSIS Lookup fails validation on x64 machine

Question

I have an SSIS package that is using a Lookup:

• Target and Source use OLE DB connections, looking at SQL Server 2008 R2.
• Lookup is using Full cache, and an OLE DB connection manager (defaults).
• The Lookup looks for a key of 13 columns. One of them is of type Date, which fails validation later.

It all works well from my machine, naturally.
I can install the package on production, but when running it it quickly fails on validation of the Lookup:

input column [ColumnName] (238) and reference column named [ColumnName] have incompatible data types

This is clearly a lie - they have the same types. It looks like this is a known problem of the Lookup component:

• Lookup claims incompatible data types
• SSIS Lookup Component Error: output column ‘x’ and reference column named ‘y’ have incompatible data types

In both cases, some people suggested running the package in 32 bits solved their problem. However, my package fails connection to the server when running in 32 bits...

What am I missing? Should I install additional x86 providers?
Is there a good way of fixing the Lookup?

Answer 1

As a workaround, I was able to open the Microsoft Visual Studio 2008 Shell on the production server, and run the package through there. Since this is a one-time migration job, I don't really mind.