Note
Access to this page requires authorization. You can try signing in or changing directories.
Access to this page requires authorization. You can try changing directories.
Question
Wednesday, June 30, 2010 2:00 PM
Hi All,
I am trying to execute SSIS package from the .net Framework.
I have a User Variable available in my SSIS package to dynamically pass file name to the flat file source.
Package executes successfully If i ignore Variable passing part from my code. But If I set variable in my code and then execute the package, it gets failed.
I am getting the following error:
"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."
Below is my code:
Microsoft.SqlServer.Dts.Runtime.Application app=new Microsoft.SqlServer.Dts.Runtime.Application();
Package pkgIn = new Package();
string pkgLocation;
DTSExecResult pkgResults;
pkgLocation =@"c:\Package1.dtsx";
pkgIn.Variables["FileName"].Value = @"c:\filename.txt";
pkgIn = app.LoadPackage(pkgLocation, null);
pkgResults = pkgIn.Execute();
MessageBox.Show(pkgResults.ToString());
FYI : I have created User Variable in my package and it executes without any error if i run a package from the BIDS.
Thanks in advance!!!
All replies (14)
Wednesday, June 30, 2010 2:14 PM
Variable names are case sensitive in SSIS, and might also be scope sensitive as well when assigning a value via code.Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Wednesday, June 30, 2010 2:20 PM
I have checkd for the Variable name and scope. looks perfect! I dont know whts wrong?
Wednesday, June 30, 2010 2:40 PM
Are you 100% sure that is how to refer to a User variable? (I don't run packages via code so I don't know.)
Should it be: pkgIn.Variables["User::FileName"].Value
Just guessing here.
Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Wednesday, June 30, 2010 2:56 PM
I have tried with pkgIn.Variables["User::FileName"].Value, but no luck :(
I have solved the "variable can not be found error" but now my problem is that : variable value is not getting set in the package1.
that means whatever file name I give in a package, it will be executed but the value which I set in my code for the file name doesnt reflect in my package.
plz help me in this if u have any idea..
Wednesday, June 30, 2010 3:06 PM
Again, I don't invoke package from code, so take this for what it's worth:
The back slash character is reserved in SSIS Expressions, so when you are using it as a literal, you need to double it up. Example: "C:\MyFolder\" + @[User::FileName].
But in the Variables window, that is not required.
Could it be that you need to assign it a value of "C:\filename.txt"
Still just guessing, sorry.
BTW, how did you solve "Variable not found" error
Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Wednesday, June 30, 2010 3:33 PM
Thanks for your guess, It didnt work though :)
I appreciate your inputs..
Regarding variable not found error: i was assigning value to the variable before loading the package (silly me :)). So modified my code and it worked.
Wednesday, June 30, 2010 3:47 PM
OK. Now that you are past the one hurdle, can you tell us how you KNOW that the Variable value is not being honored in the package?
How is the variable being used? My guess that you have a Flat File Connection Manager that is THAT file? Do you have an Expression on the Connection Manager's Connection String property?
Maybe, as a test, try this: Create a package that has only one task: A Send Mail, and set the MessageSource property via an Expression to be your FileName variable. Maybe something like this:
"Hello, the variable value is: " & @[User::FileName]
Does your resulting email reflect the design-time value, or the passed value?
Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Wednesday, June 30, 2010 3:55 PM
I have a different file name assigned to a Variable as default value in a package. And the file name which I am passiing form my .net code is also different from the default file name.
So when I execute my code, only the default file is being executed. thats how I got to know that package is not using my passed variable value.
I have a expression assigned on the connection manager's connection string property.
I will try to work on your idea just to find out whether my passed value is getting sent to the package or not.
Thanks again!
Wednesday, June 30, 2010 4:22 PM
I have created a package with "Send Mail" task and passed my variable name. It worked well and I got the correct varaible name in my email when I have executed package from my .net code. This means variable name is getting sent from the .net code.
Now I dont understand why flat file source is not working for the same passed value?
I think we are very close to the solution.. plz provide me the next step
Wednesday, June 30, 2010 5:12 PM
Just to confirm, you have an Expression set on the Connection String property of your Flat File Connection Manager and this gives you the correct result when you click "Evaluate Expression".
Now when you try to pass in a new value to the Variable, it doen't take it.
Does it fail, or does it succeed with the Design Time value? This is important.
Is the file path really on the C:\ drive? or is it a mapped drive? does the serverhave the same drive mapping? Does the execution account have permissions on the drive and/or shared folder?
Any error messages?
Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Wednesday, June 30, 2010 5:22 PM
File path is on the C:\ and I dont get any error while executing package. It executes successfully with the design time value.
No error message...
Wednesday, June 30, 2010 5:27 PM
Sounds like the Expression on your Connection Manager is not right. How did you set it up? Tell me step-by-stepTodd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.
Wednesday, June 30, 2010 5:54 PM
I can see very strange behaviour of my package!!!!
I have found that package is taking new value which I am passing from the .net code and gives me correct output BUT when i change file name value in my package and pass it second or third time, It still gives me the older value. The value which I passed for the first time.
So I think file data has been stored in a package memory or something like that and thats why package is executing old value everytime and doesnt take new value.
Do you know what i am saying?
Wednesday, June 30, 2010 5:59 PM
Try calling your package with a BAT file utilizing DTEXEC and pass it various values for the variable. Confirm that the issue is SSIS or maybe your code?Todd C - MSCTS SQL Server 2005 - Please mark posts as answered where appropriate.