DTExec with variable

Question

DTExec with variable

HappyGeek

SSCoach

Points: 18726
More actions
December 5, 2016 at 4:04 am

#401164

Trying to pass a variable - password - to a dtsx package using dtexec command, but the password contains a semicolon and consequently the package fails as below
exec xp_cmdshell 'dtexec.exe /F "D:\DBUpdates\Mypackage.dtsx" /SET "\package.Variables[User::JobLoginPassword].Value";"8:;xLuZc"'
But getting an error saying the argument for SET is not valid, removing the semi colon means the package fails with invalid password, have tried wrapping the variable in double quotes, single quotes, two single quotes and even square brackets but with no success.
Is this indeed possible? Help here would be most welcome.
...

Viewing 6 posts - 1 through 5 (of 5 total)

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

Thom A SSC Guru Points: 99126 More actions · Answer 1

The escape character for SSIS Expressions is a backslash (\). So, a variable of value; abc"123; would be: ="abc\"123\;".

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

HappyGeek SSCoach Points: 18726 More actions · Answer 2

Thom A (12/5/2016)
The escape character for SSIS Expressions is a backslash (\). So, a variable of value; abc"123; would be: ="abc\"123\;".

Thom many thanks for the quick response, however if I remove all double quotes I still get the error, if I just remove the ; I get an invalid login error.

...

Thom A SSC Guru Points: 99126 More actions · Answer 3

Thats not what I was saying, you need to escape the special charaters:

exec xp_cmdshell 'dtexec.exe /F "D:\DBUpdates\Mypackage.dtsx" /SET "\package.Variables[User::JobLoginPassword].Value";"8\:\;xLuZc"'

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

HappyGeek SSCoach Points: 18726 More actions · Answer 4

Thom A (12/5/2016)
Thats not what I was saying, you need to escape the special charaters:
exec xp_cmdshell 'dtexec.exe /F "D:\DBUpdates\Mypackage.dtsx" /SET "\package.Variables[User::JobLoginPassword].Value";"8\:\;xLuZc"'

Many thanks for the clarification, but again after trying several dozen combinations with the \ before and after the special characters still getting the same error! Very surprised it did not work.

...

HappyGeek SSCoach Points: 18726 More actions · Answer 5

After a bit more searching found this link: http://www.sqlservercentral.com/Forums/Topic851583-148-1.aspx which turned out to be the answer in this instance too, should anyone have a similar issue.

...