Debugging Control Flow | MicrosoftВ® SQL Server(TM) 2005 Integration Services Step by Step

If you have one of the following issues in your package, this section will give you an idea of how to troubleshoot it:

A certain task that works with a resource external to the package (for instance, files, database, Web services, and so on) fails. You would like to check its state immediately before the task is executed.
A package variable is changed to an unexpected value under certain conditions. You would like to find out which task is updating the variable.
A Foreach Loop container or For Loop container doesn't work as you intend (for example, the loop never ends, the loop doesn't pick up the correct set of sources, and so on). You would like to know what is happening inside the loop.

These problems generally happen in tasks and containers in Control Flow. BIDS and SSIS Designer allow you to suspend execution by using breakpoints and allow you to browse the state through debug windows. Also, progress messages appearing on the Progress tab help you understand details of the execution status. The goal of debugging Control Flow is to discover which tasks, settings, or resources are causing the issue and to find out the reason for the defects.

Understanding Breakpoints

A breakpoint is a marker for where you would like to pause package execution. When the package is executed in BIDS, it suspends package execution at the breakpoint you have specified and enables you to investigate the state of the package. SSIS Designer allows you to set breakpoints on events that a package, container, and task provide. Events are essentially messages derived by a component, such as "I'm starting," "I'm done now," or "I have a problem." You can set breakpoints on multiple events and on the different items at the same time. The following table shows common events that most components provide:

Open table as spreadsheet

Event	Description
OnPreExecute	This event is raised by a package, task, or container immediately before it runs, when the execution logic begins.
OnPostExecute	This event is raised by a package, task, or container immediately after it runs, immediately after the execution logic of the task completes.
OnError	This event is raised by a task or container when an error occurs.
OnWarning	This event is raised any time the task is in a state that does not justify an error but does warrant alerting the client.
OnInformation	This event is raised any time the task is to provide information.
OnTaskFailed	This event is raised by the task host when it fails.
OnProgress	This event is raised any time a task experiences measurable progress.
OnQueryCancel	This event is raised by tasks and containers to determine whether they should stop running at any juncture in task processing where it is feasible to cancel execution.
OnVariableValueChanged	This event is raised by a variable whenever its value is changed.
OnCustomEvent	This event is raised by tasks to raise custom task-defined events.

The OnPreExecute event is usually used to suspend execution before the component in question is executed. The OnPostExecute event is also useful if you would like to see what the target component has done immediately after the execution. If you would like to suspend execution when an error occurs anywhere inside the package, you can set a breakpoint on the OnError event at the package level.

Some components have their own events. For example, on the For Loop and Foreach Loop containers, you can set a breakpoint at the beginning of each iteration of the loop.

SSIS Designer also enables you to specify Hit Count Type and Hit Count options on each breakpoint. You can choose Always, Hit Count Equals, Hit Count Greater Than Or Equal To, or Hit Count Multiple from Hit Count Type and specify the hit count. Always will be used by default if you don't specify this option. This can be used with loop iteration events. For example, if your package loads multiple comma separated value (CSV) files into a database and it keeps failing at the third file, you can suspend execution when the Foreach Loop container comes to the third loop. You set a breakpoint at the loop iteration event on the container by specifying Hit Count Equals on Hit Count Type and 3 on Hit Count.

In the following procedure, you will learn how to set breakpoints and suspend execution in Control Flow by using BIDS and SSIS Designer.

Set Breakpoints and Suspend Package Execution

Start BIDS.
On the File menu, point to Open, and then select Project/Solution.
In the Open Project dialog box, specify C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005SbS\Chap07\Chapter07.sln and click Open.
In Solution Explorer, double-click DebuggingControlFlow.dtsx to open the package.
Right-click Execute SQL Task - Clear DimCustomer and select Edit Breakpoints.

The Set Breakpoints <container name> dialog box appears with the list of breakpoints that you can set on the task. You can also specify the Hit Count Type and Hit Count options through this dialog box. Here, you will set a breakpoint on the OnPreExecute event by selecting the Enabled check box by the break condition.
Select the check box next to the break condition Break When The Container Receives The OnPreExecute Event. Your screen looks like this:
Click OK.

Notice that a red dot is displayed on Execute SQL Task - Clear DimCustomer, as shown in the following illustration. This indicates that a breakpoint has been set on this task.

