SSIS : Creating an error output from a script component

I had to implement this today…

I have a script component in SSIS that could have exceptions that need to be handled gracefully, row-by-row, and logged as such. This blog post contained all I needed!


SSIS DataFlow Discoverer

I have a pretty large ETL project I’m working on. I was getting the generic (read unhelpful) error message when records were not writing to my target database. It told me that there was a constraint violation, and gave me a column number.

Mind you, this isn’t the number corresponding with the column in the database. Rather, it was a number corresponding to a derived column in the SSIS package. Ugh!!! How was I to know what column it was? Enter the SSIS DataFlow Discoverer.

I found this blog:

Referencing this tool:

After running the tool on my DTSX file, I was able to use the column number in a query against the database the tool generated and, voila! It helped get to the column in question much more quickly than having to do it manually by process of elimination!


How To Set and Use Variables in SSIS Execute SQL Task

Needed this today…

How To Set and Use Variables in SSIS Execute SQL Task

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.

Happy coding!

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: