Database account lock

Question

Database account lock

WWL

SSC Enthusiast

Points: 180
More actions
July 15, 2008 at 10:10 am

#381673

Any help would be appreciated!!!
I have an SSIS package that has about 20 Control Flow Items. Each Control Flow item has a data flow that connects to a SQL DB View and migrates info to another SQL DB. The connection manager is setup with an SA account to login to the DB that data is being pulled from and a Domain Account for the DB data is being written to.
We believe the issue to be when the package is opened in BIDS and it's making it's connections, it's bombarding the DB SA Account and locking it up. Thus causing no one to be able to login to the DB data is being pulled from.
Currently the Control Flow items are NOT in a sequential order.
I'm at a loss because I've never seen this happen before. Is there a property I can set that only allows the connection to the Pulling DB once at a time or would setting the Control Flow items in a sequential order help with this?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

WWL SSC Enthusiast Points: 180 More actions · Answer 1

Would the RetainSameConnection Property in the connection manager be what I'm looking for? So it only makes the connection once and not 20 times?

ssismaddy SSCarpal Tunnel Points: 4850 More actions · Answer 2

are all the control flow elements using same database?? If I understood the question, you can use some other alternatives for the data transfer like import/export wizard...

WWL SSC Enthusiast Points: 180 More actions · Answer 3

They are in this package. The import/export wizard would work but not for how we're trying to set things up.

I think what's happening is the connection manager is trying to make twenty simultanious connection and it ends up locking the Account for the DB.

WWL SSC Enthusiast Points: 180 More actions · Answer 4

The issue has been pinpointed a little better. Setup is still the same as in the original post but it only seems to be locking up the DB account when a NEW user opens the package.

I'm guessing that because the package is assigned to userkey and a new user opens the package the connection password is deleted yet it still tries to make the connection and locks out the account???

Again, I'm just guessing so any help would be great.

ssismaddy SSCarpal Tunnel Points: 4850 More actions · Answer 5

I guess, try changing the protection level in the package to "don't save sensitive"....