How to find and Debug SSIS problems (when data loads fail)

When loading data through an SSIS package, one of the problems we face is when data causes a step to fail.  If the source data contains 10,000 rows, how do we tell which row had the data that was too long, in the wrong format, null or caused the bad join?

Well, when running in automated mode (via a SQL agent job), SQL will log the cause of the error (the error description and the column), but will not log anything about the row.   Now, we could create a complex SSIS error trapping and logging solution (direct bad rows to a step which writes the information away).

A simpler solution is to re-run the package in design mode (via Visual Studio), and temporarily add a data view of problem rows.   Using a data viewer, whenever a row causes an error, the record details (all columns of all error rows) are displayed on screen in a grid, which allows us to examine the error.

Steps involved to use a Data Viewer

Imagine a situation where you have a data reader (source control), data writer (destination control) or a merge that fails.   To invoke a data viewer, use the following steps:

1)      First, for the step, double click the step box to bring up the properties, and go to the error output.   The default action will be to Fail Component. Change these to redirect the row.  This will push bad data to a different branch in the work flow:

2)      Next, add a Derived Columns control next to out failing step – this is a dummy control, it is not actually going to do anything, we just need this extra ‘step’ to receive the error rows.

3)      Now, join your failing component to the dummy script using the red error flow as shown below.

4)      Now, on the red line joining the two, right hand click and select Data Viewers.   In the pop-up data viewers, click ADD to add a data viewer, and select Grid to display the details.  In the 2nd tab of “grid” it will have by default already selected all the columns in the data flow – if you want to, you can edit this to deselect columns you don’t need (although, personally, I would keep them all in).

5)      You will now have a flow as shown below – notice the new data viewer icon floating above the error line, to indicate the position in the flow of your new data viewer:

6)      Now, run your SSIS package using visual studio.  When rows produce errors – a pop-up will appear showing the first 100 rows or so that error (press the green triangle for more).  This should allow you to spot the cause of your error.

7)      When you are done, to reverse the changes, just delete the Dummy Derived step (which will also delete the error flow line and data viewer) and change the originally erroring step back to Fail Component rather than redirect line.

1 Trackback or Pingback for this entry