Package execution with an ampersand in a password

  • I am executing a package in a stored proc using dynamic sql along with connection string as package variable and the password has a ampersand in it which is causing an error. Is there escape character that I can use for the ampersand.

    ERROR MESSAGE:

    The argument "\package.Variables[ConnString].Value;"Server=testserver;Database=testDATABASE;User ID=testUSER;Password=WrKe\ " has mismatched quotes.

    'WEVzz' is not recognized as an internal or external command,

    I am using something like this below.

    ----package name

    SET @packagename = 'testpackage'

    SET @ServerName = 'testserver'

    SET @params = '/SET \package.Variables[ConnString].Value;"\"Server= ' + @ServerName + ';Database=testDATABASE;User ID=testUSER;Password=WrKe$WEVzz;Trusted_Connection = False\""'

    SET @ssisstr = 'dtexec /sq ' + @packagename + ' /ser ' + @servername + ' '

    ----now making "dtexec" SQL from dynamic values

    SET @ssisstr = 'dtexec /sq ' + @packagename + ' /ser ' + @ServerName + ' '

    SET @ssisstr = @ssisstr + @params

    ----now execute dynamic SQL by using EXEC.

    DECLARE @returncode int

    EXEC @returncode = xp_cmdshell @ssisstr

    SELECT @returncode

  • I figure it out putting double quotes around the whole password.

Viewing 2 posts - 1 through 1 (of 1 total)

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