Running a DTS package from a Stored Procedure (sort of)

by Jason Haley 14. March 2004 06:26

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.

Comments (16) | Post RSS RSS comment feed |

Categories:
Tags:

Altering stored proceduresDo you ever edit a stored procedure in Enterprise manager, then forget which stored procedure it was...Having problems getting sample code running with a Sql Server Express mdf file?Having problems getting sample code running with a Sql Server Express mdf file? Do you run as non-ad...New Web Items' sort ordersHere is my current sort order for the new web items (different from last weekend's New Items): ...

Comments

Sreenath

June 8. 2004 06:24

HOw can assign the value of a global variable of the package? any thoughts?

Jason Haley

June 8. 2004 20:23

Sreenath: Some of my co-workers suggest doing this in the DTS package using a table as the place to hold the value you want to set, then grab that value out of the table in the DTS package and set the variable. I have never tried it, but it sounds do-able.

Dave C

June 10. 2004 09:17

Jason,

Do you know how to run a DTS package from VB.NET?

Jason Haley

June 10. 2004 19:37

Dave: Check out these links: http://support.microsoft.com/?kbid=321525" rel="nofollow">http://support.microsoft.com/?kbid=321525">http://support.microsoft.com/?kbid=321525" rel="nofollow">http://support.microsoft.com/?kbid=321525
http://15seconds.com/issue/030909.htm" rel="nofollow">http://15seconds.com/issue/030909.htm">http://15seconds.com/issue/030909.htm" rel="nofollow">http://15seconds.com/issue/030909.htm

Or find an example of using VB6, it is almost the same with .Net. You will have to use the COM library since there currently isn't a managed library for dts.

PRAVEEN

December 15. 2004 13:03

THANKS

Leonid

April 15. 2005 03:16

Hi,

If job already running, how to check it in the stored procedure and restart job , let say in 5 sec.
Thank you,
Leonid

Melinda Savoy

April 27. 2005 00:24

Jason,

I am getting the following error when trying to run the stored procedure via my ASP page:

Microsoft OLE DB Provider for SQL Server error '80040e14'

The specified @job_name ('Run_RSVPDTSPackage') does not exist.

/rsvpthrevents/rsvpadministration.asp, line 210

However, when I look at the job in my DTS section of my database it is there. Any suggestions?

Thanks.

Melinda Savoy

April 27. 2005 04:02

Jason, I just found that the user that you're running as on the web app MUST HAVE sa permissions in order to run the job (CMDEXE) that runs the DTS package. Is that correct?

Thanks.

Mike Larkin

May 2. 2005 05:46

You can pass any global variable values by using the /A flag in the Job Step.

Ex:

@command = 'DTSRun /SDevelopment /NTest Package /E /AMyGlobal Variable=123'

Rama mohan

September 26. 2006 22:34

NAA MODDA LAGA VUNDHI

m Waas

January 30. 2007 06:14

The person who runs the stored procedure: Does he have to have the sysadmin role ?

Tim

June 18. 2007 19:43

This was very helpfull and solved my issue!! thanks

mike

July 8. 2007 10:57

And how to wait until the job finishes?

Aargau3

February 6. 2008 00:03

Cheers!!

Shourya Mehra

October 9. 2008 22:13

-- Created by Shourya Mehra
Create procedure fire_dts
as

declare @pkg_obj int
declare @pkg_name varchar(128)
declare @temp int
declare @server_name varchar(128)
declare @db_name varchar(128)
declare @file_name varchar(128)

select @server_name = @@servername ,
@db_name = db_name() ,
@pkg_name = 'xshr_temp' ,
@file_name = 'c:\\InpFile\TestFile.txt'

exec sp_OACreate 'DTS.Package', @pkg_obj output
exec @temp = sp_OAMethod @pkg_obj, 'LoadFromSQLServer' , null,
@server_name = @server_name, @Flags = 256, @pkg_name = @pkg_name
exec @temp = sp_OASetProperty @pkg_obj,
'GlobalVariables("ServerName").value', @server_name
exec @temp = sp_OASetProperty @pkg_obj,
'GlobalVariables("DatabaseName").value', @db_name
exec @temp = sp_OASetProperty @pkg_obj,
'GlobalVariables("FileName").value', @file_name
exec @temp = sp_OAMethod @pkg_obj, 'Execute'
exec @temp = sp_OADestroy @pkg_obj

GO

Shourya Mehra

October 9. 2008 22:15

In the above example "xshr_temp" is assumed to be th name of the DTS package
cheers!!!

Comments are closed

Jason Haley

Ramblings of a .Net developer
.ver 3:0:0:0

Running a DTS package from a Stored Procedure (sort of)

Related posts

Comments

TextBox

Jason Haley

Ramblings of a .Net developer .ver 3:0:0:0

Running a DTS package from a Stored Procedure (sort of)

Related posts

Comments

TextBox

Tag cloud

Category list

Ramblings of a .Net developer
.ver 3:0:0:0