I have created a package . The source is DEVSQL\SQL1 with windows auth and the destination is TDEVSQL\SQL1 . I created a config file for source so i can change it for QA and DEV . I also created a config table in SQL server on DEV\QA\PROD so the package can dynamically get the destination details(TQASQL\SQL1) and TPRODSQL\SQL1. Somehow when i execute the package using Execute Package utility , the destination does not change from TDEVSQL\SQL1 to TQASQL\SQl1 if i pass the config file that connects to QASQL\SQL1. We will copy the dts files manually to deploy it on QA\PROD and do not use main manifest to deploy. Is it a bug in SSIS or is there any solution to it ? Satish

Where are you running the package? In BIDS or through SQL agent? If from SQL agent, did you add the configuration file in the job properties in SQL agent?

we can double click/right click on the package(.dts) and then do the Execute Package Utility . Pass the config file for QA . I want to make sure this thing work before i put it as a job . Satish

Can you do a "find" in the configuration file and search for the string " TDEVSQL\SQL1" just in case you miss it?

It is getting connected to QA server If I pass QA configuration file but not taking destination connection from QA SQL Server Config table but using it from DEV as defined the package during development Satish

There seems issue with Multiple configuration for same connection. As you are using 2 configurations for connection, please check in which orders configurations are loaded. 1. SQL Server 2. Config File. You may want to change the order to see if proper connection is loaded. Ideally, one connection should be configured from one place, either SQL Server Table or Config file, maintaining consistency across environments.Please vote for answer if it helps you.

How do you point your packages to the SQL Server configuration tables? This is my preferred method (called indirect configuration): place an environment variable on each server. This environment variable contains a connection string to the configuration database on the corresponding server. In your packages, add an OLE DB connection manager. Create a package configuration that takes the value of the environment variable and points the connection manager to the right database. Now your packages always find the correct config table, no matter what machine they are on. Create your other package configurations using the configuration table. Make sure the package configuration set by the environment variable is on top of the list.MCTS, MCITP - Please mark posts as answered where appropriate.

@jayesh , The order is first config file and then SQL server (connectionstring) . I am merely giving an example , the package is more complex and has more connections (teradata connection as well ) . We want to keep the first database connection that has SQL Server config table in configuration file and all other connection in SQL Server Config table. @koen , I have checked indirect configuration but setting environment variabe is not an option. Satish