Endgame Viable

I had the “opportunity” to fix someone else’s SSIS package that was failing because of a data overflow on a date column at one of the data destination components.

At first I thought it was a simple case of a mangled date coming into the data flow.  But subsequent investigation showed that the data source component wasn’t failing, and no errors were trapped in the data flow.  The error occurred at the destination component, ie. as it was writing to the destination.  It was most puzzling.  It was even more puzzling because the destination was, in fact, a view.  (I always thought views were read-only.)

Anyway, I pushed all the mysteries aside because I needed to get this package running as soon as possible, because it had been failing for days and we were falling behind.  I needed a band-aid.

My first instinct was to set the destination component to “redirect rows” on an error, change the bad date to something sensible, set the error flag on the row, and send it to another destination.  It seemed simple enough.

Until I found out I couldn’t redirect rows on a data destination component.

WTF!  Why does it let me select that option if it doesn’t work?  This was yet another example of the immaturity of Microsoft’s SSIS Designer, and how most of my time in SSIS work is spent fighting with the interface.

I ended up setting the error disposition to “ignore errors.”  I felt dirty.  But the package ran, and for some bizarre reason, no dates were mangled on the output.