SQL Server 2008: execute SSIS package with store procedure

Posted on 2011-10-11
Medium Priority
Last Modified: 2012-05-12
Hello Experts,
Hopefully someone can advise if this is possible:

I have an SSRS web report that reports the data collection status of several ETL processes. I am planning on putting an image "button" on the report that will enable users to execute a SSIS package by clicking the button. These users will not have DTEXEC or any other client tools on their PC just a web browser.
Pressing the buttom will invoke an action that calls a dataset that is based on a stored procedure. The stored procedure would run dtexec on the server that has SSIS installed, using as specific, hard-coded user id/password. Where I need help is in the Sproc....

1. How can dtexec be run inside a stored procedure?
2. Right now, I have the DTSX stored on the servers file system....should it be moved to server based storage for this effort?
3. This particular ssis package is within it's own SQL agent job - is it possible, or easier to "run agent job" using the stored procedure?

Thanks for taking the time to check out my help request.
Question by:Louis Capece
LVL 25

Accepted Solution

TempDBA earned 2000 total points
ID: 36949217
1. Yes you can run the dtexec inside a stored procedure.

DECLARE @command VARCHAR(3000), @ret INT
SET @command = 'dtexec /FILE "'+ '<package name with full path>"  /CONFIGFILE "'+ <package config file with full path>"  /CHECKPOINTING OFF /REPORTING E'
CREATE TABLE #temp (error VARCHAR(500))
EXEC @ret = xp_cmdshell @command
DECLARE @ErrorMessage VARCHAR(2000)  
 IF ISNULL(@ret,-1)<>0
    SELECT @ErrorMessage = @ErrorMessage + error
    FROM #temp
    RAISERROR (@ErrorMessage,12,1)

2. Yes you can copy the config file and the package to a file location in the server.

3. To start the job, you need to run following system procedure
EXEC msdb.dbo.sp_start_job @job_name = ''
LVL 101

Expert Comment

ID: 37144333
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

829 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