Run SSIS Package from T-SQL

Question

I noticed you can use the following stored procedures (in order) to schedule a SSIS package:

msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'
msdb.dbo.sp_add_job ...
msdb.dbo.sp_add_jobstep ...
msdb.dbo.sp_update_job ...
msdb.dbo.sp_add_jobschedule ...
msdb.dbo.sp_add_jobserver ...

(You can see an example by right clicking a scheduled job and selecting "Script Job as-> Create To".)

AND you can use sp_start_job to execute the job immediately, effectively running SSIS packages on demand.

Question: does anyone know of any msdb.dbo.[...] stored procedures that simply allow you to run SSIS packages on the fly without using sp_cmdshell directly, or some easier approach?

Answer 1

Not really... you could try sp_OACreate but it's more complicated and may not do it.

Do you need to run them from SQL? They can be run from command line, .net app etc

Answer 2

Well, you don't strictly need the sp_add_category, sp_update_job or sp_add_jobschedule calls. We do an on-demand package execution in our app using SQL Agent with the following call sequence:

 - sp_add_job 
 - sp_add_jobstep
 - sp_add_jobserver 
 - sp_start_job

Getting the job status is a little tricky if you can't access the msdb..sysjobXXX tables, but our jobs start & run just fine.

EDIT:: Other than xp_cmdshell, I'm not aware of another way to launch the the SSIS handlers from withinSQL Server. Anyone with permissions on the server can start the dtexec or dtutil executables; then you can use batch files, job scheduler etc.