At my work we have three different environments: dev, test, and prod. We also quite a few different SSIS packages. Currently, when we need to deploy to a particular environment, we update the connection for the package in Visual Studios and deploy it to the correct environment. I'd like to make this easier. I'd like to pass in the environment name, the server name, and the database name and have a parameterized connection string in order to run the package correctly.
I'm currently not allowed to set up an Integration Services Catalog for these environments and variables. The only options I have is to somehow pass in the parameters when I set up the SQL Server Agent Job that will execute these packages on a schedule.
I previously attempted using a parameter in the SSIS package, but when I attempted to pass in a value I got an error stating that parameters are read only.
So, how can I set up these packages to send the pertinent data they need for the server they're running on?
Clarification: We are currently deploying our packages by uploading them to MSDB.
Best Answer
A common approach is to use a different SSIS configuration file for each environment.
Give it the same name, but different contents (server name, local paths, any other variables) in each environment.