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 *
I tried the trace log and it works a treat! Thank you so much for working this out and publishing it
ReplyDeleteWhat 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@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?
ReplyDeleteAnd some good news... debug has been added in the 2012 version of SQL Server/SSIS.