Ever need to run a DTS package from a stored procedure? One of my current projects needs this. I got lucky in the sense that we have another application at work that
does exactly this. What this application does is the following:
- Starts a SQL Server Job
- This job runs the DTS package
Pretty simple, but there are a few moving parts here. An operator, an SQL job, a job step, and a DTS package. The operator is so the job can send and email or net send
message to someone, the other items should be pretty obvious.
First create a new operator. This (via T-SQL) requires the use of sp_add_operator, here is the syntax from books online:
sp_add_operator [ @name = ] 'name'
[ , [ @enabled = ] enabled ]
[ , [ @email_address = ] 'email_address' ]
[ , [ @pager_address = ] 'pager_address' ]
[ , [ @weekday_pager_start_time = ] weekday_pager_start_time ]
[ , [ @weekday_pager_end_time = ] weekday_pager_end_time ]
[ , [ @saturday_pager_start_time = ] saturday_pager_start_time ]
[ , [ @saturday_pager_end_time = ] saturday_pager_end_time ]
[ , [ @sunday_pager_start_time = ] sunday_pager_start_time ]
[ , [ @sunday_pager_end_time = ] sunday_pager_end_time ]
[ , [ @pager_days = ] pager_days ]
[ , [ @netsend_address = ] 'netsend_address' ]
[ , [ @category_name = ] 'category' ]
Second create a job. This requires the use of sp_add_job. Here is the syntax for sp_add_job:
sp_add_job [ @job_name = ] 'job_name'
[ , [ @enabled = ] enabled ]
[ , [ @description = ] 'description' ]
[ , [ @start_step_id = ] step_id ]
[ , [ @category_name = ] 'category' ]
[ , [ @category_id = ] category_id ]
[ , [ @owner_login_name = ] 'login' ]
[ , [ @notify_level_eventlog = ] eventlog_level ]
[ , [ @notify_level_email = ] email_level ]
[ , [ @notify_level_netsend = ] netsend_level ]
[ , [ @notify_level_page = ] page_level ]
[ , [ @notify_email_operator_name = ] 'email_name' ]
[ , [ @notify_netsend_operator_name = ] 'netsend_name' ]
[ , [ @notify_page_operator_name = ] 'page_name' ]
[ , [ @delete_level = ] delete_level ]
[ , [ @job_id = ] job_id OUTPUT ]
Third add jobs to that new job. This is done with the sp_add_jobstep system stored procedure. Here is the syntax for sp_add_jobstep:
sp_add_jobstep [ @job_id = ] job_id | [ @job_name = ] 'job_name'
[ , [ @step_id = ] step_id ]
{ , [ @step_name = ] 'step_name' }
[ , [ @subsystem = ] 'subsystem' ]
[ , [ @command = ] 'command' ]
[ , [ @additional_parameters = ] 'parameters' ]
[ , [ @cmdexec_success_code = ] code ]
[ , [ @on_success_action = ] success_action ]
[ , [ @on_success_step_id = ] success_step_id ]
[ , [ @on_fail_action = ] fail_action ]
[ , [ @on_fail_step_id = ] fail_step_id ]
[ , [ @server = ] 'server' ]
[ , [ @database_name = ] 'database' ]
[ , [ @database_user_name = ] 'user' ]
[ , [ @retry_attempts = ] retry_attempts ]
[ , [ @retry_interval = ] retry_interval ]
[ , [ @os_run_priority = ] run_priority ]
[ , [ @output_file_name = ] 'file_name' ]
[ , [ @flags = ] flags ]
And last, start the job. This step would be done in the stored procedure you want to run the DTS package. This is done with the sp_start_job system stored procedure. Here is the syntax for sp_start_job:
sp_start_job [@job_name =] 'job_name' | [@job_id =] job_id
[,[@error_flag =] error_flag]
[,[@server_name =] 'server_name']
[,[@step_name =] 'step_name']
[,[@output_flag =] output_flag]
Here is the SQL I used for a little sample:
USE msdb
BEGIN
-- Create an operator
EXEC sp_add_operator @name='Jason Haley',
@enabled=1,
@netsend_address='127.0.0.1'
DECLARE @job_id_out UNIQUEIDENTIFIER
-- Create a job
EXEC sp_add_job @job_name = 'Run DTS Package',
@enabled = 1,
@description = 'This is a job used to run a DTS package',
@start_step_id = 1,
@notify_level_eventlog = 3,
@notify_level_netsend = 1,
@notify_netsend_operator_name = 'Jason Haley',
@job_id = @job_id_out OUTPUT
-- Add a step to the job just created
EXEC sp_add_jobstep @job_id = @job_id_out,
@step_id = 1,
@step_name = 'Start DTS Package',
@subsystem = 'CMDEXEC',
@command = 'DTSRun /SDevelopment /NTest Package /E'
EXEC sp_help_job @job_name = 'Run DTS Package'
END
GO
CREATE PROCEDURE dbo.RunDTSJob
(
@job_name varchar(50) = 'Run DTS Package'
)
AS
BEGIN
EXEC msdb.dbo.sp_start_job @job_name
END
Now I need to bulk it up a bit to get some status information out, I'll keep you posted.