Needed this today…
We are doing some ETL work into a data warehouse for a client. One of the dimensions has a “smart key” value for a date dimension. So, we figured we could forego looking up the date key to populate an associated fact with and instead transform the incoming date to the smart key value. This worked great…except when it didn’t! Specifically, when we ran across a date that exceeded the data in the date dimension table. Yuck!
The solution was to do an initial lookup of the max date in the date dimension and capture that into a variable. Then, in the derived column where we were doing the smart key transformation, we would also check to see if the date exceeded the max in the dimension. If it did, we would set it to the default key value.
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:
I am posting this because I came across the same thing. As the original author states – maybe it will be useful for someone else.
SSIS 2012 – Package Configurations Menu Option Missing