set a variable in ssis to a new value

Question

set a variable in ssis to a new value

arkiboys

Hall of Fame

Points: 3958
More actions
December 12, 2011 at 10:17 am

#247512

I am unable to see why the following does not reset the variable MainPath inside the package. Do you see why please?
EXEC xp_cmdshell
'DTExec /f "D:\Users\packagefolder\packagename.dtsx"
/SET "\Package.Variables[User::ServerName].Value";"\\servername"
/SET "\Package.Variables[User::MainPath].Value";"\d$\ssis"
/SET "\Package.Variables[User::FileName].Value";"filename"'
Looking closely the error message is:
Error Description : Cannot open the datafile "\\servername\d$\foldername\file name.csv".
Note that it is looking at the wrong location. This is the default location inside the package variable. But I am trying to alter it to \d$\ssis\file name.csv

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

Do you have package configurations enabled on the variable MainPath?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

arkiboys Hall of Fame Points: 3958 More actions · Answer 2

I did but then I disabled it.

Still seems the package is looking at the folder path which is set in the package variable.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 3

arkiboys (12/13/2011)
I did but then I disabled it.
Still seems the package is looking at the folder path which is set in the package variable.

Take a copy of your package, delete all existing configurations and then try again. The SET switch should work, but package configurations have higher precedence.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

arkiboys Hall of Fame Points: 3958 More actions · Answer 4

I did what you suggested and difinitely my set statement does not work because:

1- I removed the value in the MainPath variable of the package and renamed the pckageconfiguration and disabled the package configuration from the package.

Running the exec code, the error is:

Description: Cannot open the datafile "\\servername\file name.csv".

NOTE, that there is no path in there whereas I entered it in my exec set statement.

So, the SET statement does not path parameter.

Do you see why please?

Thanks

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 5

arkiboys (12/13/2011)
I did what you suggested and difinitely my set statement does not work because:
1- I removed the value in the MainPath variable of the package and renamed the pckageconfiguration and disabled the package configuration from the package.
Running the exec code, the error is:
Description: Cannot open the datafile "\\servername\file name.csv".
NOTE, that there is no path in there whereas I entered it in my exec set statement.
So, the SET statement does not path parameter.
Do you see why please?
Thanks

So you removed the value of the variable, making it effictively blank?

And still you get the old value?

Did you deploy your new version of the package?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

arkiboys Hall of Fame Points: 3958 More actions · Answer 6

1- Disabled the package configuration

2- I remeoved the value of the MainPath variable which is inside the package i.e. blank value

3- Redeployed to sql server integration services

4- Ran the cmdshell exec...

5- Error is:

Error Description : Cannot open the datafile "\\servername\file name.csv".

NOTE that there is no path folder now. So definitely my SET statement is not working.

Do you see why please?

Thanks

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 7

What if you don't put double quotes around \Package.Variables[User::MainPath].Value?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

arkiboys Hall of Fame Points: 3958 More actions · Answer 8

No matter how I run it, the mainPath folder is NOT being set.

It is only using the value inside the package variable.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 9

According to MSDN, the SET option only doesn't work if values are set by a configuration.

http://msdn.microsoft.com/en-us/library/ms141682.aspx

So you are still doing something wrong somewhere.

Change the design time value for mainPath folder to Dummy. Do everything just the same way as you did before. Do you see Dummy popping up?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

arkiboys Hall of Fame Points: 3958 More actions · Answer 10

Fixed the problem.

It seems all the parameters have to be on one single line.

Thanks for your time.

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 11

arkiboys (12/13/2011)
Fixed the problem.
It seems all the parameters have to be on one single line.
Thanks for your time.

If that doesn't make you bang your head against the wall, what doesn't? 😀

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP