Stored Procedure to Initiate SSIS Package?? 1

[sconti11]

I have just been asked by my business if we can initiate my SSIS package ON DEMAND. Basically be able to update the records (which is done within the ETL of the SSIS package) whenever a user wants to.

Without getting into the logistics of this, I am trying to figure out how to create a simple Stored Procedure that will initiate the SSIS package. It currently is on a schedule within the SQL Server Agent. I have read that I could use sp_start_job, but I am not sure how to construct this properly??

Any assistance on writing the Stored Procedure would be helpful. Also there are no parameters being passed at this time to the SSIS package.

 

[RiverGuy]

EXEC sp_start_job 'The name of your SQL agent job'

 

[sconti11]

So that is all I have to write within the Stored Procedure Code?

Seems too simple....but again I am new to writing these.

 

[sconti11]

I figured it out! the sp_start_job IS the stored procedure!!!

Awesome....thanks alot!

 

[RiverGuy]

You'll have to make sure that the user has the proper permissions as well.

 

[sconti11]

RiverGuy,

I am glad you brought that permissions up, because it is now an issue. We are trying to initiate the sp_start_job procedure from Lotus Notes using Lotus Script, and we are getting the permissions error.

Not sure exactly how to get that user the appropriate permissions, because when I sign into SQL Mamnagement Studio using his credentials I am able to execute that stored procedure. Would you mind assisting me on this permission piece?

 

[RiverGuy]

If a non-sysadmin owns a job, he or she can start their own job.

A sysadmin can start any job.

Then there are SQLAgent roles in msdb

http://msdn.microsoft.com/en-us/library/ms188283.aspx