Select Foreach Loop Container - Find Customer CSV and press F9.
F9 is a shortcut key to set a breakpoint on the OnPreExecute event. Press F9 again and observe that the breakpoint is removed.
Right-click anywhere on the design surface of the Control Flow tab and select Edit Breakpoints. This allows you to set breakpoints at the package level.
Select the check box next to the break condition Break When The Container Receives The OnPostExecute Event.
Click OK.
Right-click Foreach Loop Container - Find Customer CSV and select Edit Breakpoints.

Verify that the dialog box shows the Foreach Loop container-specific break condition Break When The Beginning Of Every Iteration Of The Loop at the end of the list.
Select the check box next to the break condition Break At The Beginning Of Every Iteration Of The Loop.
Select Hit Count Equals from the Hit Count Type drop-down list and type 3 in the HitCount field. Your screen looks like this:
Click OK.
Before you execute the package, you need to copy the source CSV files to a designated location. Start Windows Explorer and open C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005SbS\Chap07\Data. Copy all CSV files in the folder to the root folder of the C drive.

Go back to BIDS and press F5 to execute the package.

The first breakpoint is the OnPreExecute event of Execute SQL Task - Clear DimCustomer. Notice that a yellow arrow appears on the red dot, as shown in the following illustration. This arrow indicates the current location of execution.

image from book

Also, be aware that the Execute SQL Task - Clear DimCustomer task is colored yellow. SSIS Designer changes the color of tasks and containers to depict the current state of each item. The following table shows the meanings of each color:

Open table as spreadsheet

Color	Execution Status
White	The item is waiting for execution; it has not executed yet.
Yellow	The item is currently running.
Green	The item ran successfully.
Red	The item ran and encountered errors.
Gray	The item is being disabled (will be skipped).

Start SQL Server Management Studio (SSMS) and connect to localhost.
In Object Explorer, expand Databases, is2005sbsDW, and Tables. Right-click the dbo.DimCustomer table, and then click Open Table. Verify that the table holds data.
Go back to BIDS and press F5 to resume execution. Notice that the yellow arrow stops at Foreach Loop Container - Find Customer CSV.
Go back to SSMS and select Execute SQL on the Query Designer menu. Notice that data in DimCustomer has been truncated by the Execute SQL Task - Clear DimCustomer task.
Go back to BIDS and press F5.

Because you have set the condition Hit Count = 3 at the loop iteration event, SSIS Designer executes Data Flow Task - Imports Customer CSV and File System Task -Delete File for the first two files and then breaks execution before the container enters the third iteration. Open the root directory of the C drive in Windows Explorer and verify that the first two CSV files (Customer_A–F.csv and Customer_G–L.csv) have been processed and deleted.
Select File System Task - Delete File and press F9.

A red dot is now displayed on the File System Task - Delete File task. As you can see here, you can set and remove breakpoints while the execution is suspended.
Press F5. Once you have verified that execution has been suspended at File System Task - Delete File, press F5 to resume execution.

Now the yellow arrow appears on the Control Flow tab. This indicates that the package execution has reached the end, and the OnPostExecute event was fired.
Press F5 to complete execution.
On the Debug menu, click Stop Debugging.
On the Debug menu, select Delete All Breakpoints. Click Yes in the alert message box.

Tip
As you can see in the menu caption, you can also delete all breakpoints by pressing Shift+Ctrl+F9.

Reviewing Debug Windows

As you go through the procedure in the previous section, you might want to look into such details as package variables. You might have noticed that a set of small windows docked in the BIDS window appear while you are executing a package. These windows provide information about the package or about each item in Control Flow while execution is suspended by breakpoints. BIDS provides several kinds of windows for debugging, and the following windows are useful when you debug Control Flow. These windows are available on the Debug menu in BIDS.

Call Stack

The Call Stack window provides a hierarchy and sequence of the package execution. You can view the names of containers and tasks invoked in the execution. A yellow arrow in the window identifies the task or container currently running.

Breakpoints

The Breakpoints window provides a list of the breakpoints currently set in the package. You can activate, deactivate, and delete breakpoints by using this window. You can also modify the break condition of each breakpoint through this window.

image from book

When a package is executed, the Breakpoint window shows how many times the breakpoint is passed in the Hit Count column.

Locals The Locals window is the primary tool for browsing variables. Under the Variables node in the list, you can view all system variables and user variables available in the current package. You can also modify the value of variables through this window while the execution is suspended.

In the following procedure, you will see how you can browse and modify the variables through the Locals window.

Tip

Most debug windows are available only while you are executing the package in BIDS. If you would like to review the final state of the package, you need to set a breakpoint at the OnPostExecute event at the package level.

