Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Variable not in script task Expand / Collapse
Author
Message
Posted Wednesday, December 10, 2008 6:05 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:51 AM
Points: 141, Visits: 457
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)
Post #616910
Posted Wednesday, December 10, 2008 6:15 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 3:04 AM
Points: 210, Visits: 115
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.



Post #616920
Posted Wednesday, December 10, 2008 6:58 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:51 AM
Points: 141, Visits: 457
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!
Post #616962
Posted Wednesday, December 10, 2008 7:12 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 3:04 AM
Points: 210, Visits: 115
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.



Post #616985
Posted Wednesday, December 10, 2008 7:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:51 AM
Points: 141, Visits: 457
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.
Post #617021
Posted Wednesday, December 10, 2008 7:49 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 3:51 AM
Points: 141, Visits: 457
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.
Post #617040
Posted Wednesday, December 10, 2008 12:14 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 01, 2009 9:48 AM
Points: 26, Visits: 90
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.
Post #617303
Posted Thursday, December 11, 2008 7:39 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, December 11, 2013 3:04 AM
Points: 210, Visits: 115
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





Post #617905
Posted Thursday, December 11, 2008 8:21 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 5:52 AM
Points: 1,114, Visits: 2,191
Can you post your SSIS packages to take a look?

---
SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Post #617955
Posted Thursday, December 11, 2008 9:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, September 01, 2009 9:48 AM
Points: 26, Visits: 90
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
Post #618045
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse