Having now added a like for like DB to do UAT on to our server I have run into a problem with our SSIS Packages. They are stored as files and as such we use File System in SQL Server Agent to run them on a schedule. I now need to run these packages on both databases (different data sources).
I have checked over documentation and forums and when paramterizing over environments every time it relates to the deployment of packages to the catalog. Which I think is because with files they are saved with package data sources not project data sources.
This left me wondering...
Is it possible to switch data sources when calling a package using File System in SQL Server Agent?
Unfortunately the answer on this is no.
Moving to the SSIS catalog allows you to switch between environments which you can set in SSMS.
This is a great link: https://blog.exsilio.com/all/configuring-environment-variables-ssis-package/
I did get an error when running my agent job after this but this was a need to set the runtime to 32bit.
In SSIS - Right click your solution and head to debugging to find the option. In SQL Server Agent - Head to Config and advanced where you would set the environment variable in the SSIS step you have created.