Execute Package and Review Debug Windows

On the Control Flow tab, right-click Foreach Loop Container - Find Customer CSV and select Edit Breakpoints.
Select the check box next to the break condition Break When The Beginning Of Every Iteration Of The Loop.
Click OK.
Start Windows Explorer and navigate to C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005SbS\Chap07\Data. Copy all CSV files in the root folder of the C drive.
Press F5 to execute the package. Verify that the package is suspended at the Find Customer CSV container.
On the Debug menu, select Breakpoints to open the Breakpoints window.

Ensure that all the breakpoints you have set are listed in the window. You might notice that one of the breakpoints is marked with a red dot and a white plus sign. This indicates the breakpoint has an additional condition such as Hit Count Type and Hit Count.
On the Debug menu, point to Windows, and then select Locals. Expand the Variables node in the Locals window.

Verify that all system variables and user variables are listed in the Variables node. Find User::FileName variables in the list. The value of the variable now should be C:\Customer_A–F.csv. This variable holds a CSV file name that will be provided to the Flat File source component in Data Flow Task - Import Customer CSV.
Press F5 to process the first CSV file.

Foreach Loop Container - Find Customer CSV found the second CSV file and updated User::FileName to C:\Customers_G–L.csv.
In the Locals window, expand User::FileName and change the value in the Value property to an incorrect file name, such as ABC.
Press F5.

The package fails because it tries to find the given, but inappropriate, file name.
On the Debug menu, select Stop Debugging.
On the Debug menu, select Delete All Breakpoints. Click Yes to confirm.
In Windows Explorer, delete any CSV files remaining in the root folder of the C drive.

Understanding Progress Messages

When a package is executed, SSIS Designer logs events and messages raised by packages and components on the Progress tab. This helps you understand package execution precisely.

After you stop debugging a package, these messages remain available on the Execution Results tab, so you can refer to them later.

The messages on the Progress tab usually appear in the following form:

image from book

A Progress message starts with the top-level package name, and all tasks and containers defined in the package appear under that package name. Tasks and containers are alphabetical, not chronological.

Event messages derived by each component appear under the component name. The messages of each component usually start with a validation message such as Validation Has Started, Progress:Validating, and Validation Is Complete. This is because the SSIS engine validates all components defined in the package before the package is actually executed. If you suspend the package at its OnPreExecute event and look on the Progress tab, you can see that the message tree is structured, and the validation of each item is complete.

After the validation, the SSIS engine logs the package's start event as Start under the top-level package name with the current time stamp. After the package is executed, the SSIS engine logs each component's start event and interim event such as the information, progress, error, and complete (logged as Finished) events. The messages under each component appear in chronological order.

To find an active task, find a task that has a Start message but doesn't have a Finished message at the end of the message set. If you would like to know the total execution time or the execution time for each component, refer to the elapsed time logged with the Finished message under the component.

Executing a Package Partially

If you have a long series of tasks and containers in your package, you might want to execute the package only partially to save you debugging time.

BIDS and SSIS Designer provide two options to execute packages partially. The first option is to right-click a task or container and select Execute Task or Execute Container. The SSIS engine executes the specified task or container and then terminates package execution without executing other tasks and containers. If breakpoints are set in the task and container, they will be ignored.

The other option is to disable tasks and containers that you would like to skip during the execution. This can be helpful if you would like to investigate more than one task or container. Breakpoints work well for this.

In the following procedure, you will learn how to execute a package partially by using the features previously discussed.

Disable a Task and Execute a Package Partially

Start Windows Explorer and navigate to C:\Documents and Settings\<username>\My Documents\Microsoft Press\is2005SbS\Chap07\Data. Copy all CSV files in the folder to the root folder of the C drive.
In Control Flow, right-click Execute SQL Task - Clear DimCustomer and select Execute Task.

The SSIS engine executes the specified task (Execute SQL Task - Clear DimCustomer) and terminates execution before it begins the next task.
On the Debug menu, select Stop Debugging.
Right-click File System Task - Delete File and select Disable.

Notice that the task has been grayed out. This indicates that the task is disabled and never runs.
Press F5 to execute the package.

The package runs and executes all tasks and containers in Control Flow. Start Windows Explorer and open the C folder. Observe that the CSV files remain in the folder because you disabled the delete task (File System Task - Delete File) in the last step.
On the Debug menu, select Stop Debugging.
Right-click File System Task - Delete File and select Enable.