Tuesday, 5 June 2012

FireInformation to debug SSIS script

This is a quick blog that I've been meaning to post for a while. When writing script in an SSIS script task or transform, debugging can be a bit tricky.
Strategies for debugging scripts are briefly outlined here. I know a lot of beginners (and those few hold-outs still migrating over from DTS) use message boxes to debug scripts. There are a number of disadvantages to message boxes; you can't copy the contents of the message (to validate a file-path for example), the message isn't recorded anywhere to allow you follow the script logic, and you have to remember to remove the message box statement before deploying to a server (otherwise the package will hang waiting for a response that will never come!). Therefore I prefer using the FireInformation method of the IDTSComponentEvents interface. At its simplest it looks like this;
public void Main()

            string s1 = "Hello world";
            bool f = false;

            Dts.Events.FireInformation(0, "", s1, "", 0, ref f);

            Dts.TaskResult = (int)ScriptResults.Success;
This results in the following in the 'Output' window;

Note that the 'fireAgain' parameter ('f' in the script) is passed by reference and needs the 'ref' keyword. All this means is that the variable can be altered by the FireInformation method itself - I always set it to false. Obviously more useful things can be logged - but the principle is the same. This is particulary useful in a Data Flow (ie as a Script Transformation) where breakpoints and so on rapidly become impractical. One word of warning though ensure that 'OnInformation' events aren't logged as part of the SSIS logging, otherwise all of these debugging messages will be written out to your logging provider, slowing things down. Alternatively simply comment the calls out before deploying the package.

1 comment:

Emil Glownia said...

Hi Phil,

I use this method with SSIS framework (presented on SQL Bitx IX by Andy Leonard) to raise warnings and events without having to do much.... but I admit I have forgotten you can use that for debugging although I do remember you doing that at Mitie.