SSIS Debugging Tip: Using Data Viewers
- by 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. Now what? We can tell from our execution results that there is a problem with [lastname], but we have no idea WHICH record? 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. 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. 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: 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. 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. 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
