Wednesday, 3 August 2011

SSIS Tutorials : Debugging Data Flow

BIDS tool has option to debug the Data Flow control. In normal cases you can see row count while you execute the package in debug mode. And you can see the execution details in progress (Execution Result) tab of the package designer. Apart from this you can see data which is being manipulated in you data task using data viewer. Let's see how to use data viewer in Data Flow task.

Step 1: Define your Data Flow Task. See below image for sample data fow task.

Step 2: Right Click on  Data Flow Path editor as show in below image. And the click on Edit or Data Viewer.

Step 3: Click in on Add  as show in above image. to add data viewer. It will open sceen as shown below to select the type of data viewer you want. For Grid view just click on ok and Ok. It will add one data viewer to your data flow.

Step 4: After adding data viewer you will see small viewer icon along with data flow path.

Step 5: Now run your package to see the data viewer in action. Once you run the package, it will stop at data viewer and will show the records. You will see tree buttons on data viewer. Play, Detach, Copy Data. Click on play to continue exectution with data viewer attached to data flow path. Click on Detach to detach data viewer from execution flow. and Copy Data to copy data in clipboard.

This was short explanation to use data viewer to debug the Data Flow task.

Data viewer can display data in different format. It can show data in grid, histogram, scatter plot and column chart.

Data Grid : It allows column selection, which can later appear as column in tabular data display in debug mode.

Histogram : It only works for numeric column and shows data distribution as histogram.

Scatter plot : This one also only works with numeric data. You can select numberic data column for different axis for the graph.

Chart format: This chart show the occurrence count of discrete values in the selected column.

MS SQL Server Integration Services | SSIS | SSIS Tutorials | Learn SSIS | Beginers Guid to SSIS | Microfot BI Tutorials | Learning SSIS | How to debug package | Debugging Packages | Debugging Data Flow | How to Debug SSIS Package