I created a package, created an XML Config file, created a deployment utilityand then deployed to server. Changed Connection Managers Data Source and Initial catalog properites during deplyment. Config file in the deployment folder have the right data source and initial catalog properties value. But when i run the package..I see wrong connection string(still showing dev server name) even though I changed during these properties during deployement and shows it right in configuration file in deployment folder. From where the package is reading connection properties....very confusing I will appreciate any help!!! Amar

Don't use the data source and initial catalog properties. Use the ConnectionString property instead. At least, that's worked good for me.

Thanks Phil for you reply. I have used Data Source and Initial Catalog properites in another pacakge and it worked fine...but not this time;)

I even tried ConnectionString property..but still not working. I am missing something here

Are you sure that when you deploy the SSIS package can find the *correct* config file? It's not grabbing values from a duplicate config file sitting somewhere, is it?

SSIS package should use config file in deployment folder...i dont think SSIS is doing that. I cannot find any other config file. Anything else i should check.

When you right-click on the background of the control flow, do you have the XML Configuration file listed under "Package Configurations"? If yes, what directory does it point to? Are you receiving any errors when you open up the package on the second server?

Yes XML configuration file is listed under Package configurations. I am not specifying any directory path..just providing name of the config file..so that the config file is placed in deployment folder. No..I am not getting any error on the second server.Just the connection string is wrong.

Amar Khaira wrote:Yes XML configuration file is listed under Package configurations. I am not specifying any directory path..just providing name of the config file..so that the config file is placed in deployment folder. No..I am not getting any error on the second server.Just the connection string is wrong.Here's the deal. I believe you *need* to specify a path. Here's why. When I opened up and saved a new XML configuration file without a full path, my config file got stored in: C:\Program Files\Microsoft Visual Studio 8\Common7\IDE.Then I deployed the package, and opened the package from the deployment folder (C:\Documents and Settings\Phil Brammer\My Documents\Visual Studio 2005\Projects\Test01\Test01\bin\Deployment), and the values were picked up from the IDE folder, not the deployment folder.Check into that and use absolute paths, where possible.

I can confirm Phil's answer, we ran into a similar problem recently. To use XML config files without the full path, you have to make sure the current working directory is the same as the directory to package is located in. If you are running DTEXEC directly, you could use a batch file to change the directory (CD) before calling DTEXEC. If you are running it from SQL Agent, the only work-around we found was to have Agent call a batch file to run DTEXEC instead of running it directly.

I specified path while creating XML config file...but it still does not work after deployment.I am deploying the package in File System. But here is what i noticed. When I open package from deployment folder by clickingC:\Program Files\Microsoft SQL Server\90\DTS\Packages\Some_FolderName\Package_Name.dtsxit shows right connection properties. But I try to run the package from IS by right clicking the package->Connection Manager it shows wrong server name. I am doing something terribly wrong. Please HELP!! Amar

Unless you have the full SSIS client installed, when you right-click and execute a package, it is using your local machine's properties, not the remote machine's.

I have full SSIS Client Installed.

There is now an open source utility available in CodePlex which can be used to batch update SSIS Package Configuration File paths without using BIDS or manually editing the package XML: http://ssisconfigeditor.codeplex.com/