This post discusses how to parameterize connection strings and values at runtime using SSIS environment variables. Although it may seem like a lot of steps, it's a one-time setup per environment and makes your SSIS projects very portable and allows for easier manageability of dynamic values that change routinely.
Applicable to: SQL Server 2012 or 2014. Screen shots shown are from SQL Server 2014.
What are SSIS Environment Variables?
SSIS Environment Variables provide the mechanism to set values at the time a package is executed. This functionality is useful for any number of things, frequently for specifying different values between Dev, QA, and Prod environments.
Note that these SSIS environment variables are a different thing than Windows environment variables, although similarly named. They're also different from "regular" variables inside an SSIS package.
This technique is applicable to SQL Server 2012 and up, and only to the project deployment mode (i.e., not package deployment mode). The concept of SSIS Configurations is no longer applicable with the project deployment mode as they've been replaced with SSIS Environment Variables. If you're just getting familiar with these concepts, please check out this overview: Getting Started with Parameters, Variables, and Configurations in SSIS 2012 (the overview blog entry has gotten to be one of my most visited pages so I figure that's a clue that I should write more on the subject, eh?).
Steps for Using SSIS Environment Variables to Parameterize Connection Strings and Values When the Package Executes
SQL Server Data Tools (SSDT) - Integration Services Project
Step 1: Create Parameters (Project or Package level as appropriate) and associate expressions, source queries, etc to these Parameters as appropriate.
Step 2: Parameterize connection strings.
Step 3: Deploy Project to the SSIS Catalog once package executes as desired within SSDT.
SQL Server Management Studio (SSMS) - Integration Services Catalogs>SSISDB
Step 4: Create Environment & set up Variables within the Environment. These should coincide with the Parameters from the SSIS project.
Step 5: Configure each Project (and Packages individually, if needed).
Step 6: Execute package in SSMS via the Catalog. Will need to manually specify which Environment to use at execution time when running in this on-demand manner.
Step 7: Create Agent Job to execute on an ongoing basis. Associate the job to the appropriate Environment so it automatically detects values which have been set.
Details for each step are below.
Step 1: Create Parameters and Associate Expressions to the Parameters (SSDT)
First thing to do is determine what elements of the package you wish to be able to vary at runtime. Or, in other words, what may need to be changed by the DBA or system admin without the SSIS developer needing to make a change in SSDT and redeploy the package.
In the example below I have 2 project-level parameters that signify a date range. Since this date range would apply to multiple packages, I made these project-level parameters rather than at the package-level: