Debugging SSIS ScriptTasks and ScriptComponents with Information Messages

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, "<SubComponentName>", "<Message>", "", 0, true)

 

For ScriptComponents in the data flow you use the following

  ComponentMetaData.FireInformation(0, "<SubComponentName>", "<Message>", "", 0, true)

 

Both these calls cause an information message like  "[ <SubComponentName> ] <Message>" 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. 

 image

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

image

Print | posted on Sunday, March 29, 2009 8:28 PM

Comments on this post

# re: Debugging SSIS ScriptTasks and ScriptComponents with Information Messages

Requesting Gravatar...
Most useful. Thank you.
Left by Henrik Staun Poulsen on Mar 30, 2009 9:09 PM

# re: Debugging SSIS ScriptTasks and ScriptComponents with Information Messages

Requesting Gravatar...
Thanks for posting this nugget. I came in very handy.
I love SSIS and it really annoys me that MS have only been sitting on SSIS/DTS for a couple of decades or so and still we have to put in debug lines like this.
I think that SS2012 has proper breakpoints in scripts but such is MS's reputation until I see it working, I would believe it.
Regards
D.
Left by Derek Robinson on Nov 15, 2012 8:42 PM

# re: Debugging SSIS ScriptTasks and ScriptComponents with Information Messages

Requesting Gravatar...
Most useful. I was strugging to find a way to use fire information inside data flow script task. This helps lot.
Left by Raj on Mar 26, 2013 11:46 AM

# re: Debugging SSIS ScriptTasks and ScriptComponents with Information Messages

Requesting Gravatar...
Right click on the script component and select Edit Break point and select your main method and press ok and close and run package it will fire
Left by vijayakumar on Jul 05, 2013 7:46 PM

# re: Debugging SSIS ScriptTasks and ScriptComponents with Information Messages

Requesting Gravatar...
Right click on the script component and select Edit Break point and select your main method and press ok and close and run package it will fire
Left by vijayakumar on Jul 05, 2013 7:48 PM

# re: Debugging SSIS ScriptTasks and ScriptComponents with Information Messages

Requesting Gravatar...
@vijayakumar - debugging both script tasks and components has only been available since SQL 2012. This article pre-dates that.
Left by Darren Gosbell on Jul 05, 2013 8:42 PM

# re: Debugging SSIS ScriptTasks and ScriptComponents with Information Messages

Requesting Gravatar...
Note: in C# at least, the last parameter has to be a reference to a bool - you cannot use the the True (or False) constant values, as above.
Left by RD Francis on Jan 10, 2014 5:20 AM
comments powered by Disqus