Sooner or later, you will need to debug the variable values in your SSIS package when something is not working as expected. With SSIS you have many choices when it comes to debugging your variables, breakpoints, stepping over, stepping in, the local or watch windows, etc. However sometimes you just want a quick and simple approach, enter the venerable msgbox function vbscript (or Messagebox in C#).
In this example I am using a variable to store xml results. I defined a variable for storing an XML result from my SQL data query, and another to store for the HTML result after the transformation by a XSLT file.
Step 1: Declare your variables
Step 2: Create a SQL Task to get the XML from the SQL data query result.
Step 3: The transformation didn’t generate the correct HTML as expected. So I want to set up a script task to tell me what is wrong with the variable varETLStatusXML.
Pass the variable varETLStatusXML as a read only variable to the script task.
Click the Design Script… button, and code the following.
Option Strict Off
Public Class ScriptMain
Public Sub Main()
MsgBox(Dts.Variables(“varETLStatusXML”).Value, MsgBoxStyle.Information, “varETLStatusXML”)
Dts.TaskResult = Dts.Results.Success
If this is a XML stream you want to keep as a file, add this to the VBA code:
Dim sw As New IO.StreamWriter(“C:\varETLStatusXML.xml”)
Step 4: Execute the SQL task and the script task. Now I see that my XML was not generated correctly. Time to figure out why.
… and there you have it, a simple method of displaying your variables for debugging purposes.