Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 890
  • Last Modified:

Calling SSIS from a Stored Procedure

I have a 32 bit SSIS package that I need to call from a stored procedure in MS SQL. I need to call the 32 bit version of DTExec.exe. But I am having pathing issues using the xp_cmdshell. I have also tried it without the double quotes.

The error I am getting with this code is: 'C:\Program' is not recognized as an internal or external command,

-- code --
DECLARE @ReturnCode int
DECLARE @SqlQuery nvarchar(2000)

set @SqlQuery = '"C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /SQL "pkgAbraTransferSpreadsheet" '
print '*' + @sqlQuery + '*'
EXEC @ReturnCode = master..xp_cmdshell @SqlQuery
 
print 'return code: ' + convert(nvarchar(15),@returncode)
0
Paula-at-APEX
Asked:
Paula-at-APEX
1 Solution
 
ProjectChampionCommented:
I'd suggest instead of using XP_CMDShell which entails unnecessary security risks and may as well be disabled on your production servers, create a SQL Server job that runs the pertinent SSIS package, setup all the necessary parameters and other settings (including the option to run the package in 32 bit mode) and the call sp_start_job inside your proc to start the job.

Also I appreciate that you may have a special but valid scenario to have to run an SSIS package from within a proc, but for multiple reasons it's not usually best practice for instance, since the package will run outside the stored proc context, there's no easy/reliable way to ensure it's done its job successfully or as that matters even if it's finished or still running. So it's usually the other way round, i.e. you call the procs from within a package.

Good luck anyways.  : )
0
 
Brendt HessSenior DBACommented:
One note from xp_cmdshell:
command_string cannot contain more than one set of double quotation marks
Your command string contains two sets.

Now, having tried several ways to do this, I had problems as well.  It turns out that there is a bug in the parser in xp_cmdshell if the command starts with a double quote.  You can work around this by adding a CD command to the command line, e.g.:

set @SqlQuery = 'cd.. && "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\Binn\DTExec.exe" /SQL pkgAbraTransferSpreadsheet'

Open in new window

1
 
Paula-at-APEXAuthor Commented:
WOW. Would have never figured that out. It worked great. Thanks.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now