Debugging SSIS ScriptTasks and ScriptComponents with Information Messages

[Darren Gosbell]

I recently had to do some simple debugging of some script tasks and components in SSIS and through I would share the simple technique that I was using. While this is only just above the level of debugging using MessageBoxes it can still be quite handy. It basically consists of printing information messages to the Progress/Execution Results window in BIDS. The syntax differs between the ScriptTask and the ScriptComponent and I keep forgetting it, so I figured if I posted it here I should be able to find it later without too much trouble.

For ScriptTasks in the control flow you use the following

* Dts.Events.FireInformation(0, "", "", "", 0, true)

*

For ScriptComponents in the data flow you use the following

* ComponentMetaData.FireInformation(0, "", "", "", 0, true)

*

Both these calls cause an information message like* "[ ] " to be written to the Execution Results window in BIDS.

This can be useful for displaying the values of variables and for printing out trace messages when certain logic has been invoked.

The screen shots below are from a simple test package which just loops through every database on a server and selects a list of all the tables. In the data flow task I have a script destination component that raises information events with the database and table name in the description. below is a copy of the code and a snippet of the Execution Results window that shows the output.*

*

Then at the end of the control flow I have a script task that just prints out a message that the packages had finished.








More...