SQL Server 2008: execute SSIS package with store procedure

Posted on 2011-10-11
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

    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
        SELECT @ErrorMessage = @ErrorMessage + error
        FROM #temp
        WHERE error IS NOT NULL
        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 100

    Expert Comment

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

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Join & Write a Comment

    Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL ( several years ago, it seemed like now was a good time to updat…
    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

    754 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now