trying to pass in parameter from outside; trying to see global variables
-
Hello,
I have created a package that more or less does what I need it to with one exception. In a couple of the tasks I have hard-coded a variable. I'm trying to figure out how to pass in a parameter into the package to replace these hard-coded values.
I'd like the package to default to using an XML configuration for the variable unless the operator changes the parameter when creating a SQL job. Then I'd like the SQL job to override the XML config file.
I'm running the package on SQL server (i.e., not BIDS). In the package, I've created a variable named "Package_ID", of type int32, with value of 1 and package-wide scope. I've also created a new XML configuration file that contains just the value of this variable.
Unfortunately, the rest is awful. In the first script task that currently uses the hard-coded value, I have specified 'Package_ID' in the ReadOnly Variables list.
The task contains a subroutine "Write_Debug_Stmt" that enables me to write logging information into a table. In the script task, I have added this code at the top:
Dim vars As Variables
Dim v As Variable
Dts.VariableDispenser.GetVariables(vars)
Call Write_Debug_Stmt("before v loop")
For Each v In vars
Call Write_Debug_Stmt("looping....")
Call Write_Debug_Stmt("name + value: " & v.Name & ". " & v.Value)
Next
Call Write_Debug_Stmt("after v loop")
Call Write_Debug_Stmt("Package ID: " & Dts.Variables("Package_ID").ToString)
In my debug table, I have "before v loop" followed by "after v loop". There is no looping among the 15 or so variables I have defined in the package, all with package-wide scope, including "Package_ID".
In addition, the "package ID" line gives me: 'Package ID: Microsoft.SqlServer.Dts.Runtime.Variable'. I don't get the value of 1, which I have defined (in BIDS) as the value of the variable (i.e., it shows up in the "Variables' window).
How come none of the package-wide variables appear in my "for each" loop? How come I don't get a value of 1 for Package_ID? How come Microsoft tells me the value of Package_ID is "Microsoft.sqlserver.Dts.Runtime.Variable"?
Finally, does anyone have an elegant way to define with a package-wide variable from a configuration file that can be overriden by a SQL job?
thanks!
-
Not sure I'm understanding.
You say you aren't using BIDS, you're running a SQL job (stored procedure?), but then you refer to tasks such as Script Task and a packageID, which I would naturally associate with an SSIS package.
It seems that you are asking about a package-level variable that a different SQL Job can change? I don't think that is possible.
-
I'm designing the package in BIDS and running it on a SQL Server. In a couple of script tasks, I'm trying to eliminate global variables and use a package-wide variable that can be specified when I run the package. I've read this page:
Unfortunately, I have no idea why my variable "Package_ID", with package-wide scope, is not appearing as part of the "variables" loop In my script task. I also have no idea why I am not seeing a value of 1 for Package_ID in my code.
thanks for the help,
-
I have some parent package variable configurations for ServerName and such, which I can set at sql job level for input to package. I've not dealt with other config types so can't tell you if this is the best way to pass in from the outside.
On the actual SQL Agent Job config, it's the SetValues tab within a step where I assign the variable values.
While I've not had a use for passing these variables into different pkg components, there is something about locking variables for use, which I thought was confined to script task/script component.
Sorry can't be more help but interested in how you resolve this.
-
Well, the reason that execution never enters my loop (described in the first post above) is because I needed the "lockforread" method to be executed prior to it. This tip comes from here:
The new code is:
Dts.VariableDispenser.LockForRead("User::Package_ID")Dim vars As Variables
Dim v As Variable
Dts.VariableDispenser.GetVariables(vars)
Call Write_Debug_Stmt("before v loop")
For Each v In vars
Call Write_Debug_Stmt("looping....")
Call Write_Debug_Stmt("name + value: " & v.Name & ". " & v.Value)
Next
However, I'm baffled as to why I am getting zero (0) as the value of "Package_ID". I've set the value to 1 in the varibles window. Also, I have an XML configuration file that sets the value to 1. So why does the package say the value is 0?
-
Well, two days later I still have NO IDEA how to set and read values in a variable that has package-wide scope. Thank you Microsoft for making it SO DAMN HARD to work with globals in SSIS packages.
Why does Microsoft maximize the difficulty of using package-wide variables in SSIS? It isn't hard using global variables in VB.NET or VBScript. So, why, why, why does Microsoft make such a simple need so excruciatingly difficult here?
Again, I have created a variable named Package_ID that has package-wide scope. In one task, I have defined it to be within the ReadWriteVariables list. I have this code:
Call Write_Debug_Stmt("original package ID value: " & Dts.Variables("User::Package_ID").Value.ToString)
Dts.VariableDispenser.LockForWrite("User::Package_ID")
Dts.Variables("User::Package_ID").Value = 19
Call Write_Debug_Stmt("changed package ID value: " & Dts.Variables("User::Package_ID").Value.ToString)
Dts.VariableDispenser.Reset()
The task correctly writes 19 to the debug table.
Next task, I have set Package_ID among the ReadOnlyVariables list. Here's the code that's there:
Dts.VariableDispenser.LockForRead("User::Package_ID")
Call Write_Debug_Stmt("Package_ID: " & Dts.Variables("User::Package_ID").Value.ToString)
Dts.VariableDispenser.Reset()
Naturally, the debug table reports that Package_ID is zero (0).
Is the problem that the "Reset" method (in the first task) changes the package_ID back to its initial state? So far I don't see an alternative way to unlock the variable after I've changed it.
If someone can help me escape this dungeon of darkness I'd appreciate it. Thanks.
-
Hi CafeScott
I did a quick test using Visual Basic (I work with C#), so it was very simple, I created 3 package level variables, User::TestVariable; User::WriteVariable and User::PackageID; assigned the values Hello World to the TestVariable and Mine to the User::PackageID one.
Within the Script Task at the Control flow, I declared User::TestVariable, User::PackageID and System::PackageID as read-only variables and User::WriteVariable as Read-Write variables, within the script code, I just displated a pop-up message as shown below:
Public Sub Main()
'
' Add your code here
'
MessageBox.Show(Dts.Variables("User::TestVariable").Value.ToString)
MessageBox.Show(Dts.Variables("System::PackageID").Value.ToString)
MessageBox.Show(Dts.Variables("User::PackageID").Value.ToString)
Dts.TaskResult = ScriptResults.Success
End Sub
I am not using the method Dts.VariableDispenser.LockForRead("User::Package_ID")
The little test worked fine, I did not use the method Dts.VariableDispenser.LockForRead("User::Package_ID") as I do not believe it is required because the variables are ready available in the Dts.Variables() collection.
If you are not getting the expected value in your variable User::Package_ID, it is likely to be the logic within your package, either the variable is not getting initialized or its initial value is wiped off within its logic.
I suggest the following test, add a Script Task as the very first task of your package; create a test variable (User::TestVariable) with whatever default value, then, pass as read-only the newly created User::TestVariable and your User::Package_ID, inspect their values with a message box, this simple test will tell you that Package_ID is getting initialized at the beginning of your package. You can move your debugging Script Task until you find where User::Package_ID is losing its information.
Cheers,
Hope this helps,
Rock from VbCity -
Rock, you Rock!
I created a test similiar to what you're describing using a package-wide variable named WriteVariable of type string. I added two tasks at the top of my package, one to write to the value, and the second one to verify that the value was retained. It worked.
Then I added a new variable named Package_ID_str of type string. I repeated the same thing with the new variable, and it worked.
Apparently, package-wide variables simply don't act as globals when they are of type Int32. (At least, I have no idea what it takes to get them to retain values across multiple tasks.) Meanwhile, package-wide vars that are of type string remember their settings across multiple tasks.
Thanks a lot for helping me get out of this dungeon!! 😀
-
Well, I tried with a package level integer variable with a default 123 value, it worked, in both: the first script task and on the second ST inside a sequence task.
It could be possible that you have two variables with the same name, keep in mind that if you add the variable "User::Test" after clicking anywhere on the package design area (outside any task) you are creating a package level variable, but if you add a variable "User::Test" (yes, with the same name) while the control task is selected you will end with two "User::Test" variables, the control tasks taking priority anywhere inside that controls task.
The attached picture illustrates this scenario "two" User::Test variables, It seems this is happening to you.
Cheers,
Hope this helps,
Rock from VbCity -
Here's a bizarre epilogue to my last post.
I can get a package-wide variable of type string named "Package_ID_str" to retain a value in a second task after the value has been set in the first task. However, I can't get a package-wide variable of type stirng named "Package_ID" to do it! (By the way, I deleted the Package_ID variable and added it in again.)
To test, I have two package-wide string variables, Package_ID and Package_ID_str.
In task I, I have both in the ReadWriteVariables list. The code:
Dts.Variables("User::Package_ID").Value = "1"
Dts.Variables("User::Package_ID_str").Value = "1"
In task 2, both variables are listed in the ReadOnlyVariables list. The code:
MessageBox.Show("Package_ID: " & Dts.Variables("User::Package_ID").Value.ToString)
MessageBox.Show("Package_ID_str: " & Dts.Variables("User::Package_ID_str").Value.ToString)
The first message box (Package_ID) shows the unintended value of zero. The second (Package_ID_str) shows the expected value of 1.
Is this a bug? This is really bizarre, imo.
-
Uh, never mind my last post. Rock, you rock yet again.
I didn't see it; but there was another variable named "Package_ID" in my list. I removed it and it is working.
sorry for the histrionics! Most of this thread has been a waste of time--sorry about that.
-
Hi CafeScott,
Did you check my previous post?, it seems you are having a variable scope issue, the behavior described sounds like you created the variable User::PackageID twice, the first time at package level and the second time at the control task level.
Hope this helps,
Rock from VbCity -
cafescott (6/15/2012)
Uh, never mind my last post. Rock, you rock yet again.I didn't see it; but there was another variable named "Package_ID" in my list. I removed it and it is working.
sorry for the histrionics! Most of this thread has been a waste of time--sorry about that.
No worries, I rather prefer to get a notification message of some sort when entering a variable name already defined with a higher scope, sadly, it does not do that.
Cheers,
Hope this helps,
Rock from VbCity -
Rock, in my last post I admitted that there was a second variable named Package_ID that I had not seen. Your post helped me recognize that fact.
thanks!
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply