From the book of Bloggering, Chapter 59 Verse 11: Thou shalt humbly accepteth better solutions to thine posits as they appeareth in thine Comments Section. Yea verily, although thou may fearest change, thou shalt learneth new and better coding in this manner and be pleasing in the sight of thine employer.
I had a blog post several weeks ago about debugging the values of SSIS Variables. I shared the method I have always used, which was simply (actually maybe it’s not simple at all) adding a script task into the control flow and placing a message box in the control flow, displaying the value of the variable.
Eric Wisdahl commented that another way of seeing the value of your variables (and in truth a less labor intensive way at that), is to use the locals window from the DEBUG (emphasis mine) menu in the BIDS designer. I responded in typical change-fearing fashion: saying that once I had found my old way, I just stopped looking for alternatives. A classic example of “we’ve always done it this way”. Now in the grand scheme of things is this completely horrible? No, because I am “getting it done” , but as you’ll see, my method takes approximately 20 mouse clicks and keystrokes, and as I self-deprecatingly pointed out, almost always involves a Google search. The locals window takes … many fewer keystrokes, fewer mouse clicks, less brow furrowing, and fewer memory (and ultimately Google) searches.
Did I feel a little stupid? Yeah. But I’m willing to share all of the this as an example of why it is important to interact with your fellow SQL folks. If I were not a social person, inclined to share what I know and seek out what I do not, I would have continued debugging with this method and not grown. Again, is this particular oddity in my debug arsenal the worst thing I could do regarding my career? No of course not. But a pattern of resisting improvement certainly can. So anyway back to the matter at hand: Debugging your SSIS Variables Using the Locals Window.
Begin Lift from Previous Blog Post: (Feel free to do the Wayne’s World Flashback jingle in your head……….) —>
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:
<———– End Lift .
Begin Improved Method —>
What we’re going to do is add a breakpoint to the control flow task which is erroring out:
Select the file system task and right click. Select Edit Breakpoints.
Select the box beside the break condition “OnPreExecute” event. (by the way for more on using the breakpoint editor, see John Welch’s post here).
Close the Set Breakpoints window. Here is what the task looks like with its eagerly waiting to be helpful breakpoint:
(I’m a breakpoint, I can’t wait to make something “break”)
Now let’s start the package again and see the breakpoint work its magic:
The breakpoint shows us that it is stopped with the yellow arrow. It didn’t error out. It’s waiting for us to tell it to continue.
Now since we know the problem is most likely the value of that variable we are populating with the Execute SQL task, let’s go take a look at its value using the Locals window:
Go to the Debug Menu and click Debug>Windows>Locals
The Locals window will appear somewhere (where depends on your settings) mine usually appears in the bottom of my screen. Here is a first glance:
Click on that plus sign beside the word Variables to see all of the variables and their values.
So looking at the value for the pesky variable (circled in red) we have been troubleshooting I can see that the problem is that I do not have the slash between my directory name and my file name. Now I can go in and fix the problem in the Execute SQL task, take the breakpoint out and be done. (BTW If I wanted to continue the execution of the package I would hit F5 or select Continue from the Debug Menu.)
But one thing I did notice which happens using the Locals Window, which may be the only reason a person would want to use the message box method instead of or in addition to this method is the part that I highlighted in yellow in the above shot. Notice that the Locals window puts in the Escape characters beside all of the slashes? If a person were dealing with many file type values in his/her variables and specifically troubleshooting them, looking at the locals window with its many double slashes could get confusing. I found at least one person who thought so here. Nevertheless, I think the Locals window is much better for day to day SSIS debugging. Thanks Eric and to Piotr, I haven’t investigated your suggestion fully yet but thanks to you as well for the insight.