CODEWITHSUNDEEP
t-sqlssis
Zachary Scott
Zachary Scott

Reputation: 21198

Run SSIS Package from T-SQL

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?

Upvotes: 1

Views: 3948

Answers (3)

e_i_pi
e_i_pi

Reputation: 4820

In SQL Server 2012+ it is possible to use the following functions (found in the SSISDB database, not the msdb database) to create SSIS execution jobs, prime their parameters, and start their execution:

[catalog].[create_execution]

[catalog].[set_execution_parameter_value]

[catalog].[start_execution]

Upvotes: 0

DaveE
DaveE

Reputation: 3627

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.

Upvotes: 3

gbn
gbn

Reputation: 432677

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

Upvotes: 2

Related Questions