I have been scouring the internet for a clear answer to this question and I haven't been able to find one.
We currently have a SQL configuration enabled across packages which stores everything in a database, (dev or prod). It is a tricky process to execute packages, figuring out which configuration the packages are mapped to, and having to change them during testing. My hope is that we can use an environment variable to always use a specific connection string to the specific sql server configuration we want to use based on the machine.
I have seen ways to set up variables with the connection string hard coded as an environment variable with an additional SQL server configuration after to manage this. I have also seen ways to manipulate the connection string using XML and calling that from a variable, or from the package itself.
Both of these solutions work and very well may be viable solutions. My question that I'm really trying to answer is; Is it possible to develop the entire SQL Server configuration connection through the environment variable? As in combining the connection string with the configuration string so that the one environment variable connects straight to the package configuration? Has anyone tried this? Does it make sense as a solution to the problem?