Debugging the SSIS Script Component

I've been doing a lot of work with SSIS recently, and learning a lot on the way. One of my tasks over the last week was to convert each row of a result set in to XML and post it to a web service. The web service only accepted one row at a time, and generating XML in SSIS is not the most intuitive of tasks, so I wrote a script component to do the conversion work.

Anyone who's tried anything more advanced than a "Hello World" example in a script component will be able to tell you that you cannot use breakpoints in them - making it quite difficult to debug even the simplest of code. For a developer such as myself, it's like having an arm removed in a horrific train accident. I had a bit of a task ahead of me - I had to create an XML document with tags containing attributes and values, attributes and child elements, child elements and values, attributes, child elements and values, then self-closing tags with some of the above... whilst not exactly a daunting task, it certainly required a bit of debugging to get right.

MSDN suggests that you should use System.Windows.Forms.MessageBox.Show() to show any debugging information. Aside from the obvious ugliness of this method, it stops the package flow, and if the code is left in the package when deployed, you can lock up the package execution at runtime. Now... I'm not entirely sure why this hadn't came to me before now, but in a flash of super-hero-esq inspiration and inside-out underpants, I remembered a similar issue I had a few years ago...

System.Diagnostics.Debug.BloodyHorrible()

DebugView to the rescue!

I used DebugView extensively when I was developing a SharePoint 2007 site a few years ago. It's a tiny little app from the SysInternals suite (god bless Cogswell and Russinovich) which monitors the Win32 OutputDebugString, which is exactly what System.Diagnostics.Trace.Write writes to by default.

So. here's the main bulk of my Script Component code, along with the very simple tracing code:

    public override void Input0_ProcessInputRow(Input0Buffer Row)

    {

        // For each row, compile XML and submit to the web service

        var sb = new StringBuilder();

        using (var writer = XmlWriter.Create(sb, new XmlWriterSettings()

        {

            Indent = true

        }))

        {

            // Write to the XML Writer

            writer.WriteStartElement("request");

            writer.WriteAttributeString("action", "insert");

            writer.WriteAttributeString("database", Variables.MDatabase);

            writer.WriteStartElement("credentials", "");

            writer.WriteAttributeString("server", Variables.Server);

            writer.WriteAttributeString("layout", Variables.ClaimsLayout);

            writer.WriteAttributeString("username", Variables.Username);

            writer.WriteAttributeString("password", Variables.Password);

            writer.WriteEndElement(); // <credentials>

            writer.WriteStartElement("columns");

            // Data columns

            writer.WriteColumn("NCDAffected", Row.NCDAffected);

            writer.WriteColumn("PolicyReference", Row.PolicyReference);

            writer.WriteColumn("Status", Row.Status);

            writer.WriteColumn("TypeOfClaim", Row.TypeOfClaim);

            writer.WriteColumn("FileID", Row.FileID);

            writer.WriteColumn("TotalOutstandingReserves", Row.TotalOutstandingReserves);

            writer.WriteColumn("TotalPaymentsRecoveries", Row.TotalPaymentsRecoveries);

            writer.WriteColumn("ClaimDate", Row.ClaimDate.ToString("dd/MM/yyyy"));

            writer.WriteColumn("NotificationDate", Row.NotificationDate.ToString("dd/MM/yyyy"));

            writer.WriteColumn("TransactionDate", Row.TransactionDate.ToString("dd/MM/yyyy"));

            writer.WriteEndElement(); // <columns>

            writer.WriteEndElement(); // <request>

        }

        //System.Windows.Forms.MessageBox.Show(sb.ToString());

        System.Diagnostics.Trace.Write(sb.ToString());

    }

}

public static class Extensions

{

    public static void WriteColumn(this XmlWriter writer, string columnName, object value)

    {

        writer.WriteStartElement("column");

        writer.WriteAttributeString("name", columnName);

        writer.WriteValue(value);

        writer.WriteEndElement();

    }

}

The bold line is the one we're interested in - it's transparent to the user and won't affect performance if deployed with the application. In fact, it may even aid debugging on a production environment. Of course, it may expose a security hole if used incorrectly, but it does a much neater job of posting debug output that using MessageBox.Show(). It posts the following to DebugView:

System.Diagnostics.Debug.Trace.MuchCleaner()

So, until MS make SSIS Script Components easier to debug, I'll be sticking with this method :)