Thanks for the response. I will put together a test to see if I am experiencing problems described by Jamie. However, in the meantime, I am able to reproduce the problem easily in our environment.
These are the steps I take:
Create a simple package with an OLE SQL Server data source
On the workstation: Create a system-wide environment variable to contains the path to an XML configuration file (file contains connection info for a development database)
On Server 1 & 2: Create system-wide environment variable (named the same as workstation version) pointing to a new path that contains production database connection string
Configure the package to set the connection string of the data source using an indirect config with the above environment variable
Run the package on workstation to verify that it works
Use BIDS Helper to deploy the package to Server 1 (deployed to SQL Server storage)
Use SSMS to manually deploy the package to Server 2 (deployed to SQL Server storage)
Create a job on Server 1 & 2 to run the package
Now, Server 1 & 2 both have environment variables that point to the same configuration. However, Server 1 runs using the connection string that was configured on the workstation. And, Server
2 runs using the new connection string as referenced in the environment variable. I have tried this on a few different servers and the result is always the same. Maybe it is the same behavior that Jamie was seeing. However, it still seems strange to me that
there would be a difference between the BIDS Deploy and Management Studio.