How to close all conns to target db until child pkgs complete?

[hfdev]

Hello,

I have built a master SSIS package that is configured to run numerous child
packages. Based on a parameter that is passed to the master package,
different child packages are run. The master package is scheduled in a SQL
Agent job a few times with different schedules (based on which child packages
need to run at that time). For example, there is a job that calls the master
to run child packages to pull certain tables (or views) into an Extracts 5
times a day so the data in the Extracts table is up to date a few times a
day. There is another job that runs before nightly batch processing. And
there is another job that is scheduled to run after nightly batch processing.

Here are some details on the Master package:

1) kill all connections to the Extracts database (except the current
connection), put Extracts into SINGLE_USER mode (this is the part I need to
improve)

2) grab list of child packages from a table to run based on the passed in
parameter (parameter is called ScheduleTypeID)

3) run each child package, update last run time in SSIS_FSExtracts config
table; email admin on error

4) put Extracts back into MULTI_USER mode; exit

All of this works 95% of the time.

Now to my question:

The code that I have to kill all connections (except the current one) and
that puts the Extracts database into SINGLE_USER mode works MOST of the time.
Sometimes it fails and sometimes some situation occurs that causes the db to
be stuck in SINGLE_USER mode. Then, getting it back in to MULTI_USER mode is
problematic and requires manual intervention.

Here is the code (from the Master SSIS package that runs just before the
ForLoop container that runs the individual child packages) that kills
connections and puts it into single user mode:

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

DECLARE @DatabaseName nvarchar(50)
DECLARE @spid smallint
declare @CMD1 varchar(8000)

SET @DatabaseName = N'Extracts'
DECLARE my_cursor CURSOR FAST_FORWARD FOR select spid from
master..sysprocesses where dbid = db_id(@DatabaseName) and spid <> @@spid

open my_cursor

fetch next from my_cursor into @spid

while @@fetch_status = 0
begin

SELECT @CMD1 = 'KILL ' + CAST(@spid AS varchar(5))
EXEC (@CMD1)

FETCH NEXT FROM my_cursor INTO @spid
end

CLOSE my_cursor
DEALLOCATE my_cursor


ALTER DATABASE [Extracts] SET SINGLE_USER WITH NO_WAIT
---------------------------

Any ideas on how to accomplish this in a more reliable way?

TIA,

[hfdev]

I just removed the WITH NO_WAIT termination clause from my alter statements.
When I had researched that clause initially, I must have misunderstood the
purpose. I thought it forced the command to happen but it actually does the
opposite if there are any running transactions.

I will let this ride for a while and see how things behave. Any insight is
greatly appreciated.

Thanks,
--
Josh Blair (hfdev)
HydraForce, Inc.

Post by hfdev
Hello,
I have built a master SSIS package that is configured to run numerous child
packages. Based on a parameter that is passed to the master package,
different child packages are run. The master package is scheduled in a SQL
Agent job a few times with different schedules (based on which child packages
need to run at that time). For example, there is a job that calls the master
to run child packages to pull certain tables (or views) into an Extracts 5
times a day so the data in the Extracts table is up to date a few times a
day. There is another job that runs before nightly batch processing. And
there is another job that is scheduled to run after nightly batch processing.
1) kill all connections to the Extracts database (except the current
connection), put Extracts into SINGLE_USER mode (this is the part I need to
improve)
2) grab list of child packages from a table to run based on the passed in
parameter (parameter is called ScheduleTypeID)
3) run each child package, update last run time in SSIS_FSExtracts config
table; email admin on error
4) put Extracts back into MULTI_USER mode; exit
All of this works 95% of the time.
The code that I have to kill all connections (except the current one) and
that puts the Extracts database into SINGLE_USER mode works MOST of the time.
Sometimes it fails and sometimes some situation occurs that causes the db to
be stuck in SINGLE_USER mode. Then, getting it back in to MULTI_USER mode is
problematic and requires manual intervention.
Here is the code (from the Master SSIS package that runs just before the
ForLoop container that runs the individual child packages) that kills
---------------------------
DECLARE my_cursor CURSOR FAST_FORWARD FOR select spid from
open my_cursor
begin
end
CLOSE my_cursor
DEALLOCATE my_cursor
ALTER DATABASE [Extracts] SET SINGLE_USER WITH NO_WAIT
---------------------------
Any ideas on how to accomplish this in a more reliable way?
TIA,
--
Josh Blair (hfdev)
HydraForce, Inc.

[hfdev]

I still have been getting intermittent problems in my Master package where
some MS Access app keeps a lock on a linked table in the SQL Extracts db and
causes the child packages to fail and also causes my Master package fail at
getting the Extracts db out of SINGLE_USER mode.

After reading more about those modes, I changed the Master package to use
RESTRICTED_USER instead of SINGLE_USER mode. Hopefully that helps.

Any advise or insight is greatly appreciated,
--
Josh Blair (hfdev)
HydraForce, Inc.

Post by hfdev
I just removed the WITH NO_WAIT termination clause from my alter statements.
When I had researched that clause initially, I must have misunderstood the
purpose. I thought it forced the command to happen but it actually does the
opposite if there are any running transactions.
I will let this ride for a while and see how things behave. Any insight is
greatly appreciated.
Thanks,
--
Josh Blair (hfdev)
HydraForce, Inc.

Post by hfdev
Hello,
I have built a master SSIS package that is configured to run numerous child
packages. Based on a parameter that is passed to the master package,
different child packages are run. The master package is scheduled in a SQL
Agent job a few times with different schedules (based on which child packages
need to run at that time). For example, there is a job that calls the master
to run child packages to pull certain tables (or views) into an Extracts 5
times a day so the data in the Extracts table is up to date a few times a
day. There is another job that runs before nightly batch processing. And
there is another job that is scheduled to run after nightly batch processing.
1) kill all connections to the Extracts database (except the current
connection), put Extracts into SINGLE_USER mode (this is the part I need to
improve)
2) grab list of child packages from a table to run based on the passed in
parameter (parameter is called ScheduleTypeID)
3) run each child package, update last run time in SSIS_FSExtracts config
table; email admin on error
4) put Extracts back into MULTI_USER mode; exit
All of this works 95% of the time.
The code that I have to kill all connections (except the current one) and
that puts the Extracts database into SINGLE_USER mode works MOST of the time.
Sometimes it fails and sometimes some situation occurs that causes the db to
be stuck in SINGLE_USER mode. Then, getting it back in to MULTI_USER mode is
problematic and requires manual intervention.
Here is the code (from the Master SSIS package that runs just before the
ForLoop container that runs the individual child packages) that kills
---------------------------
DECLARE my_cursor CURSOR FAST_FORWARD FOR select spid from
open my_cursor
begin
end
CLOSE my_cursor
DEALLOCATE my_cursor
ALTER DATABASE [Extracts] SET SINGLE_USER WITH NO_WAIT
---------------------------
Any ideas on how to accomplish this in a more reliable way?
TIA,
--
Josh Blair (hfdev)
HydraForce, Inc.