Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 SSIS and Import/Export (2008)
 Execute Process Task adding an extra quote

Author  Topic 

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-08 : 13:12:58
I have an Execute Process task in my package. It is running a mysql command I use a few variables to build the command.

1. [User::MySql] = \\crsddvcp01cru.d2-tdbfg.com\JOBS\SANScreen\mysql.exe
2. [User::SanScreen] = "C:\Users\Redirection\brittg2\Documents\LINQPad Queries\SanScreen_CapacityCurrentFact.sql"

I put it together with two expressions:

1. Executable = @[User::MySql]
2. Arguments = "-pxxx -uxxx --host=myhost.mydomain.com <" + @[User::SanScreen] + " -B >" + @[User::OutPut] + " 2> C:\\Users\\brittg2\\Temp\\out.err"

However, the execute process task fails. Upon closer inspection, I see that SSIS has added an extra double quote, which is in fact the problem. SSIS constructs and tries to execute this:

quote:

[Execute Process Task] Error: In Executing "\\crsddvcp01cru.d2-tdbfg.com\JOBS\SANScreen\mysql.exe" "-pxxx -uxxx --host=myhost.mydomain.com <"C:\Users\Redirection\brittg2\Documents\LINQPad Queries\SanScreen_CapacityCurrentFact.sql" -B >C:\Users\brittg2\Temp\temp.txt 2> C:\Users\brittg2\Temp\out.err" at "C:\Users\brittg2\Temp", The process exit code was "1" while the expected was "0".



which fails due to the extra quote just before -pxxx. If I remove the extra quote, I can run the command correctly without error.

So...

1. What is the cause of the extra double quote?
2. How can I get around the problem?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-08 : 13:33:41
Try using single quotes for Arguments.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-08 : 13:44:59
tara, that's not correct for SSIS. All strings must be double-quoted. The syntax is similar the syntax that the C and C# languages use. See

http://msdn.microsoft.com/en-ca/library/ms137547.aspx
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-08 : 13:51:21
It sounds like a bug to me. You could try to workaround it by putting the string into a variable and then removing the double quote before executing the process.

If it is a bug, I would check the build of SSIS and SQL Server. Get to the latest cumulative update package since those contain bug fixes too. Also update the client.

If getting to the latest build doesn't resolve it and you can't workaround the issue, you'll need to submit a case with Microsoft. If they confirm it is a bug, that case is no cost to you, well as long as you are on a supported version.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-08 : 14:41:53
Yeah I thought of that. Unfortunately the extra double-quote is added during the final assembly of the command passed to process start up. I set a breakpoint at the start of the EP task and everything looks good, including a variable I created that mimics the assembly by ssis. But, when SSIS runs the command, the extra quote appears.
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-08 : 15:27:28
FYi -- just updated to 2008 R2 SP3 -- same problem
Go to Top of Page

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-10-09 : 09:17:39
OK -- I found the problem. SSIS encloses the arguments -- where 1, 2, 5 or more -- in double-quotes. That is, it treats the arguments as a single string. Unfortunately that makes it pretty useless for calling programs like PowerShell or Robocopy (and a host of others!) that take arguments.

There is a workaround, however:

Set the executable to c:\windows\system32\cmd.exe and the arguments to /c followed by the command you want to run and the arguments. So in my case, I set the arguments to:

"/c " + @[User::Cmd] + " " + @[User::Opts]

and it works!

Pity that we have to workaround SSIS behavior in this.
Go to Top of Page
   

- Advertisement -