Running\Triggering SSIS package from Stored Procedure

Question

Running\Triggering SSIS package from Stored Procedure

justpiyushmittal-1127780

Old Hand

Points: 379
More actions
August 26, 2009 at 3:15 am

#385248

Hi,
Can anyone tell me how to run\trigger a SSIS package from a stored procedure?
Kindly note that Xp_cmdshell is disabled so I am looking for an alternative approach for DTEXEC.
Any inputs will be helpful.
Thanks In Advance.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 1

One option is to create a SQL ServeAgent job that runs the package. You can then start the job from the stored procedure.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 2

Please do not send private messages asking for more help. If you have any questions related to what is posted in a thread then ask the question in the thread itself.

As far as your question is concerned, please show that you're will to do a bit of work yourself. You'll find that people are more likely to help you if you show that you tried to help yourself first.

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]

justpiyushmittal-1127780 Old Hand Points: 379 More actions · Answer 3

Thanks Alvin for your advice for a newbie.

here are the couple of methods I have implemented along with the issues I am facing:

-Created a Sql Agent job and scheduled it to run time to time to check if there is a requirement to run the package. The problem here is it is unnecessary running the package when it is not required and throwing an failure alert, which is hampering the performance of server.

-Secondly, I wrote a XP_cmdshell statement to run the package when it is required from the stored procedure using the DTEXEC utility. But we did not want to enable the cmdshell on server due to security implications.

So I am looking for some alternative to my second approach, which does not require cmdshell to run the package and can trigger the package from stored procedure when only it is required.

Please let me know if this helps and if you can provide some directions.

Thanks.

Alvin Ramard SSC-Forever Points: 41190 More actions · Answer 4

look at sp_start_job

Alvin Ramard
Memphis PASS Chapter[/url]

All my SSC forum answers come with a money back guarantee. If you didn't like the answer then I'll gladly refund what you paid for it.

For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]