Debugging Data Flow

When your package encounters the following issues, you need to investigate the Data Flow task:

• A Data Flow task produces no result data, or it produces the wrong number of records. You would like to discover the component that is causing the issue.

• In a Data Flow task, unexpected data is produced during the transformation. You would like to suspend the data flow and look into the passed data.

You might have noticed that SSIS Designer doesn't allow you to set breakpoints to Data Flow components. This is because the breakpoints and debug windows might not be efficient tools when you inspect data flowing inside the Data Flow task. Instead of the breakpoints and debug windows, SSIS Designer provides you with a tool called data viewers.

Browsing Data By Using Data Viewers

A data viewer is a browser that you can attach to paths between two Data Flow components. You can set data viewers before or after the component in question to see whether the component is working as you intend. For example, you might set a data viewer right after the Lookup Transformation component to monitor whether the component retrieves appropriate values from a designated data source.

SSIS Designer provides four different types of viewers: Grid, Histogram, Scatter Plot, and Chart Format. You can set different types of viewers for each data flow path. The following illustration shows a Grid data viewer working on SSIS Designer.

You might need to choose an appropriate viewer as the situation demands. Usually, the Grid viewer is the first option to inspect data because of its versatility. The rest of the viewers help you understand what kind of data is flowing or how the data is spread.

Data viewers work like a composite tool of the breakpoints and debug windows. The SSIS engine suspends data flow execution while you are browsing through a data viewer. SSIS Designer allows you to detach and attach data viewers during the execution, just like the breakpoints in Control Flow. If you are finished browsing through the data, you can simply detach the data viewer to let the rest of the data go through.

In the following procedure, you will learn how to browse through data flowing inside the Data Flow task by using data viewers.

Set Data Viewers and Browse Data

1. If you haven't opened  Chapter07.sln yet, start BIDS and open the solution file saved in C:\Documents and Settings\<username>\Microsoft Press\is2005SbS\Chap07\Chapter07.sln.

2. In Solution Explorer, double-click  DebuggingDataFlow.dtsx.

3. In SSIS Designer, click the Data Flow tab.

4. Right-click the green arrow between OLEDB Data Source - vProductProfitability and Flat File Destination - ProductProfitability CSV and select Data Viewers.

5. In the Data Flow Path Editor dialog box, click Add. The Configure Data Viewer dialog box appears on the screen.

   

   This dialog box allows you to configure a data viewer directly on the data path. In the General tab, you can select a data viewer from four types and specify a name for the data viewer. The name specified here will be used as a caption of the data viewer.

   The second tab is aligned with the data viewer you have selected on the General tab. The second tab enables you to configure the appearance of the data viewer.

6. In the Configure Data Viewer dialog box, select Grid in the Type list and change Name to Grid - ProductProfitability Output Summary.

7. On the Grid tab, click the double left-arrow button to move all columns into the Unused Columns list on the left side.

8. In the Unused Columns list, double-click the following columns to move them to the Displayed Columns list on the right side: Year, Month, Product, OrderQuantity, and SalesAmount. Your screen looks like this:

   

9. Click OK. Verify that the new data viewer is added in the list.

10. Click Add.

11. In Configure Data Viewer, select Column Chart and change the name to Column Chart - SubCategory.

12. In the Column Chart tab, select SubCategory in the Visualized Column list and click OK.

13. Click OK to close the Data Flow Path Editor dialog box.

    SSIS Designer shows an eyeglasses icon next to the path to specify that the path contains data viewer settings.

14. Make sure you are on the Data Flow tab, and then press F5 to execute the package. Your screen looks like this:

    

    Two data viewer windows appear over BIDS and, soon, the process will be suspended at the path between OLEDB Data Source - vProductProfitability and Flat File Destination - ProductProfitability CSV. The Grid - ProductProfitability Output Summary window shows the first data block from the data source in the grid, and the Column Chart - SubCategory window shows a column chart based on the SubCategory data.

    Also, notice that SSIS Designer displays the record count of the current data block near the data flow path. You can use this number to make sure the data flowing through each path is as you expect it to be.

15. Click the green arrow button in the Grid - ProductProfitability Output Summary window.

    The first data block has been processed, and the next data block appears in the Grid -ProductProfitability Output Summary window. Also, the column chart in the Column Chart - SubCategory window has been updated by the new data. Click the green arrow button in the data viewer window several times and review the data flow.

    When you have finished reviewing data, you can detach data viewers to let the process go through without detouring through the data viewers.

16. Click Detach in the Grid - ProductProfitability Output Summary window and Column Chart - SubCategory window.

    The two data viewers are now detached from the path, and data flows freely. You can re- attach the data viewer while execution is active.

17. While SSIS Designer is processing data, click Attach in the Grid - ProductProfitability Output Summary window. Notice that the data viewer is now attached on the path again and shows the current data block in the grid. Click the green arrow button in the window several times to review the data.

18. Click Detach in the Grid - ProductProfitability Output Summary window.

19. After all files are processed, select Stop Debugging on the Debug menu.

20. On the Debug menu, select Delete All Breakpoints. This deletes all data viewers set in the Data Flow task.

Understanding Other Options

Along with the data viewers, the following tools also help you debug data flow.

• Progress Messages If an error occurred inside the data flow, the messages on the Progress tab are the first place to investigate the error.

• Error Output If the error occurred inside your Data Flow task, the Error Output feature helps you identify the record causing the error. Error Output is discussed in Chapter 4, "Using Data Flow Transformations."

• Row Count As you have seen in the previous exercise, SSIS Designer shows you the number of processed records in the Data Flow tab. If the resulting data count is incorrect, this might be one of the clues for debugging.