Variable not in script task
-
I have been working with SSIS for a short while but cannot work this out, please help!!
Package level variable I've entered it into the ReadWrite Variables in Script Editor (checked the spelling and case).
I cannot work out why the script doesn't see that the variable is there, I have ReadOnly variables working fine (not sure why dts.variables.count is 0 though) but I cannot write to the ReadWrite variable(s).
Code to write to variable is:
Dts.Variables("sSourceFolder").Value = drFolderLocations.Item("Folder").ToString
Error is "The element cannot be found in a collection. This error happens when you try to retrieve an element from a collection on a container during execution of the package and the element is not there.
(The datatable/row is populated)
-
Is this a script task (control flow) or component (data flow)? If in the data flow then you can only access variables in some methods, e.g. preexecute, postexecute. From memory this will be dependant on what type of script component it has been set up as, i.e. source, destination or transform.
Hope that helps
Rob.
-
robertm (12/10/2008)
Is this a script task (control flow) or component (data flow)? If in the data flow then you can only access variables in some methods, e.g. preexecute, postexecute. From memory this will be dependant on what type of script component it has been set up as, i.e. source, destination or transform.Hope that helps
Rob.
Script task is in the Control Flow as I understand it I should be able to reference the ReadWrite variables as long as they are in the script editor? Or not!
-
In that case then these are the two most likely causes of your issue:
1. You've misspelled the variable name. Might sound silly but it's worth checking and then checking again!
2. Your variable is out of scope. For example, if your script component isn't in any other container in the control flow, e.g. sequence container etc then the scope of the variable you're calling needs to be at the package level. You can check this in the variables tab.
Rob.
-
robertm (12/10/2008)
In that case then these are the two most likely causes of your issue:1. You've misspelled the variable name. Might sound silly but it's worth checking and then checking again!
2. Your variable is out of scope. For example, if your script component isn't in any other container in the control flow, e.g. sequence container etc then the scope of the variable you're calling needs to be at the package level. You can check this in the variables tab.
Rob.
1. Copied and pasted variable name from variables window (package level btw) into ReadWrite Variables and into the code
2. script is in the control flow no other containers.
No change, still says it isn't there.
-
Just tried renaming the variable, which caused expressions to fail (proves it was recognised somewhere by ssis) renamed it in the script and ReadWrite variables - same error!!
The variable is assigned in a script task, then used in an expression later in the package.
-
Any resolution to this. I'm having the same problem. I have the script task in the control flow, made sure by copy/pasting the variable names were accurate, and even made sure via msgbox that it has valid values for the variables FROM and TO values.
For some reason, It won't change the values of my variables.
Dts.Variables("UploadFileNm").Value = sUploadFileNm
Dts.Variables("UploadFilePath").Value = sUploadFilePath
Perplexed.:unsure:
-
OK sorry for the delay in replying again... not sure what the issue could be without seeing the specifics of package. Have you tried to get a very simple package to work with just this functionality, e.g.
1. Create new package
2. Create variable = MyVariable, Scope = packagename, Data Type = String, Value =
3. Add Script Task to control flow
4. Open task and add the string "MyVariable" to the ReadWriteVaiables property (excluding quotes)
5. Design script and add code: Dts.Variables("MyVariable").Value = "new value"
6. Save and close all windows
7. Add new script task and follow steps 3 to 6 again but this time add parameter name to ReadOnlyVaiables property and add code: MsgBox(Dts.Variables("MyVariable").Value)
8. Save package and run.
You should see the message box pop up with the text "new value" in it.
Let me know how you get on.
Rob
-
Can you post your SSIS packages to take a look?
---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/
-
Rob and Journeyman,
Thank you both for your post. You both had a piece of the solution. Turns out that with the msgbox, I could verify that the values were being captured and changed. Meaning that it was reading the name of the variables from the last execution, it was finding the name of the file to change the variable to and it was actually changing the the value . All this without actually setting the variable.
When I went back and added the statement to set the variable (uncommented the statements provided in my first post), it "barfed" and failed the package. The problem was that I had delayed validation set to false. It was trying to connect to a variable datasource name that didn't exist yet.
Moral to the story...check your package properties and make sure they support the variable nature of your connection string. (DUH moment)
Thanks again,
Sabrina
-
I have worked around it by creating a new script task and assigning the variables in that (copy and pasting the code form the script task that won't work) - this works fine.
Thanks for the help everyone.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply