Hi All,

If I have a stored procedure that is being run by a DTS which is scheduled
to run by SQLSERVERAGENT. From that stored procedure, can I retrieve the
job id, task id, step id of the DTS package?

Thanks.

You cannot get the value directly from within the stored procedure, in the
same way that a procedure would no nothing of the VB code module that called
it. You can however do this with global variables and a Dynamic Properties
Task.

To start with change the job step to something like this

Dtsun /S.... /A"JobID":"3":"[JOBID]"

The syntax is probably wrong, but the key point is you can use the [JOBID]
token functionality of a job step and the /A command for DTSRun to set a
global variable to the SQLAgent Job Id.

You now have the JobID in a global variable at run-time.

Next use a Dynamic Properties Task to assign the step and task Id's (not
sure what you mean, as the "real" identifier for a Dts step and task is the
name) to global variables.

To use the global variables just use the parameter functionality of the Exec
SQL Task to pass in the values.

EXEC myProc ? ? ?

The ? is a placeholder, to which you map the global variables in the UI.


--
Darren Green
http://www.sqldts.com



" David N" <dq.ninh (AT) netiq (DOT) com> wrote