Execute a SSIS Package with xp_cmdshell

Question

Execute a SSIS Package with xp_cmdshell

lucazav

SSC Eights!

Points: 981
More actions
October 8, 2008 at 3:31 am

#126026

I have a job that runs a SSIS package using this command line:
"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /DTS "\MSDB\StratificaGiacenzaDaCSV" /SERVER "DV-ERP" /SET \package.variables[strRollUpDate].Value;"2008/09/28 23:00" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
When I launch it manually, it works fine.
Now I create a Stored Procedure with this line:
EXEC master..xp_cmdshell '"C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /DTS "\MSDB\StratificaGiacenzaDaCSV" /SERVER "DV-ERP" /SET \package.variables[strRollUpDate].Value;"2008/09/28 23:00" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V'
When I launch that stored procedure I get this error:
'C:\Program' is not recognized as an internal or external command,
operable program or batch file.
NULL
I know it's a quoting problem, but I can't resolve the issue...
Any help?
Thanks!

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

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

lucazav SSC Eights! Points: 981 More actions · Answer 1

I solved the issue adding a " at the beginning of the string:

EXEC master..xp_cmdshell '""C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\DTExec.exe" /DTS "\MSDB\StratificaGiacenzaDaCSV" /SERVER "DV-ERP" /SET \package.variables[strRollUpDate].Value;"2008/09/30 23:00" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V'

and this works great in a query window of the Management Studio.

Now the problem is that if I store the string in a VARCHAR variable (@cmd) in a stored procedure, and then I try to launch this command (even in the stored procedure):

EXEC master..xp_cmdshell @cmd

I get this error:

Microsoft (R) SQL Server Execute Package Utility
Version 9.00.3042.00 for 32-bit
Copyright (C) Microsoft Corp 1984-2005. All rights reserved.
NULL
Argument ""\package.variables[strRollUpDate].Value;"" for option "set" is not valid.
NULL

even if the string stored in @cmd is the same of the "manual" string of the query window...

I can't understand why I get the error...

Any suggestion?

Thanks

lucazav SSC Eights! Points: 981 More actions · Answer 2

I got the solution empirically... The package goes on with this string stored in the @cmd variable:

C:\Progra~2\Micros~2\90\DTS\Binn\DTExec.exe /DTS \MSDB\StratificaGiacenzaDaCSV /SERVER DV-ERP /SET \package.variables[strRollUpDate].Value"; 2008/10/01 23:00" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V

I really can't understand why the double quotes work in this way...

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 3

lucazav (10/10/2008)
I got the solution empirically... The package goes on with this string stored in the @cmd variable:
C:\Progra~2\Micros~2\90\DTS\Binn\DTExec.exe /DTS \MSDB\StratificaGiacenzaDaCSV /SERVER DV-ERP /SET \package.variables[strRollUpDate].Value"; 2008/10/01 23:00" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
I really can't understand why the double quotes work in this way...

Hi,

It works ...

EXEC xp_cmdshell 'C:\"Program Files"\"Microsoft SQL Server"\100\DTS\Binn\DTExec.exe .............'

Elliott Whitlow SSC Guru Points: 102402 More actions · Answer 4

sudhakara (11/10/2010)
lucazav (10/10/2008)
I got the solution empirically... The package goes on with this string stored in the @cmd variable:
C:\Progra~2\Micros~2\90\DTS\Binn\DTExec.exe /DTS \MSDB\StratificaGiacenzaDaCSV /SERVER DV-ERP /SET \package.variables[strRollUpDate].Value"; 2008/10/01 23:00" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V
I really can't understand why the double quotes work in this way...
Hi,
It works ...
EXEC xp_cmdshell 'C:\"Program Files"\"Microsoft SQL Server"\100\DTS\Binn\DTExec.exe .............'

I think you have too many quotes..

EXEC xp_cmdshell '"C:\Program Files\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" .............'

should handle it nicely..

CEWII

jorge_gomes98 SSC-Addicted Points: 486 More actions · Answer 5

hello,

can you tell me yf this works when you put this code into ASP page and run from remote location?

This is exactly what i need to run a SSIS job from and ASP page...aSSIS job that creates tables from and external excel file...

thank you.

Jeff Moden SSC Guru Points: 1004415 More actions · Answer 6

jorge_gomes98 (10/24/2015)
hello,
can you tell me yf this works when you put this code into ASP page and run from remote location?
This is exactly what i need to run a SSIS job from and ASP page...aSSIS job that creates tables from and external excel file...
thank you.

The Double Quotes are necessary because there are spaces in the path to the EXE.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)