SQL Server 2008: execute SSIS package with store procedure
Posted on 2011-10-11
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.