October 9, 2008 at 11:51 am
Can I lock all other users out my MyDatabase for the duration of my SSIS package? How?
I don't want anyone else to be able to write to any tables in the DB during execution, except for me, the owner of the package.
Thanks,
[font="Courier New"]ZenDada[/font]
October 9, 2008 at 3:26 pm
you could put the database in single user mode, but I'm not sure if it would work from an SSIS package. Worth a try I suppose
October 9, 2008 at 3:37 pm
Adam Angelini (10/9/2008)
you could put the database in single user mode, but I'm not sure if it would work from an SSIS package. Worth a try I suppose
Thanks. Forgive my ignorance. I am an old hand at SQL 2000 DTS. Total n00b at 2005 SSIS. And never ever been a DBA. So I need the exact syntax to put in an ExecSQL task, I guess? It also looks like I can use a Sequence Container with Transaction IsolationLevel = Serializable. Do I want to throw all my tasks in that container?
[font="Courier New"]ZenDada[/font]
October 10, 2008 at 9:30 am
Single-user mode will limit you to a single connection to the database. You can do this, but you would have to set your connection manager to RetainSameConnection and make sure you do not have conflicting parallel operations that require your package to make multiple connections.
A simpler solution may be to simply rename the database before the package runs and then change the name back when you are done. It will not prevent other operations, but it will mean that someone would actually have to change a connection string.
If you actually use security correctly on the database, you could also simply remove all of the other users from the database. If your users are not sysadmin on the server, removing their permissions temporarily will work just fine.
October 10, 2008 at 9:36 am
Why can't anyone write to the db? Not to question your idea, but I'm wondering if there is something else we can come up with.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
October 10, 2008 at 9:45 am
Steve Jones - Editor (10/10/2008)
Why can't anyone write to the db? Not to question your idea, but I'm wondering if there is something else we can come up with.
Spec comes from boss, and that is his request. This is a temporary staging DB where an import of catalog information is taking place between two different companies. They pass in their data. My job is to map it into the my company's db, deciding whether inserts, updates, or 'deletes' (hides) are appropriate. During the decisioning and import, I simply need for this table to remain static (and one other table which has an audit function). Table locks would be fine right?
[font="Courier New"]ZenDada[/font]
October 10, 2008 at 9:50 am
If it is only two tables, table locks are probably the way to go.
That can get a bit tricky with SSIS if you have multiple steps in your control flow - keeping a lock on the table the entire time takes some additional work.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply