SSIS : Use SSIS Environment variable to maintain multiple server connection string

In the real project, we need to maintain lots of environments for different purpose, like testing server, staging server and production server . How can we deploy SSIS project to different server and run directly without any code change.

I am going to use DB Connection string as an example in this blog to show you how can we connect to different database without code change after project deployed.

  1. Add new Connection Managers
  2. Right click the new created connection manager then select ‘Parameterize…’ from popup list

3. change property to ‘ConnectionString’, select ‘Create new parameter’ in Parameterize window and enter the parameter name

4. Once you saved this configuration then you can see your saved parameter in Project Parameters windows

5. In Management Studio, expand the Integration Services Catalogs> SSISDB node in Object Explorer, and locate the Environments folder of the project for which you want to create an environment. Type a name for the environment and optionally a description, and then click OK.

6. Right-click the new environment and then click Properties to open properties window.

7. On the Variables page, do the following to add a variable:
Enter the variable name
Select the Type for the variable.
Enter the Value for the variable

8. Click OK to save your changes to the environment properties.

9. Under the SSISDB node in Object Explorer, expand the Projects folder, right-click the project, and then click Configure.

10. On the References page, click Add to add an environment, and then click OK to save the reference to the environment.

11. Right-click the project again, and then click Configure. In the Parameters tab on the Parameters page, click the browse button next to the Value field. Click Use environment variable, and then select the environment variable you created.

12. Click OK twice to save your changes.

Leave a Reply

Your email address will not be published. Required fields are marked *