Accessing Package Variable in a Script
-
How do I access package level variables within a Script Task?
I have a package level variable named "vendorName". I use this variable to retrieve the correct vendor id to be inserted in the data flow. The package runs fine when I hardcode the value for the vendorName variable in the variable panel. I can't make it work using the Script task. This is what I did
1) I added "User::vendorName" in the ReadWriteVariables property of the Script section in the ScriptTask Editor.
2) I used this code to assign value Dts.Variables("User::vendorName").Value = "Vendor A"
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett -
I ran some tests again and I found out that even the hardcoded version IS NOT working at all. I was given the impression it worked because of the default value for an integer variable, which is zero, happens to be the id of the vendor name I used initially for the test. When I used another vendor and expected another value, zero was still returned to me. That's the time I realized I was not geting the value of the output parameter of the stored procedure I use in the Execute SQL Task. I have 2 package level variables vendorName, vendorId which I mapped to the stored proc params @vendorName and @vendorID respectively. The sproc works outside SSIS. Here are the values for some of the Execute SQL Task
General section
ConnectionType = ADO.NET
SQLSourceType = DirectInput
SQLStatement = EXEC dbo.GetVendorId @vendorName, @vendorId OUTPUT
IsQueryStoredProcedure = False
Parameter Mapping
Variable Name Direction Data Type Parameter Name
User::vendorId Input Int32 @vendorId
User::vendorName Output String @vendorName
My question now becomes...
How should I supply and retrieve parameter values to the sproc in a EXEC SQL Task?
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett -
Two questions in one..
Your way of setting the variable should work. Try dropping the User:: part.
As for the SQL part, I use OLE DB, not .NET and you map the params by using @MyParam = ? and map them to variables using the zero based index. Works great.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Thanks Crispin. Everything is green now. However, it seems like the value I set for the input parameters is not transferred to the parameter. I initialized the value of my variable in the Variable pane. I found out about this weired behavior when I inserted my package variables into a table using Derived Column transformation. The column which source is the User::vendorName is always blank and the column which source is User::vendorId is always zero. Is is also possible that the value I used to initialized was not assigned to the variable at all?
Here are my settings
Sproc
CREATE PROC dbo.GetVendorId @name VARCHAR(100), @vendorId INT OUTPUT AS
SET NOCOUNT ON
SELECT @vendorId = vendorId FROM Vendor WHERE [name] = @name
This sproc works as intended in the Management Studio
SQL Task - General
ConnectionType = OLEDB
SQLSourceType = DirectInput
SQLStatement = EXEC GetVendorID ?, ? OUTPUT
BypassPrepare = False
SQL TASK - Parameter Mapping
Variable Nam Direction Data Type Parameter Name
User::vendorName Input VARCHAR 0
User::vendorId Output LONG 1
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett -
hmmm, cannot see anything wrong with it.
If you put a watch on the variable, can you see the value change after you have "apparently" assigned it?
Put a break point on a task, run the package, from the debug menu, add Watch Window 1, open variables pane, drag the variable into the watch and note it's value. step into task and see if the value changes.
Also, set up profiler and ensure the procedure is being called and write the value of your vendorID to a table to ensure that is working.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Crispin I did exactly what you instructed. The weird thing is, only the User::vendorId contains a value of -1 (my init value) when I peek in the Locals and Watch windows. For the User::vendorName it's just {} - blank. How exactly should I init the values for the variables for debugging purpose? I use the Variable pane on design time.
I'm sure my sproc is executed because at the end of the workflow task, I always get the expected number of rows based on the flat file. As indicated in my previous posts, I even derived two more columns for the variables so that I could map them to the target table in the OLEDBDestination object. Another quirk I noticed, SSIS changes the default -1 to 0.
The table always ends up with blank vendorName column and 0 for vendorId column of all the rows.
Very confusing to me.
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett -
You don't necessarily need to init variables although I do and set them to stupid values like "Crispin Rocks". Yes, you do this in the design time variables pane.
If your value is changing from -1 to 0, it is working fine. You are assigning a new value to the variable. That value looks to be 0.
Do you have any event handlers in the package that could be screwing with the values.
Go back to beginning: Create another package and have only a SQL task, two variables and a script task.
Use the script task after the SQL task to msgbox your variables. See if that works.
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Thanks for the reply Chris.
I made another package and still the same result. My question then is how shall I supply values to my variables during debugging?
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett -
Hi,
I am having a very similar problem.
In the variables collection, I created a variable called theDate of type String.
I set it to "1/2/2003"
In the the script setup properties, I set the ReadWriteVariables entry to theDate
In the task'S Main() I put the following...
-----------------------------------
MsgBox(Dts.Variables(
"theDate").Value)
Dts.Variables("theDate").Value = "Hi Mom!"
MsgBox(Dts.Variables(
"theDate").Value)
Dts.TaskResult = Dts.Results.Success
------------------------------------
You'd think the way everyone has told us to do it that the sequence of events would be:
- First MsgBox pops and displays: 1/2/2003
- Second MsgBox pops and displays: Hi Mom!
- Variables collection shows theDate now contain the value: Hi Mom!
All this happened except the 3rd item. The variables collection (package scope) is still at the initialized value of 1/2/2003.
What is irritating is I have found several posts of this behavior and nobody will address the issue. Why can't we change the package variable like everyone tells us will happen.
I am glad you guys are at least discussing this which is why I jumped in because it seems like a scope issue. It seems like the script is creating a local copy to work with and blowing off the package variable. Is this a side effect of filling in the ReadWriteVariables entry? How can I force it to point at the package variable?
Gerald
-
Variable values changed at run time will not reflect in the variables pane. is that what you are seeing?
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
Hi Crispin,
That's exactly what I am seeing. I just run headfirst into a Misnomer and the variables pane is just names and initialization values (not really current values)!
I created a second script (to run right after the first) to pop the variable and it said "Hi Mom!" like it was supposed to and the variables pane still says "1/2/2003".
The power of a missing word is amazing.
MS might want to change that label in the future because it is definitely not the "value".
Thanks for pulling back the blinds so I could move on!
You da person!
Gerald
-
If you want to see the values, use a watch window. From debug menu, select watch. Drag and drop a variable into it.
SSIS has two environments: Run time and design time and never shall the two meet....
Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!
-
I experienced similar quirks with design time values. The values I set in the variable pane were not the ones used in the execution. They were always changed to default values somewhere else. The culprit is the package configuration file which I configured but forgot about. Check if you have configured one. You can toggle it off by rightclicking the workspace of your package and select configuration.
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett -
Variables are more complex than they appear. The design-time value is saved in the dtsx file that defines the package. No run-time action will rewrite the package file to change this initial value. The value saved in the package can be overridden multiple times by configuration files, and then again by command line switches, before the package runs.
The issue of not seeing the variable value change within a single script task is because they are not simple variables, they're more like database records protected by locking and transaction semantics. While one script task has a "opened" a variable, it is locked to other tasks. When you set the value property and then immediatly re-read it, you see the original value because the change has not yet been committed. There are functions for locking and releasing variables within the task if you want to get creative.
It would be nice if the variables pane showed the current runtime value during debugging, but it doesn't. Remember to use the Locals and Watch windows.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply