Tuesday, 5 April 2011

Breakpoint does not work within SSIS Script Component

Case
I cannot debug (use breakpoints) in a Script Component. What's wrong?

Solution
The Script Component does not support the use of breakpoints. Therefore, you cannot step through your code and examine values as the package runs. There are a few workarounds to still get some form of debugging.

* Script Task not debugging? Switch Project Properties to 32bit for SSIS 2005 and 2008! *
1) MessageBox
The good old messagebox is a simple quick way of displaying some value. But it could be a little annoying with a lot of records.
// C# Code
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    System.Windows.Forms.MessageBox.Show("SomeMessage: " + Row.YourColumn);
}

Messagebox.Show

















2) Fire events
You can fire events and watch the execution result tab.
// C# Code
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    bool fireAgain = true;
    this.ComponentMetaData.FireInformation(0, "ScriptComponent", "SomeMessage: " + Row.YourColumn, string.Empty, 0, ref fireAgain);
}

Partial Execution Results








3) Trace log
The .Net framework has it's own trace features which you can use the write messages to a listner. There are a lot of listners (third party, open source or your own custom handmade .net application), but your can also download one from Microsoft.com: DebugView for Windows.
// C# Code
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    System.Diagnostics.Trace.WriteLine("SomeMessage: " + Row.YourColumn);
}











Let me know if you have an other workaround. And also see/vote for this Feedback request on Microsoft.com.
* Update 17 November: Debugging has been added in SQL Server 2012 RC0 *

3 comments:

  1. I tried the trace log and it works a treat! Thank you so much for working this out and publishing it

    ReplyDelete
  2. What I do is build functionality in classes and create a testing environment in a standard WindowsApplication. This way I have to do a minimum of coding in the awkward tool-less script-coding environment of SSIS, because the classes are already tested, debugged and ready to use.

    ReplyDelete
  3. @Tobias: Thanks for your suggestion. Do you use a DLL inside the Script Component or do you test it in a WindowsApplication and copy and paste it to the Script Component?

    And some good news... debug has been added in the 2012 version of SQL Server/SSIS.

    ReplyDelete

Please use the SSIS MSDN forum for general SSIS questions that are not about this post. I'm a regular reader of that forum and will gladly answer those questions over there.

All comments are moderated manually to prevent spam.