?
Solved

Calling SSIS from a Stored Procedure

Posted on 2014-04-24
3
Medium Priority
?
829 Views
Last Modified: 2016-02-10
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
Comment
Question by:Paula-at-APEX
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 8

Expert Comment

by:ProjectChampion
ID: 40021179
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
 
LVL 32

Accepted Solution

by:
Brendt Hess earned 2000 total points
ID: 40021248
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
 

Author Closing Comment

by:Paula-at-APEX
ID: 40021326
WOW. Would have never figured that out. It worked great. Thanks.
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how the fundamental information of how to create a table.

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question