31 Days of SSIS – Data Flow Breakpoints?! (3/31)

Posted by & filed under , , , , .

31 FingersAs I mentioned in the introductory post, I’m writing 31 Days of SSIS.  In yesterday’s post I talked about Using Breakpoints.  One of the points I stressed was that breakpoints only exist in the Control Flow and not in the Data Flow.  Today, I’m going to show you that I’m a liar.  But not really.

A specific breakpoint feature does not exist in the Data Flow.  A feature that behaves very much like a breakpoint, though, does exist.  It isn’t called a breakpoint.  Instead it is called Data Viewers.

Data Viewers Definition

Data Viewers are containers that can contain a single SSIS buffer’s work.  As the data passes through the Data Path you are able to view what is in each buffer.  As each buffer passes into the Data Viewer, the processing of the SSIS package on that Data Path is paused.  In essence, you have a breakpoint.  But not really.

There are four types of data viewers.  These are:

  • Grid: Displays selected columns display in a tabular format.
  • Histogram: Displays distribution of numeric values and works only with numeric data.
  • Scatter plot: Displays scatter plot graph works only with numeric data.  You are able to set the x-axis and y-axis.
  • Chart format: Displays a column chart of the count of discrete values in the selected column.

To be perfectly honest, I’ve only ever found a use for the Grid Data Viewer.  There may be situations that I’m unaware of that the others are of great benefit.  Unfortunately, I’ve never come across those.

Data Viewer Setup

Now setting up a Data Viewer on a Data Path is fairly simple.  First, go into the Data Flow and select the Data Path that you want to pause the data processing.  You can right-click to open the properties or double click on the Data Path.  Just like make sure your cursor is on the red “X” below.

image

Once you have that, the Data Flow Path Editor will open.  From this screen you can tab into the Data Viewers tab to get the options for Data Viewers.

SNAGHTML9262b24

The types of Data Viewers will appear on the left-hand side of the screen.  It’s shown below at the green arrow.  All four of the types from above will be listed.  As I mentioned, the only one I use is the Grid.  The other three have never been of use to me.  I’ve looked at them a few times and just thought they were pretty.

SNAGHTML9275bb6

Before moving on, it’s advisable if you are putting these into your SSIS package to name them with something meaningful.  The blue arrow and bubble above indicate the place where the name can be typed

Now this next step is optional but I do recommend that you consider this.  When you create a Data Viewer Grid you have the option to remove some columns from the grid.  This can be extremely handy when the Data Path has more than a handful of columns.  Since you are likely adding the Data Viewer to look for specific values, you may as well only return the values that are of use.

SNAGHTML926586b

When you are finished, select OK and you’ll return to the Data Flow.  From here you will notice that a little icon has been added to the Data Flow Path to indicate that there is one or more Data Viewers on the path.

image

Yes, that’s correct.  You can have more than one Data Viewer on the same Data Flow Path.  Depending on what you are troubleshooting it may be worthwhile have two or three Data Viewers that return different columns.

Data Viewer In Use

Once you have a Data Viewer in place on a package, go ahead an fire it up.  The execution of the package will change when there are Data Viewers.

When the package begins, a contained will open for each Data Viewer.  By default, these will float over the window.  If you choose, you can dock these, that is up to you.

On the container there are a few things to take notice of.  First, in the image below the yellow area is all of the data currently contained in the Data Viewer.  Within it you can highlight one row or multiple rows.

image

If you want to copy out the data, you need to use the Copy Data button, indicated at the red arrow.  The use of Crtl-C to copy doesn’t do a dang thing.  And that sucks.

If the data you are looking for isn’t in the current buffer, you can move the package forward to the next buffer.  You do this with the Play button located at the orange arrow.  Selecting this will clear the Data Viewer and it will populate with the next buffer of data.

Lastly, if you are done with the Data Viewer and want to stop using it you can detach it from the Data Flow Path.  This is done at the green arrow.  By detaching, the Data Viewer retains it’s current data while the package continues with processing the rest of the Data Flow.

As I mentioned, Data Viewers are a lot like Breakpoints.  Due to this, you can use the Breakpoint window to disable, enable, and delete data viewers in your project.  This can be extremely handy when you have a half dozen or more data viewers you just want to get rid of.

image

Data Viewers Wrap-Up

Now that we’ve gone through this, I hope you can see the power and benefit of using Data Viewers.  When you are troubleshooting an SSIS package trying to figure out where some of the data is becoming out of wack.

Above, I mentioned a couple bits where I compared Breakpoints to Data Viewers.  In a lot of regards, they function similar to one another.  There is one way, though, in which they are very different.  When a breakpoint is encountered, the entire package execution is paused until the break is cleared.  When a Data Viewer is encountered only the data in that Data Flow Path is paused.  While these can be in effect the same thing, in more complicated packages this might cause unexpected results.

Finally, you may have trouble using Data Viewers when you are processing large volumes of data.  If the row that is causing the issue is a few thousand or million rows into the data set you will likely want to do a little bit more than just add a Data Viewer.  You may need filter the data some to make the Data Viewer useful for troubleshooting.

Thanks for reading and if you have any questions, feel free to leave a comment.  Also, since I still have 29 posts to go, feel free to ask for items on specific topics.

  • Pingback: Tweets that mention 31 Days of SSIS – Data Flow Breakpoints?! (3/31) | Strate SQL -- Topsy.com()

  • Pingback: 31 Days of SSIS – The Introduction | Strate SQL()

  • http://blog.realworldis.com Chris Anderson

    Really enjoying the series, and have forwarded to some of my team that I need to bring up to speed on SSIS concepts.

    Looking forward to some event handlers for errors. A number of projects I've seen don't have a good way to log exceptions/errors on packages, at least in a way that makes it easy to focus on useful information.

    • http://feeds2.feedburner.com/StrateSql jstrate

      If I don't get to that in these 31 days, I will make certain I get something up shortly afterwards. Surprisingly my 31 days of topics is filling up faster than I expected.

  • Pingback: SQL Server Central()

  • Koen Verbeeck

    I didn't know about the possibility to remove columns from a grid data viewer. Great stuff!
    I think the other data viewers (histogram, scatter and chart) can be used to do some ad-hoc low level data profiling. But just like you, I have never used them before :)

  • Amy

    Just a quick question: after executing the package, and the data viewer appears — what if you see something terribly wrong and want to halt the data flow right then and there. Both "Detach" and the "X" in the corner of the window let the data flow task continue. Is there a way to back out using the data viewer?

  • Din

    Can You please exaplin Play and Detach Buttons in detail??

    • Jason Strate

      Sure thing, Play will allow the SSIS package to proceed to the next data buffer. The Data Viewer only contains a single data buffer from the SSIS data flow. When Play is selected, the current data buffer is released and the next is populated. The Detach button releases the Data Viewers control over the data flow. It retains the last data buffer the was retrieved and will let all subsequent data through uninterrupted.

  • SQLnerd

    “To be perfectly honest, I’ve only ever found a use for the Grid Data Viewer. There may be situations that I’m unaware of that the others are of great benefit. Unfortunately, I’ve never come across those.”

    Obviously you wasn’t the only one, as the other three types has discontinued in the SQL 2012 version:

    Previous versions of SSIS included four types of data viewer—the Grid, Histogram, Scatterplot, and Column Chart data viewers. Because the Grid data viewer is the most commonly used, the SSIS team removed the other types of data viewers from SSIS 2012.

  • Pingback: Wrapping Up 2013 | Strate SQL()