asked
SSIS 2005 connection manager issues
I have a project with about 30 separate Pkgs. Each pkg has 5 connection managers to the same 5 connections. These connections are in a config file and each package is pointing to the same config file. Each time I open the project and then one of the packages, one or more of the connection managers receive the error: > ssis error code > dts_e_cannotacquireconnectionfromconnectionmanager. > the acquireconnection method When I reopen the config file, one or more of the connections is no longer checked to be in the config file. How does this happen? I am a bit confused on all the encrypt/ don't encrypt protection level, True/false delay validation and the 64 vs 32 bit properties that can be set and how that affects connection managers. I can't seem to find consistency for the settings among opinions and answers. Is there a standard from which to start? I have a windows 2003 server with 2005 sql and BIDs. How do I determine if I am running in 32-bit or 64? Is this something set in both sql server, windows server and BIDS?
You've asked a number of questions, so please bare with me: In regards to the encryption, check out:
http://msdn.microsoft.com/en-us/library/ms141747(v=sql.90).aspx. The delay validation refers to when the package loads, by default it will attempt to validate all of the connections in order to make sure that they are present. If you are attempting to do dynamic setting of the connections through code, you'd want to set this flag to TRUE to prevent any validation errors from appearing. The 64/32 bit will refer to where ever you are running the package. If the package is going to run on a 32 bit server, you won't have to change anything. You can determine this by looking at the computer properties of the server. It will tell you if the server is 32 bit or not. If the Windows server is 32 bit, SQL Server will be 32 bit as well. In my environment we don't use dynamic connections so we usually do not turn on Delay validation and we do not change the encryption levels. Was there any additional information regarding the error? I found a couple possible solutions but it would depend on what else was contained within the error. Hope this helps!
Hi John, Thanks for all the info. Regarding the actual problem. Here is the only info I get from BIDS. Error 1 Error loading Apmm Build dimManufacturer.dtsx: The connection "SSIS_LOGS" is not found. This error is thrown by Connections collection when the specific connection element is not found. Warning 2 Warning loading Apmm Build dimManufacturer.dtsx: Cannot resolve a package path to an object in the package ".Connections[SSIS_LOGS].Properties[Name]". Verify that the package path is valid. Warning 3 Warning loading Apmm Build dimManufacturer.dtsx: The package path referenced an object that cannot be found: "\Package.Connections[SSIS_LOGS].Properties[Name]". This occurs when an attempt is made to resolve a package path to an object that cannot be found. For this particular pkg I get the error and warnings repeated 4 times. There are 4 SQL tasks and 1 data flow task. If I don't use the Delay Validation option, I get the red circle with the cross in the data flow task, both source and destination. Thanks again, Maggie
SSIS_Logs is a database on my sql server. I understand that it telling me the connection can't be validated. But it is not always the same db that cannot be validated. As I type I am having an epiphany. The db that could not be validated is now unchecked in the wizard - this is the result of the problem, not the cause. (possibly?) But the config file does contain the correct properties to connect. So Maybe the question is why isn't the connection in the config file working? Is there any way to validate the data in the config file without using a package? Any ideas why SSIS can validate some connections in the config file and not all? And why different connections at different times. I'm a bit panicked at this becasue I am getting ready to push a new app out and was planning to use the pkg config concept again. It prevents me from having to re-create a build each time I need to connect to the dev vs prod server.
10 People are following this question.
