October 11, 2009 at 5:28 am
Using Sql Svr 2005 Integration Services I am attempting to output the contents of a table to an Excel file with the filename determined by variables, one of which stores the current date.
When I run the package it fails stating that the variable(s) is not found. I have tried options such as readonly, but have been unsuccessful.
After around 8 hours just trying to view the contents of the variables at runtime I am running out of patience and options. Any help is greatly appreciated.
Thanks. Ivan
October 11, 2009 at 7:35 am
Where is the variable being used?
From what you said I'm going to assume your using a script task. Do you have the variable specified in the script task under the read only or read-write variables? Make sure you just use the name don't put @ in front of the name and separate vars with commas. When you reference it (For a scipt task) you should be using dts.variables("VarName").value With a script component I use Me.readonlyvariables.varname
Also you need to make sure the scope of the variable is correct. If you use it in multiple locations then it must be specified at a level high enough to cover all those locations. Example if the scope is the name of the package then it's global and available everywhere. If the scope is the name of a script component then it's available only to that script component. Scope can't be changed you need to delete and re-create. Also when you create a variable whatever component you are in or whichever has focus will be the scope of the var. To make it global click on an empty area of the package control flow before creating the var.
To see the variable value you can setup a watch, check the debug file menu. Another option is to create a script component, add the variable you want to check to the read only variables(make sure it's in scope) then add this single line of code to script component msgbox(dts.variables("VarName").value.tostring). You can put this script component anywhere in the control flow. When you run interactive in BIDS you will get a message box displaying the value of the variable.
hth,
Tom
October 12, 2009 at 12:07 am
Hi Tom,
Thanks for the swift response.
I have uploaded the image Package.bmp and will try to be more specific. The variable VAR_TEST is scoped at the level of the SCRIPT task and is set to hold the value "Ivan_Test" and is included in the task's read only variables (shows as @[User::VAR_TEST] selected from the Expression Builder within the Script Task Editor window , Expresssions section.
The task fails with the error message:
Error: Failed to lock variable "Ivan_Test" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.".
The error is odd in that the value stored in the variable is returned and not the variable name, meaning that SSIS does see the variable and its value. I have not been able to add a watch as the option is not available in the debug menu. I have looked in various places and cannot add a watch. Could this be due to a patch missing? I can see the watch tabs when I run the package, but cannot add a variable. When I stop debugging I cannot see the watch window and cannot add a watch. I have been working on this from home and will not be able to access my emails. Would you mind emailing me at work: ivan.dale@uhns.nhs.uk . Otherwise, I should manage within SQL Server Central. Thanks again for your time and help.
October 12, 2009 at 8:06 am
Remove what you've entered in the Expressions section.
Click on Script, then enter your variable name VAR_TEST, without the @, in either the ReadOnlyVariables or ReadWriteVariables property.
October 12, 2009 at 9:30 am
Great, that works fine. I will now build that knowledge into my main package. Fingers crossed.
Thank you very much, your help is really appreciated Erik (and Tom).
Hopefully I can get my head around SSIS for my new job in November.
October 12, 2009 at 4:40 pm
Maybe this was just a typo but wouldn't you enter the variable name Ivan_Test in the readonly or readwrite variables and not VAR_TEST?
I just wanted to clarify.
The attached screenshot is how I reference variables in my script tasks.
October 13, 2009 at 1:16 am
I have found variables in scripts to be case sensitive too so ensure you use exactly the same each time.
October 13, 2009 at 7:22 am
In his example, VAR_TEST was the variable name, Ivan_Test was it's value.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply