How do I remotely trigger a DTS package?

[jpittawa]

I have a DTS package that imports data for reporting. I would like to allow the user(s) to trigger the package from their desk without giving them Enterprise Manager.

The nature of their work does not lend itself to scheduling the package.

The server is SQL Server 7 on Windows 2000 Server.

What are my options?

 

[philhege]

jptttawa,

You have a number of options. You can save the DTS package to a .BAS file, and then include it in a VB project and compile it to an executable, which the user can invoke.

I use the following query statement (here, in ColdFusion) to create a self-destructing job that could possibly call a DTS package (this example runs an SP):

exec msdb.dbo.sp_add_job @job_name = 'myjob',
@enabled = 1,
@description = 'Do stuff for #ucase(l_user)#',
@delete_level = 3,
@notify_level_eventlog = 2,
@owner_login_name = 'login_name'
exec msdb.dbo.sp_add_jobstep @job_name = 'job_#ucase(l_user)#',
@step_name = 'dostuff',
@command = 'exec gensp_doStuff ''#ucase(l_user)#'', ''#dateformat(now(),"yyyy-mm-dd&quot#''',
@database_name = 'mydb',
@database_user_name = 'user_name'
exec msdb.dbo.sp_add_jobserver @job_name = 'job_#ucase(l_user)#'
exec msdb.dbo.sp_start_job @job_name = 'job_#ucase(l_user)#'

The @command step would have to be the GUID of the DTS package, which you can retrieve by scheduling the DTS from the interface, and reading the step details.

Wrap all of this in some sort of script or Web-based application, and you're good to go.

HTH,
PH

 

[tlbroadbent]

Check SQLDTS.com. The following link opens a page addressing the issue of executing a DTS package from various client programs, ASP, VB, Visual C#, a SQL trigger, etc.

http://www.sqldts.com/default.aspx?6,104,104,5,1

Terry L. Broadbent - DBA
SQL Server Page:

http://tlbroadbent.home.attbi.com/sql/sql_articles.htm

If you want to get the best answer for your question read faq183-874.