SQL Server 2014 – SSIS Lookup Transform Fails on Date Column (datetimeoffset)

This came in handy today. We had an issue where we had tested a SSIS package in our development environment. When we deployed it to QA, we received an error:

VS_ISBROKEN – column [x] and column [y] have incompatible types

This is on SQL Server 2014. We confirmed that the dev server was on a slightly older build than the qa server. What fixed the issue was changing the provider on the connection string for the package to SQLNCLI11.1 instead of SQLOLEDB. The data type in question was a datetimeoffset.

This is the link where we came upon the solution:

http://stackoverflow.com/questions/15372396/ssis-lookup-transform-fails-on-date-column