Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

This tutorial will help you to get started with SSIS (SQL Server Integration Services)

Getting started with SSIS

Getting started with SSIS - Part 9: Debugging SSIS packages

Mar 1 2012 12:00AM by Sudeep Raj   

Our package is ready we have made it dynamic (partly) and we are ready to go. What if there are any errors in the package execution? How to troubleshoot or debug the SSIS Package?

There are various ways to approach this:

  • Look at the progress tab for initial symptoms
  • Debug the Package to pin point the issue

Let’s look at the Progress tab first. Open the SSIS Solution built in Chapter 3.

Go to the Solution Explorer, select the package, right click it and select Execute Package. Once the package execution starts, you will notice a new tab would appear after the Package Explorer tab as Progress. Go to this tab after the package completion. If everything is fine the data flow task will turn green and the Progress tab would look as in te figure below:

SSIS BIDS – Progress Tab all success

Now what you can do is rename the Input file in your system from Input.csv to Input_1.csv. Run the package again and notice what happens:

  • In the Control Flow tab you will see the Data Flow Task turn Red
  • Go to the Data Flow tab, you will notice the Flat File Source turn Red.
  • Now go to the Progress tab. It will be slightly different from what you noticed earlier. Look for the 2 Error statements with a red Exclamation mark icon beside them. Also there is red icon with cross towards the end in Fig 2.
  • If you read the first error message it states: “[Flat File Source [1]] Error: Cannot open the datafile "I:\InputPath\Input.csv".”

Reading the above message it is clear that there is an issue with the file name. We need to rectify this, either we change the file name in the system to Input.csv or in the flat file connection browse to the file path and select the new file that we have input_1.csv. Either case the issue will be resolved and the package will execute successfully and the progress tab will have no Red marks.

SSIS BIDS – Progress Tab with Errors

The above approach can be applied to trouble shoot basic issues like connection managers or variables missing etc. But what if the issue is not that simple?

For debugging a package, we have 3 basic methods:

  • Setting breakpoints in a package, container or task
  • Setting breakpoint in Script task
  • Using Data Viewer in the data flow task
  • Setting Error redirection in the Data Flow task

Setting up breakpoint in a task:

For example, we have for each loop that iterates through each file in a given folder (the set up for this will be shown in later sections). Now we need to see the value that is populated in the variable containing the file name, in each iteration. Here we need to debug the package and below are the steps you need to take.

Click the Control Flow tab and place the cursor anywhere on the background of the design surface or a task or a container, right-click, and then click Edit Breakpoints.

Depending on where you right click the Set BreakPoint – Package or task you clicked popup window would open as in the figure below.

You may select the various events where you want the breakpoint to be set. While execution you can have a watch to view the variable values at these breakpoints.

SSIS BIDS - Breakpoints

Setting up break point in Script Task:

Open the script task you want to set the breakpoint in VSTA. Go to the line where you wish to setup the break point. Right click on that line and select point to Breakpoint, and then click Insert Breakpoint.

SSIS, setting up breakpoints in scrip task

Using Data Viewer in Data Flow Task:

This is one of the most commonly used techniques for debugging. As SSIS is an ETL tool and we are mostly dealing with data, in case we need to see what kind of data is passing between 2 Data Flow Tasks we can use this method. Here are the steps.

  • Go to the Data Flow tab
  • Double click the green arrow connecting the Flat File source with the OLEDB Destination. Data Flow Path Editor Window would open as in the figure below:

SSIS – Data Flow Path Viewer, Add Data Grid

  • Select Data Viewer on the left panel and then click the Add button. Configure Data Viewer Window would open below figure

SSIS – Data Flow Path Viewer, Add Data Grid

  • In the General tab select Grid, the other options are generally not used, such as, the histogram, scatter plot or column chart. You could try them out later for practice.
  • Now go to the Grid tab and select all the columns you wish to view while package execution. By moving the columns from displayed columns to unused Columns panel.

SSIS- Data Flow Task- Configure Data Viewer

  • This completes the setup of Data Viewer. Now execute the package you would see a popup with the tabular display of data in the input csv file as in the figure below:

SSIS – Data Flow- Data viewer in action

  • In the above window, you can analyse the data that is coming out of any transform and if you see there are wrong transformation of data happening or unexpected data coming in like data type error you could take corrective action.

