Restoring a database from SSIS

[JJ]

I need to run several operations (stored procedures) in a database on a
nightly basis. Due to the types of things these procs do (such as TRUNCATE
TABLE) and amount of data they affect, it's not really feasible to run all
of them within a transaction, but I need them to all execute without error
or put the database back into the state it was before they started.

So at the beginning of the SSIS package, I back up the database. Then if
the OnError event triggers, I want to restore the backup made at the
beginning of the package.

Unfortunately, it is failing to do that because the database is "in use"
when it tries to do the restore, complaining that it cannot get exclusive
access. I am setting the connection in that restore task to connect to
"master", but it's either the outer SSIS container that is still connected
to the database or it could be just some other connection that's still out
there.

What's the best way to kill all of the connections to the database within
SSIS so I can do the restore?

I'm thinking of executing a "net stop" or a "net pause", then a "net start"
to restart SQL itself but is there a better way?

I'm not even sure yet if the "net stop" will kill the SSIS package also.

[Adam Mikolaj]

I know this is an older post but you could always use an 'Execute SQL' task and put the DB in single user mode. Doing this would kill all connections except yours.

Once you restore the DB it will be back to multi user mode.

Thanks,

Adam

Post by JJ
I need to run several operations (stored procedures) in a database on a
nightly basis. Due to the types of things these procs do (such as TRUNCATE
TABLE) and amount of data they affect, it's not really feasible to run all
of them within a transaction, but I need them to all execute without error
or put the database back into the state it was before they started.
So at the beginning of the SSIS package, I back up the database. Then if
the OnError event triggers, I want to restore the backup made at the
beginning of the package.
Unfortunately, it is failing to do that because the database is "in use"
when it tries to do the restore, complaining that it cannot get exclusive
access. I am setting the connection in that restore task to connect to
"master", but it's either the outer SSIS container that is still connected
to the database or it could be just some other connection that's still out
there.
What's the best way to kill all of the connections to the database within
SSIS so I can do the restore?
I'm thinking of executing a "net stop" or a "net pause", then a "net start"
to restart SQL itself but is there a better way?
I'm not even sure yet if the "net stop" will kill the SSIS package also.