//
you're reading...
Data Warehouse, SQL Server, SSIS

SSIS – Display Variable Value in a Script Task

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

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

… and there you have it,  a simple method of displaying your variables for debugging purposes.

Bonn Appetit!

ldix6

Advertisements

About ldgaller

Accomplished Data Warehouse Architect, DBA and Software Architect with over 15 years of professional experience and demonstrated success designing and implementing solutions that improve business functionality and productivity. Highly diverse technical background with proven ability to design, develop and implement technology on an enterprise level. I approach all projects with passion, diligence, integrity, and exceptional aptitude.

Discussion

No comments 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: