December 11, 2009 at 6:15 am
Ive setup SSIS to use several variables throughout the process. The first stage in the process reads a value from one of the variables to use as part of a filename (the rest of the filenename is built using the current date)
I originally set the value of the variable to 'DataFile', but after running the package I realised Id made an error with the value since the file was to be downloaded from an ftp server, Id missed out the '/' from the beginning, so I changed the value of the variable to
'/DataFile'
When I run the package and step through the code, the value is always being read in as 'DataFile' the old value !
Nothing I can do will change that. I had set the package up to use a config file, but i changed it back to not use them and even deleted the config file from my system, yet the variable is still being read in as the old value !!!
This is driving me nuts ! I cant see where its getting this value from. Ive even deleted the variable and recreated it, but its still the same.
Can anyone help ???
December 11, 2009 at 7:07 am
After several hours of headscratching ive found the problem. For some strange inexplicable reason, the Variable window decides to remove particular variables from view (not delete them, just hide them). They are still there somewhere, but hidden from view. I noticed that my variable had disappeared and couldnt understand why, I hadnt deleted it. So i decided to add it back into the variable window, at this point id given it the wrong value, so it gave me an error at runtime. I went back into the variable window, edited the value, saved it and ran the package again. This is where the real trouble began. No matter what, the variable always contained the old value. After hours of trying to figure out what the hell was going on, suddenly two versions of the same variable appeared in the editing window. The variable editor, had initially hidden my original variable, so I added another WITH THE SAME NAME. This was accepted, resulting in two identical variables with different values. At runtime my code grabbed the first one and took the value from that.
So THERE ARE TWO BUGS with the variable window. The first one is the random hiding of variables, the second is allowing two variables with the same name to be entered (I cant believe for one minute this is by design)
Im using VS2005, maybe this has been fixed in VS2008.
December 11, 2009 at 7:13 am
Actually, without seeing the actual package, it depends on the scope of the variable. If you declare a variable in a FOREACH container named MyForEach1, you won't see that variable at the package level or in another container.
I have had a similar issue while declaring variables when I didn't realize I wasn't declaring the variables with the correct scope needed.
December 11, 2009 at 7:21 am
Hi,
Neither 'issue' is a bug, it is by design - it's the 'scope' of the variable/s.
When you are at the top level (ie when nothing has focus but the package itself) you see only the package scoped variables (ie those available to any component in the package). And when you subsequently click on (say) a dataflow task, you see both the Package scoped variables *and* any variables scoped to that task. You'll start to notice that you can control when variables 'appear or disappear' - for e.g. say I have on my control flow 3 tasks. I can click on Task1 and then add a variable, that variable is then scoped to that task (say it's a data flow task, the variable is fully visible/accessible to any subsequent component/task *within* that particular data flow task).
So, it pays to pay attention to *what* you have selected when creating variables 🙂 Often in early stages of SSIS development, you find you want to add only package scoped variables but over time you will move to using variables scoped to the component for which you need them. Sort of like the old VB approach of creating all of your public variables at the start of the app, versus creating only the true global ones there and creating and using specific variables within individual methods/functions 🙂
You'll notice the last button to the right on the variables window, it allows you to select what columns you'd like to see in the variables window. I normally make this window quite a lot larger than the default and then choose which columns I'd like to show, one of which is the Scope.
HTH,
Steve.
December 11, 2009 at 7:23 am
Lynne, you beat me to it 🙂 Apologies for re-stating what you had already
Steve.
December 11, 2009 at 7:39 am
All my variables are created at the package level so they are global throughout, id expect to see them at all levels.
December 11, 2009 at 7:42 am
I can see the logic behind this, its very confusing for a novice though, but allowing variables with the same name, even though they may be different scope, is crazy in my opinion
December 11, 2009 at 7:51 am
Scoping of variables is pretty standard in development languages (e.g. C# , java, VB.net etc). If this wasn't available to you, especially on a large code-base, having to have absolutely uniqe variable names across an entire application would be extremely painful. While not as relevant for SSIS, it does give additional flexibility. I have on occasion used variables with the same names at different scopes, but will admit it's pretty rarely.
Steve.
December 11, 2009 at 8:17 am
I totally understand scope with respect to standard development, but with SSIS I dont think its quite the same thing (thats my opinion anyway) Im really new to SSIS, this is the first thing ive ever written, so there is a certain learning curve involved.
Maybe if there was some way to see if duplicate variables have been created without selecting each specific task, I could have avoided wasting hours trying to find out what the problem was. Now that Im aware of it, i wont be making the same mistake again
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply