Debugging Parameters in the SSIS Data Flow (Script Component)


I had unfortunately picked up the (bad) habit of debugging my SSIS packages by using messagebox.show at design time.  The SSIS data flow Script component does not allow debugging using this approach so I had to find an alternative.  I discovered that it is possible to output parameters at design time using the FireInformation in the Script component.  This puts the parameters in the progress window.

// Output parameters into the SSIS progress window
bool FireAgain = true;
this.ComponentMetaData.FireInformation(0, "DataFlowDebug", "The value of time is " + Row.ParamValueTime.ToString(), "", 0, ref FireAgain);
this.ComponentMetaData.FireInformation(0, "DataFlowDebug", "The value of environment is " + Row.ParamValueEnvironment.ToString(), "", 0, ref FireAgain);

I like this approach although I should probably output the parameters to one of the other console windows.


Comments (2)

  1. Anonymous says:

    Whilst events work, and no doubt better than a MessageBox, I don't think they should be used for debugging. You may use them for genuine Information reasons , but it would be a temptation to leave the extra debug events in your code. That wouldn’t be a good idea as they are quite expensive to fire, especially row by row. For debug purposes I prefer using the System.Diagnostics.Debug.WriteLine method. Use the DebugView tool from Sysinternals to capture the messages when you are actively debugging, but without a listener they are passive and less overhead than events.

  2. Anonymous says:

    Hi Ben,

    If you want to use the messagebox.show method at design time simlpy add the line:

    using System.Windows.Forms;

    to the top of the script. The dll reference is already in the project by default when created by BIDS.