On day 1 of the first package. In playing around with some settings for using variables, I've seemed to have set a few things that are causing issues.
In my dataflow I have a source and target destination with 3 separate lookups. All of these objects utilize the same connection manager. When trying to revert the package to a ProtectionLevel=EncryptSensitiveWithUserKey, I'll see one of the 5 objects fail with a No AcquireConnection issue. I'll stop the debug, test the connection to re-validate it. The next debug run will cause the next object to fail. This repeats in an infinite loop until I get tired of re-validating all of the objects.
What option have I set that has screwed up things?
ControlFlow.Security.ProtectionLevel=EncryptSensitiveWithUserKey
Connection.DelayValidation=False
Connection.RetainSameConnection=False
Any Ideas? Thanks in advance.
Just thought I'd add an update. It appears to be tied to the entire solution and not just a particular package.Here's the actual error:
[LKP-UCM DepartmentId [276]] Error: The AcquireConnection method call to the connection manager "MDW_Staging" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "LKP-UCM DepartmentId" (276) failed validation and returned error code 0xC020801C.
Again, when going into the individual objects I can preview the results and everything is fine. It seems that the connection made is not held for later connections (lookups, destinations). My only solution now is to have the password in the connection manager's connectstring. Seems like a security issue there.
