Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Variable not in script task


Variable not in script task

Author
Message
DanAzz24
DanAzz24
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 625
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)
robertm
robertm
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 118
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.



DanAzz24
DanAzz24
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 625
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!
robertm
robertm
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 118
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.



DanAzz24
DanAzz24
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 625
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.
DanAzz24
DanAzz24
SSC-Enthusiastic
SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)SSC-Enthusiastic (185 reputation)

Group: General Forum Members
Points: 185 Visits: 625
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.
ssexton
ssexton
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 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.Unsure
robertm
robertm
SSC Veteran
SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)SSC Veteran (250 reputation)

Group: General Forum Members
Points: 250 Visits: 118
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



CozyRoc
CozyRoc
SSCrazy
SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)SSCrazy (2K reputation)

Group: General Forum Members
Points: 2024 Visits: 2235
Can you post your SSIS packages to take a look?

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


ssexton
ssexton
Valued Member
Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)Valued Member (54 reputation)

Group: General Forum Members
Points: 54 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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search