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.
The value of the file I am renaming is getting supplied by the values in this table:
Which I’m supplying to SSIS via the variable named vFullDirectory which is populated by the Execute SQL Task:
(here is the variable configuration)
(here is the view of the configuration of the File System Task)
(and here is the configuration of the Execute SQL Task)
(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:
The errors are not entirely helpful:
So here is what I do:
- Add a script task just after the Execute SQL task
- Configure the script task to show a message box which contains the value of the variable.
- Rerun the package so I can see the value of the variable.
- Fix the package based on the information gleaned from the above two.
Here we go:
- I’ve dragged the script task over, and configured the precedence constraint from the execute sql task to it.
- I use Visual Basic for my scripting because I learned it first and haven’t learned C#. (I know C# is COOLer.)
- I configure the ReadOnlyVariables to the variable in question: vFullDirectory. Then I click on Edit Script…
And I replace the words ‘ Add your code here
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:
where MyName = your case sensitive variable name.
so returning to the Visual Basic Editor, what I should enter is this:
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:
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)