Home > SSIS Best Practices > SSIS – Display Variable Value in a Script Task msgbox function

SSIS – Display Variable Value in a Script Task msgbox function

If you use variables in your SSIS package, soon or later, you will need to debug the variable values when something is not working as expected. There are many ways to do this, with the rich debugging capabilities, such as breakpoints, stepping over, stepping in etc, in Integration Services. But my favorite is still the old plain msgbox function is VBA.

Step 1: Define your variable in the Variable Window. In this example, I defined a variable for storing an XML result from my SQL data query result, and another one for the HTML result after the transformation by a XSLT file.

image

Step 2: Create a SQL Task to get the XML from the SQL data query result.

image

image

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.

image

Pass the variable varETLStatusXML as a read only variable to the script task.

image

Click the Design Script… button, and code as the following.

image

Option Strict Off

Imports System
Imports System.Data
Imports System.Math
Imports System.Net.Mail
Imports Microsoft.SqlServer.Dts.Runtime

Public Class ScriptMain
    Public Sub Main()
        MsgBox(Dts.Variables("varETLStatusXML").Value, MsgBoxStyle.Information, "varETLStatusXML")
        Dts.TaskResult = Dts.Results.Success
    End Sub
End Class

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")

sw.Write(Dts.Variables("User::varETLStatusXML").Value.ToString())

sw.Dispose()

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.

 

image

Categories: SSIS Best Practices
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: