How to Execute IS from

Question

How to Execute IS from

MalaSuerte

Old Hand

Points: 319
More actions
March 8, 2007 at 3:21 pm

#116245

I have a job that requires me to call and execute a SSIS package as the first step in a SQL2k5 Stored Procedure.
Can someone please give me a basic template or example or where I could find out how to do this?
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

James Moore (Red Gate) SSCrazy Points: 2644 More actions · Answer 1

Have a look here: http://msdn2.microsoft.com/en-us/library/ms141701.aspx

Basically you can set the type of the step to SQL Server Integration Services package execution which will do what you want.

- James

--
James Moore
Red Gate Software Ltd

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 2

If you have enabled xp_cmdshell, you could use it to execute dtexec to run the package.

If you set up an unscheduled Agent job with one step to run the package, you could use "EXEC sp_start_job" in a procedure to run it. You may have to go into a loop watching the job history in msdb to know when it finished.

You could create a CLR stored procedure that loads the package into a Microsoft.SqlServer.Dts.Runtime.Package object and executes it programmatically. Then call this procedure from the original procedure.

MalaSuerte Old Hand Points: 319 More actions · Answer 3

Thanks for all the suggestions and help 🙂

I do need to know when the job is finished before continuing execution of the SP but I've never set up an unscheduled SQL Agent job and then run it from a SP. I am less familiar with using a CLR SP and doing this programatically.

I'd choose using a SQL job because I'm most familiar with that. What do you 'watch' for in the msdb database? Will I get a return code when complete of failed or successful? In the loop (a While loop I'm assuming) what is the condition to check for?

Thanks.

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 4

One technique would be to add something at the end of the job that inserted a record in a table, updated a record, or otherwise created an artifact that the calling procedure could detect. You would have to be careful to make sure that no failure in the IS package could leave the calling procedure waiting forever.

But the simplest way would probably be to check the msdb.dbo.sysjobhistory table. You looking for a record with the correct job_id, run_date, run_time greater than when you called sp_start_job, and step_id = 0. The run_status field will tell you whether the job succeeded or not.

I'm assuming the package executes quickly, I wouldn't recommend this for something that runs for hours, but it could look like this:

exec sp_start_job

while 1=1 begin

waitfor delay 00:01:00 -- Or 00:05:00, or 00:00:30, whatever interval is appropriate

if exists(select null from msdb.dbo.sysjobhistory where ....)

break

end