May 30, 2008 at 9:16 am
I know that SSIS will not allow you to run the same package at the same time. But when I call that package from another package it does allow me to run that single package at the same time. How can I prevent this from occurring?
May 30, 2008 at 2:27 pm
I don't know of a direct way to accomplish this, but a work-around I've used is make the first step in the package be a "running check".
There's probably a better way to do it, but what I do is run a proc something like this:
create table dbo.Flags (
Flag char(20) primary key,
FlagBit bit,
FlagInt tinyint,
constraint CK_Flags check (FlagBit is not null or FlagInt is not null),
LastSet datetime default(getdate()))
go
create trigger Flags_LastSet on dbo.Flags
after update
as
update dbo.Flags
set LastSet = getdate()
from inserted
where inserted.Flag = Flags.Flag
go
create proc SSIS_Running
(@Package_in char(20))
as
if exists
(select 1
from dbo.Flags
where Flag = @package_in
and FlagBit = 1)
select 1 as Running
else
begin
select 0 as Running
update dbo.Flags
set FlagBit = 1
where Flag = @Package_in
if @@rowcount = 0
insert into dbo.Flags (Flag, FlagBit)
select @Package_in, 1
end
In the SQL Task where you run that proc, have a single-row result set, and assign that to a variable.
In the control flow, to get to the first step of package, make it conditional on that value. If it returns a 0 (not running), the package continues as usual. If it returns a 1 (already running), the package does whatever you need it to do instead (wait, or whatever).
Another solution is to make the proc use a WaitFor loop, but that can end up with too many copies of the package "queued up".
Edit: Almost forgot (since it's pretty obvious) - you have to make sure the package resets the flag when it stops running, whether that stop comes from successful completion or from an error, or the package will never run again till someone resets the flag.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply