Geeks With Blogs
Jim Giercyk

When you have an SSIS package error, it is often very helpful to see the data records that are causing the problem.  After all, if your input has 50,000 records and 1 of them has corrupt data, it can be a chore.  Your execution results will tell you which column contains the bad data, but not which record…..enter the Data Viewer.

In this scenario I have created a truncation error.  The input length of [lastname] is 50, but the output table has a length of 15.  When it runs, at least one of the records causes the package to fail.

 

image

 

Now what?  We can tell from our execution results that there is a problem with [lastname], but we have no idea WHICH record?

 

image

 

Let’s identify the row that is actually causing the problem.  First, we grab the oft’ forgotten Row Count shape from our toolbar and connect it to the error output from our input query.  Remember that in order to intercept errors with the error output, you must redirect them.

 

image

 

The Row Count shape requires 1 integer variable.  For our purposes, we will not reference the variable, but it is still required in order for the package to run.  Typically we would use the variable to hold the number of rows in the table and refer back to it later in our process.  We are simply using the Row Count as a “Dead End” for errors.  I called my variable RowCounter.  To create a variable, with no shapes selected, right-click on the background and choose Variable.

 

image

 

Once we have setup the Row Count shape, we can right-click on the red line (error output) from the query, and select Data Viewers.  In the popup, we click the add button and we will see this:

 

image

 

There are other fancier options we can play with, but for now we just want to view the output in a grid.  WE select Grid, then click OK on all of the popup windows to shut them down.  We should now see a grid with a pair of glasses on the error output line.

 

image

 

So, we are ready to catch the error output in a grid and see that is causing the problem!  This time when we run the package, it does not fail because we directed the error to the Row Count.  We also get a popup window showing the error record in a grid.  If there were multiple errors we would see them all.

 

image

 

Indeed, the [lastname] column is longer than 15 characters.  Notice the last column in the grid, [Error Code – Description].  We knew this was a truncation error before we added the grid, but if you have worked with SSIS for any length of time, you know that some errors are much more obscure.  The description column can be very useful under those circumstances!

Data viewers can be used any time we want to see the data that is actually in the pipeline;  they stop the package temporarily until we shut them.  Also remember that the Row Count shape can be used as a “Dead End”.  It is useful during development when we want to see the output from a dataflow, but don’t want to update a table or file with the data. 

Data viewers are an invaluable tool for both development and debugging.  Just remember to REMOVE THEM before putting your package into production  Smile

Posted on Friday, April 13, 2012 9:03 AM | Back to top


Comments on this post: SSIS Debugging Tip: Using Data Viewers

# re: SSIS Debugging Tip: Using Data Viewers
Requesting Gravatar...
Nice post! Thanks a lot.
Left by grandresume.com on Apr 13, 2012 10:18 AM

# re: SSIS Debugging Tip: Using Data Viewers
Requesting Gravatar...
Its a nice start of a day with reading this kind of quality article. Great work man.
Left by How To Calculate Molarity on Apr 13, 2012 2:14 PM

# re: SSIS Debugging Tip: Using Data Viewers
Requesting Gravatar...
This is a good one men. Great article. Go on with more like this, or better ones :-)
Left by Jorge on Apr 11, 2013 3:55 PM

# re: SSIS Debugging Tip: Using Data Viewers
Requesting Gravatar...
Whenever a problem persists in this SSIS package we should check out the data records and things first. Many times it will come handy and if problems continue the suggested method is perfectly okay. Thanks for this wonderful share and keep your great work!
attorney criminal defense
Left by Merlin John on Jul 12, 2017 8:47 AM

# re: SSIS Debugging Tip: Using Data Viewers
Requesting Gravatar...
Very useful article. Clear and nicely described.
Thank YOU.
Left by Narmad Swain on Jul 24, 2017 10:08 AM

Your comment:
 (will show your gravatar)


Copyright © Jim Giercyk | Powered by: GeeksWithBlogs.net