One of the more difficult aspects of dealing with SSIS packages is the need to find files dynamically. Meaning we don’t always know the exact name of the file or the file has the same base name but has dates appended to it, etc. Variables (and For Each Loop containers) are quite useful for dynamically determining and creating file names. But when a variable goes rogue, discovering the issue can be quite difficult. SSIS has a penchant for delivering up generic errors like “file not found” or “file cannot be opened” with no further explanation. And SSIS does not display file paths and names in the Variables container when the values are dynamic instead of hard coded.
There’s an easy method for troubleshooting SSIS variables, however, that can save DBAs a lot of headaches and a little time. Use a Script Task. At the very least, it will answer the question of whether or not the variables were loaded with the correct values at runtime. In my example I use a For Each Loop container to set the variable, but honestly, the variable setting could come from any task. The key is to put the Script Task after the task that loads the variable, whereever that may be.
FYI: This is a Visual Basic example. I’m sure a similar command exists in C#. I just don’t know what that command is.
First, let me give you a look at my For Each Loop container settings. Here’s the Collections page where I set the directory & file names to be searched for:
In this example, I’m only concerned with the filename and extension. It really doesn’t matter if you get the full path or just the file name. You can use Script Task troubleshooting with any version of the name you grab. Below is where I map the filename / extension to a package level variable.
I have 2 variables. The FileName variable which is loaded with the files found by the For Each Loop and the TestPath variable which contains my destination path.
Since TestPath is going to be my final destination path, I need to append the path “D:\Temp” with the file names. I click inside the Value box of this variable to set the Expression and the EvaluateAsExpression properties.
Because I want to check the value of TestPath before I create my File System Task (to move my files), I drop a Script Task inside of the For Each Loop container. This will be evaluated after the variable is loaded and before anything else occurs. In the Script tab, I change the Script Language property to “Visual Basic” (this MUST be done first or it will save as a C# task when I click “Edit Script” and I won’t be able to reset it). Then I add TestPath into the ReadOnlyVariables line, like so:
Then I click the Edit Script button which opens the script editor. Inside the script editor, I create a message box that will popup during package run (pausing the package run until I click the message’s OK button) and tell me what my variable value is. The code I’m using is:
MsgBox("This new path for my pictures is " + Dts.Variables("TestPath").Value.ToString)
Now I close the script editor, click OK on the Script Task, save the package and run it in Debug mode. Here’s what my message says:
The first thing I notice is that my slashes are showing up as doubled. I put two slashes in my path because the first one is an escape character to ensure the second one is read as a literal slash. So why are both slashes showing up in the message box when only 1 slash should show up? Thinking about it for a moment, I realize I put in the wrong type of slashes. I’m using webpage slashes, not file path slashes. (Good thing I checked before I built the entire package! This would have given me fits to resolve later on!) So I edit my expression to use \\ instead of //, save the package again, and re-run it. Here’s the new message box.
YAY! Variable is being loaded correctly. I can now delete (or disable) the Script Task and continue building my package knowing that if I run into errors later on, it’s not because the variables are wrong. See? Troubleshooting SSIS variables is easy!
