Hey all,

Is there a way to determine that a package is currently
executing? I have a job that executes a package every
four hours and would like to be able to check on
subsequent executions whether the package is already
executing, in order not to execute again.

Regards,
Kevin

The way I do it is

Build a control table.
At the start of the package a row is entered to say the package is starting.
At the end of the package I update the row to show the package has finished.


--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Kevin" <kmcmurphy (AT) kmcmurphyco (DOT) com> wrote

Hi Kevin,

Thanks for your post. According to your description, I understand that you
have a job that executes a package every four hours. You want to determine
if the package is executing. If I have misunderstood, please feel free to
let me know.

So far as I know, it seems there is not a built-in feature in SQL Server
can meet your requirements. If you only want to determine if the package is
executing, I think Allan's suggestions is good. However, I noticed that
your package is executed in a job. In that case, can you please try to
determine if the package is executing via checking the status of the job.
To check the status of a job, you can use stored procedure sp_help_job.

[@execution_status =] status

Is the execution status for the jobs. status is int, with a default of
NULL, and can be one of these values. @execution_status =1 will help you
get the jobs which are executing. For example,

Execute msdb..sp_help_job @execution_status = 1

For more information regarding sp_help_job, please refer to the article on
SQL Server Books Online.
Topic: "sp_help_job"

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Hi Allan and Michael

Thanks for the advice. I appreciate the options you've suggested

Allan, how do you handle cleanup of the table, in the case that the package ends unexpectedly

Unfortunately, Michael, the package can be executed outside of the job, and I'd like to make sure the it is protected in that case, as well

Tell me, if you don't mind, what you think of this idea

I'm planning to add an Execute SQL Task to each package that performs data manipulation. This task will execute sp_getapplock, creating an exclusive, session-level lock with the package name as the resource. The task will leave the connection open after executing, creating a lock for the entirety of the package run. This way, the lock will be released whether the package ends in error or reaches completion

What do you think

Regards
Kevi

----- \"Yuan Shao\" wrote: ----

Hi Kevin

Thanks for your post. According to your description, I understand that you
have a job that executes a package every four hours. You want to determine
if the package is executing. If I have misunderstood, please feel free to
let me know

So far as I know, it seems there is not a built-in feature in SQL Server
can meet your requirements. If you only want to determine if the package is
executing, I think Allan's suggestions is good. However, I noticed that
your package is executed in a job. In that case, can you please try to
determine if the package is executing via checking the status of the job.
To check the status of a job, you can use stored procedure sp_help_job

[@execution_status =] statu

Is the execution status for the jobs. status is int, with a default of
NULL, and can be one of these values. @execution_status =1 will help you
get the jobs which are executing. For example

Execute msdb..sp_help_job @execution_status =

For more information regarding sp_help_job, please refer to the article on
SQL Server Books Online
Topic: "sp_help_job

Thanks for using MSDN newsgroup

Regards

Michael Sha
Microsoft Online Partner Suppor
Get Secure! - www.microsoft.com/securit
This posting is provided "as is" with no warranties and confers no rights

Hi Kevin,

Thanks for your feedback. It seems a good way to use sp_getapplock to avoid
concurrent execution. I have not found any obvious problem and it seems
feasible. We just need to ensure that the sp_getapplock and destination
package are performed in the same transaction. Also, we can use xp_cmdshell
to run package using dtsrun utility. Please feel free to post in the group
if you would like further assistance when you carry it out.

For more information regarding this issue, please refer to the following
article on SQL Server Books Online.
Topic: "dtsrun Utility"
Topic: "xp_cmdshell"

Thanks for using MSDN newsgroup.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

How do I clean up.?

I have a job every n minutes that says

"Hey master table. Has that package been executing for longer than it
should (Do you have a start time no finish and the start time is Now() -
whatever)"
if the answer is yes I can mail myself and say

"I think something went wrong with package X which started at this time"

It can also insert an end time or I can do it myself.



--

----------------------------

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Kevin" <kwm2b (AT) iwon (DOT) com> wrote

session-level lock with the package name as the resource. The task will
leave the connection open after executing, creating a lock for the entirety
of the package run. This way, the lock will be released whether the package
ends in error or reaches completion.

Michael,

Thank you, again, for your advice.

You mentioned that I should ensure that the sp_getapplock execution occurs within the same transaction as the dts package. Therefore, you suggested I use xp_cmdshell to execute the package. However, won't the same effect be achieved by executing sp_getapplock with LockOwner of 'Session' and keeping the Session open for the duration of the package execution? I've tested this approach and seen the results I expect.

Regards,
Kevin

Hi Kevin,

Thanks for your feedback. I apologize for my confusion. When we use stored
procedure sp_getapplock to place a lock on an application resource. Locks
placed on a resource are associated with either the current transaction or
the current session. Locks associated with the current transaction are
released when the transaction commits or rolls back. Locks associated with
the session are released when the session is logged out. When the server
shuts down for any reason, the locks are released. I am not familiar with
your method in detail. However, if the 'session' you indicated is the one
mentioned above, it should meet your requirements.

Thanks for posting in the community.

Regards,

Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Hello everyone,

Thank you for all the feedback and clarification. I am
now confident using sp_getapplock as the method by which
to avoid multiple instance execution of the same DTS
package.

Regards,
Kevin