July 16, 2008 at 10:20 am
Hi,
I have 2 pkgs (A and B) that are scheduled to run when their respective input files arrive. Occasionally these packages end-up overlapping (running at approx. the same time). This leads to a deadlock situation with a table object on the destination server (DB2) which is being read/deleted/inserted from/to by both pkgs. Any suggestions on how to resolve this problem (I'm hoping for a range of suggestions we can pick from to best suit our environment).
Regards and TIA, Chris.
July 16, 2008 at 1:08 pm
If the packages are being run by the Job Agent, it is easy enough to check if the other job is currently running and wait (see sp_help_job).
If they are both running on the same SSIS Server, there must be a way to determine if a package is currently running because currently running packages can be seen in Management Studio. I have never tried to do this, but it seems like a logical approach. You are likely to find that running packages are sitting in a table in MSDB.
You could put a table and a flag somewhere, set the flag to 1 in the table when your package starts and reset it to zero when done. Then you could check if the other package was already running by looking at the flag.
This options could be expanded on by using sp_GetAppLock (see books online). If you can keep a connection open the entire time your package is running (which is not hard to do) you could take an app lock. When your second package starts, if it tries to take the same app lock it will wait until the resource is released.
Ultimately, the best solution would be to resolve the conflict so the packages can both run at the same time. That would be the first place to start.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply