p.MsoNormal, li.MsoNormal, div.MsoNormal {margin-top:0cm;margin-right:0cm;margin-bottom:10.0pt;margin-left:0cm;line-height:115%;font-size:11.0pt;font-family:'Calibri','sans-serif';} .MsoChpDefault {font-size:10.0pt;} .MsoPapDefault {line-height:115%;} @page Section1 {size:612.0pt 792.0pt;margin:72.0pt 72.0pt 72.0pt 72.0pt;} div.Section1 {page:Section1;} Hi, I have copied a number of SSID packages from a live server to my test server and am trying to get them up and running but I have a problem with the connection. The only thing that is different is the server name. I decided to keep the same connection in the connection manager as it is referenced in a dtsConfig file. I changed the dtsConfig file to reference the new servername [Data Source= new test server name]. I also check my paths and they are pointing to the correct config locations etc. I found this post which was similar to my problem but I still get problems with the connection http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1319961&SiteID=1 I updated the connection by changing the server name in the actual connection. I did this from the data flow tab in the Connection Managers sections (MS Visual Studio). Right clicked - Edit - changed the name of the server name to my test server and selected the database. Test connection was fine, and saved this. Once I saved this, the connection string changed to my current test server in the properties of the connection. However when I run the package I get the following error: //////////////////////////////// Error: 0xC0202009 at ReportsImport, Connection manager "myconnectionstring": SSIS Error Code DTS_E_OLEDBERROR.An OLE DB error has occurred. Error code: 0x80004005.An OLE DB record is available.Source: "Microsoft SQL Native Client"Hresult: 0x80004005Description: "Login timeout expired".An OLE DB record is available.Source: "Microsoft SQL Native Client"Hresult: 0x80004005Description: "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections.".An OLE DB record is available.Source: "Microsoft SQL Native Client"Hresult: 0x80004005Description: "Named Pipes Provider: Could not open a connection to SQL Server [53]. ".Error: 0xC020801C at Myprocess import from file, BuyAtReportsImport [79]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.The AcquireConnection method call to the connection manager "myconnectionstring" failed with error code 0xC0202009.There may be error messages posted before this with more information on why the AcquireConnection method call failed.Error: 0xC0047017 at Myprocess import from file, DTS.Pipeline: component "ReportsImport" (79) failed validation and returned error code 0xC020801C.Error: 0xC004700C at Myprocess import from file, DTS.Pipeline: One or more component failed validation.Error: 0xC0024107 at Myprocess import from file: There were errors during task validation. //////////////////////////////// So even if I change the existing connection properties and save and Build my package again, my connection properties (connectionstring, servername etc) all revert back to its original data source when I re-open the package. Simply it does not seem to be saving the connection properties i put in. What am I doing wrong or how can I change the existing connection manager to look at the new server. The database is the same and I have full admin privilege on the server. I don't want to recreate my packages as there are alot and they are not simple. After changing the connection properties, saving and reloading I get a connection error. But this is because it is not retaining the new connection manger setting i entered. Incidentally - I created a new SSID to perform a simple connection and write to the the server and it was OK so there is no problem with actually seeing the my test server. Thanks in advance

Do you have "Named pipes - ENABLED" for the SQL Server ? if you use a dtsConfig file that is added at runtime, the value you add in "properties" in the package itself are never used. they are replaced when the pacakage is executed. have you deployed the pacakge to the sql-server? or do you execute using "dtsexec.exe /f <packagename> (/conf <configfile>)

Hi,Yes I have both TCP/IP and named pipes set as connections (local and remote)Interestingly enough I built the package and deployed it in SQL server as a Job and i executed it with success - writes to my database with no errors - which is good!The problem this leaves me with is that I would ideally like to execute it in design mode so I can debug if I need to or develop the package further.Do you know how to make the connection work in design mode. Even though I ran the Job with success via SQL Server it still fails in design mode with error[ReportsImport [79]] Error: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "myconnection" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed. Thanks

Usually if you change a connection string and it doesn't stick that would mean that there are property expressions on the property that are overwriting the changes when the package runs. Have you checked to see if there are property expressions on your connection strings that could be doing this? Matt

Hi,I've just checked and I have no property expressions on the connection managers.

do you have the package in visual studio? to run it you just right-click and choose "execute pacakge". add breakpoints if needed. did you have any configuration settings (config file , .dtsconfig) added to the project? is the path to this file correct? and is the config correct in this specific file? if you got the .dtsx file, you can easily execute it using "dtexec.exe" as described in my previous post.

Yes, the package is in visual studio and I have executed it. There are no errors but in the output i have the error message about my connection. My dtsConfig file is added to the project in the 'Package Configuration Organizer' and path and files are correct. This is the output error:Error: 0xC020801C at xx import from file, xxReportsImport [79]: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. The AcquireConnection method call to the connection manager "myconnectionname" failed with error code 0xC0202009. There may be error messages posted before this with more information on why the AcquireConnection method call failed.I will try and run it using dtexec.exe. if I have two config files do i use dtsexec.exe /f <packagename> /conf <configfile> /conf <secondconfigfile>

do you use integrated security in the connectionstring? is it possible that you get access to the database but the user that the sql-agent runs under does not ? or something similar... Have you found a more useful error in Eventviewer?

Well I found where my problem was. I thought I had changed all the relevant properties int the dtsConfig file but I didn't change the <ConfiguredValue>servername</ConfiguredValue> setting. I put my new test server name in here and it works just fine now.Thanks for all the advice

I had the same problem, after a little research and focus, i found that i had to change the connection string from my variable. So, check out your variables and initialize ConnectionString, then you can change the properties..Badrou ZEGGAR