SQL Server 2008: execute SSIS package with store procedure

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.
Louis CapeceAsked:
Who is Participating?
 
TempDBAConnect With a Mentor Commented:
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))
INSERT INTO #temp
EXEC @ret = xp_cmdshell @command
DECLARE @ErrorMessage VARCHAR(2000)  
 IF ISNULL(@ret,-1)<>0
 BEGIN  
    SELECT @ErrorMessage = @ErrorMessage + error
    FROM #temp
    WHERE error IS NOT NULL
    RAISERROR (@ErrorMessage,12,1)
 END



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 = ''
0
 
mlmccCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
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.

All Courses

From novice to tech pro — start learning today.