Hello all, I have a existing package which includes a connection manager. I tried to edit the connection manager by giving new server name, id and password and tested it successfully. But when I save it and then run the package it seems that the new connection properties are not used and the following error is shown when I try to preview data, Description: "Login failed for user 'sa'.". SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER. In the properties window of the connection manager, the servername is blank and when I try to put the server name and password there it still remains blank. Please let me know how I can change the connection manager properties. Thanks.

How do you run your package (if using an agent then you need an account to run this package that has the rights to connect). But I suspect you a victim of the package protection level property. What did you use? Use for example Save All Data With Password, this way the credentials will persist.Arthur My Blog

I am agree with Arthur, problem probably caused by your protectionLevel. when you set ProtectionLevel as EncryptSensitiveWithUserKey, then sensitive data like connection password will be available for the user who developed the package not others, this is recommended to use EncryptSensitiveWithPassword and set a package password there. everyone who wants to run the package should enter the password otherwise sensitive data will not appear in the package.http://www.rad.pasfu.com My Submitted sessions at sqlbits.com