Debugging SSIS Variables—and a Happy Announcement from the Datachix.

Audrey and I have decided to blog regularly and alternately on Tuesdays. We’ve each had good runs of things and had months where we posted a lot, but we’ve never had great consistency. So now we’ve committed to a schedule.

From the Book of Bloggering Chapter 23, Verse 9: The Datachix shall blog weekly. The day of the week on which they shall post shall be Tuesdays. Mondays will not be the day the postings shall occur, unless it is only to load and schedule a post for the following day–Tuesday. Neither shall Wednesdays be the day of the posting, unless there be a time zone situation where the reader is residing in Wednesday time whereas the Datachix (the said aforementioned poster/bloggers) are residing in Tuesday, and therefore posting their blog. Thursdays are right out, as are Fridays. Don’t even mention Saturdays.

As a result of this scheduling pressure, I’ve spent my Thanksgiving Holiday writing like a bit of a madwoman, hoping to get some material stockpiled for later. I take this advice from many successful bloggers.

Another bit of sage blogging advice is to write for your own reference. Record things on your blog which you use fairly often, but just infrequently enough to forget the exact syntax. Save yourself the process of repeating your Google searches. So on that note I present,

How to Debug the Values of Dynamically Populated Variables in SSIS Using a Script Task and a Message Box

So, I’ve spent the entire weekend writing and writing and writing. Now I’m trying to edit and break it down into digestible pieces. I’ve got some posts written where I intend to show you all how to populate a variable in SSIS with the result of an Execute SQL task. During the writing of that post I realized I was going to need to illustrate how to debug your results.

Sometimes your variable gets populated perfectly and the rest of your package works so well that you know you nailed it and you don’t need to use this method. Sometimes you are getting errors and you know the problem is the value of your variable, which is getting populated by an expression or something dynamic, and therefore it is difficult to debug without knowing the value of the variable at runtime.

At times like these I use a Script Task with a message box containing the variable. I always forget the syntax. Then I sit there and shame myself, trying to fill it in and hoping between my foggy memory and intellisense, I’ll stumble on the correct syntax without having to look it up.

I almost always have to look it up. So here is a made up situation:

I’m in SSIS. I am using a File System Task to rename this file.

Figure 1

The value of the file I am renaming is getting supplied by the values in this table:

Figure 2

Which I’m supplying to SSIS via the variable named vFullDirectory which is populated by the Execute SQL Task:

(here is the variable configuration)

Figure 3

(here is the view of the configuration of the File System Task)

Figure 4

(and here is the configuration of the Execute SQL Task)

Figure 5

(there is a bit more to populating the variable than what is seen in this screen shot, but that will be covered in later posts).

Ha ha ClientFile.txt ! Your days with that name are numbered!

Here is what I see when I run my package:

Figure 6

The errors are not entirely helpful:

Figure 7

So here is what I do:

  1. Add a script task just after the Execute SQL task
  2. Configure the script task to show a message box which contains the value of the variable.
  3. Rerun the package so I can see the value of the variable.
  4. Fix the package based on the information gleaned from the above two.

Here we go:

Figure 8

  1. I’ve dragged the script task over, and configured the precedence constraint from the execute sql task to it.
  2. I use Visual Basic for my scripting because I learned it first and haven’t learned C#. (I know C# is COOLer.)
  3. I configure the ReadOnlyVariables to the variable in question: vFullDirectory. Then I click on Edit Script…

Figure 9

And I replace the words ‘ Add your code here

With: MsgBox()

And that’s usually (ok almost always) where I lose the plot. How do I put the value from the variable in there again? Ummmmmmmmmmmmmmmmmmmmmmmmmmm

(I go look it up).

Here is the correct syntax:

Msgbox(Dts.Variables(“MyName”).Value)

where MyName = your case sensitive variable name.

so returning to the Visual Basic Editor, what I should enter is this:

MsgBox(Dts.Variables(“vFullDirectory”).Value)

Mind you this is a plain vanilla message box with no interesting titles, but we are not using this for our end users, so forgive my economy of parameters.

I close up the visual studio IDE and run the package again:

Figure 10

Now I can see what the problem is. I forgot the “ \ “ in between my directory and my filename. Now I can go in and fix the problem. Run through the package again, and when it is working properly, take the script task back out.

But will I remember this:

—- Msgbox( Dts.Variables(“MyName”).Value)

next time?

Probably not.

–Julie

Published by

Julie Smith

One half of the Datachix.com. ETL person. This picture is the best of me ever taken. Don't be too sad when you meet me for real :)

8 thoughts on “Debugging SSIS Variables—and a Happy Announcement from the Datachix.”

  1. I love it when my fellow Datachix posts something I don’t know how to do. It’s like I’m learning from my own blog. Freaky!

    Oh, and I see the pressure is on… I guess I’d better get my post ready for next Tuesday. 🙂

  2. Nice post. You know you can also use the locals window to determine the variable values if you set a break point. This method is a little safer as you won’t potentially release code to production that still has an interactive alert box. Still, having the box pop up can be nice…

    1. Thanks Eric. I have never used the locals window, but remember watching a video on its use by Brian Knight. I guess the msgbox techique has always suited my needs. I will have to learn the locals window as well. It sounds more elegant. Thanks again. –Julie @Datachix1

  3. Nice post.
    We usually rely on logging to debug output. You can add System.Diagnostics namespace in the Script Task or Component and add lines like
    Debug.Writeline(“some message”)
    Then it is easy to track execution even withoug Visual Studio – just download dbgview from sysinternals.com – it is a must-have for developers.

Leave a Reply