Setting Error redirection in the Data Flow task:

Remember that the above action will help only when the data passes successfully from the source transform. What if the data is not able to be read in the source transform? How can we monitor the data then? Here we would need to use the Error redirection in data flow task.

Before starting we should introduce some error in the source csv file data. Open the csv file and set the name column to a long sting greater than 50 characters. Save the file and re-run the package. You will see that the package fails at the Flat File source in the Data Flow Task.

Getting around this issue we need to know what is wrong with the data. Right now the input file is small and we can get this issue by simply looking into the file but what is there is a single error in a file having millions of records and hundreds of columns. Would be able to detect it by just viewing the file? Probably not.

We use SSIS for this. Let’s say we want to see the error row redirection for all the corrupt data coming from the Flat file source. For this double click the Flat file source. Select the Error Output on the left panel. On the right, you will see a tabular representation of all possible combinations.

SSIS – Data Flow Task, setup Error Output

Let us take the example of the first column “Name”. We can have 3 scenarios for Name column namely: Error of any kind or Truncation i.e. the source value is larger that the value that the SSIS source can accommodate for this column.

The default behaviour for both Error and Truncation is that the component in question would fail leading other components to fail. We have the option of setting this property to “Ignore failure” or “Redirect Row”

Ignore Failure:

If this is selected then for this column if error occurs this row would be passed to output buffer but the value would be set to…… why not you try this out yourself. If we set this option then the number of records entering the transform = number of transforms exiting the transform.

Redirect Row:

What this option will do is redirect the row as it is to another flow (for now think that all the good records will go to the green path while the bad records to Red path). In case we set the redirect row for each column for error and Truncation. We have 100 input records and 10 records have error of some kind or the other. Then 90 records will go along the green arrow and 10 along the red arrow. We can have another destination after the red arrow to view these error records later for analysis.


Sudeep Raj
12 · 13% · 4306
11



Submit

7  Comments  

  • its going to be a complete book "The REAL STEP by STEP guide to SSIS". Thanks Sudeep. Super you are.

    commented on Mar 3 2012 5:42AM
    Manas Ranjan Dash
    39 · 5% · 1484
  • Thank You, Sudeep.

    commented on Mar 11 2012 5:23AM
    Kirti M.Darji
    10 · 16% · 5011
  • Why is it that every SSIS tutorial I read references external programs and assumes that beginners know what it is and how to use it?

    "Open the script task you want to set the breakpoint in VSTA." What is VSTA? I assume it's Visual Studio, but I have no idea what TA stands for.

    commented on Oct 30 2012 12:00PM
    red321
    2827 · 0% · 4
  • Hi red321,

    That's because we expect one learning SSIS should have basic. NET and TSQL knowledge.

    VSTA for Visual studio tools for application. When in Script task pop out Window you click on "edit script" it opens the script in another Window which is a part of VSTA. You may Google for more information for on VSTA.

    commented on Oct 30 2012 2:47PM
    Sudeep Raj
    12 · 13% · 4306
  • Maybe I'm more concerned with details than most, but when I make a tutorial, there are 2 things that I believe must be done:

    1. Never assume that the reader knows anything.
    2. Any time you click a mouse button or press a key, there should be an explanation as to what, how, and why you are doing what you did.

    Skipping explanations of steps is very frustrating to the reader, especially when they get 8 steps in and realize there's something else they need to know to continue (but at least knowing Script Tasks is not a necessity at this point).

    commented on Oct 30 2012 3:19PM
    red321
    2827 · 0% · 4
  • Thanks for your comment. Will keep that in mind.

    commented on Oct 30 2012 3:23PM
    Sudeep Raj
    12 · 13% · 4306
  • Hi Sudeep,

    just i had a walk through on this post, its really nice.

    Thanks.

    commented on Jan 27 2013 1:50AM
    Bala Krishna
    84 · 2% · 676

Your Comment


Sign Up or Login to post a comment.

"Getting started with SSIS - Part 9: Debugging SSIS packages" rated 5 out of 5 by 11 readers
Getting started with SSIS - Part 9: Debugging SSIS packages , 5.0 out of 5 based on 11 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]