Blogs

Copying environment variables from one environment to another using SQL query

Inkey, October 7, 2022 3101 Views

As any developer, one always follows the cycle of deployment using different environments, i.e., DEV (for development phase or the underground lab) -> UAT (for testing phase or Racoon city) -> PROD (for real-time application or the world).

In SSIS also, we are able to deploy the same SSIS package in 3 different environments, but how do we deploy environment variables for assigning values of parameters?

This is the most asked question whenever we deploy SSIS package in SQL – Can we duplicate environment variables?

Initially, we used to add variables in each of the environments manually. This can be done if there are few parameters and also while creating variables, we have to keep in mind that the name of parameters and variables should be the same to avoid any conflicts.

This can be a cumbersome process if the number of environments is more, for e.g., let’s say we have 20 parameters in our package, then this can be a cumbersome process and there are high chances of any mistakes to happen. Furthermore, if there are more environments, then this becomes a very risky part.

Please note that SSISDB does provide a feature to move the environment variable, but it moves the variables and does not copy the variables to the new environment.

So, to do this without much manual intervention below is the approach that uses a simple T-SQL script using which we can copy-paste variables.

Following are the steps that we need to follow: 

• Create a new environment.
  To do this, right–click on the ‘Environment’ folder and select ‘Create Environment’. Here, we have created an environment named “UATTest”.
  
• After creating the environment, right-click on the new environment that you have recently created and then select properties. Take note of the value of the identifier of the new environment.
• Right-click on the old environment and select ‘Properties’. Take note of the value of the identifier of the old environment. For example, we have DevTest as our old environment with 3 variables where we have marked one of the variables as sensitive. Please see below:
  
• Execute the below query to find the rows for the old environment for each variable in that environment. Look for the old environment_id you noted in the above step.  
  

  SELECT 
     * 
  FROM 
     [SSISDB].[internal].[environment_variables]

  
  Please know that we have marked Var3 as a sensitive value so it will be displaying NULL in value and will store the information by encrypting it into sensitive_value column.
  
  

• Now, we have to duplicate the same rows with the new environment ID.  
   
  Below is the SQL Query to “INSERT INTO” statement followed by a select statement.  
  We will need to add a hardcoded value of the new environment ID (which we noted in Step 2) in the “SELECT” statement in the columns and a where clause looking for the previous environment id
  (which we noted in Step 3).
  

  INSERT INTO [SSISDB].[internal].[environment_variables] 
  (
      [environment_id] 
     ,[name] 
     ,[description] 
     ,[type] 
     ,[sensitive] 
     ,[value] 
     ,[sensitive_value] 
     ,[base_data_type]
  )
  SELECT 
      12 AS environment_id --(New Environment ID) 
     ,[name] 
     ,[description] 
     ,[type] 
     ,[sensitive] 
     ,[value] 
     ,[sensitive_value] 
     ,[base_data_type] 
  FROM 
      [SSISDB].[internal].[environment_variables] 
  WHERE 
      environment_id = 11 --(Previous Environment ID)

  Please make sure that you create the new environment first and get the id’s correct in this script and you should be all set.

  

• Now, to test the variables, we can check them by going through the properties of the new environment and then select ‘Variables’.
  Or else we can check it by running the below script:

  SELECT 
     * 
  FROM 
     [SSISDB].[internal].[environment_variables]  
  WHERE 
     environment_id = 12 --new environmentid

  

As we can see here, using this approach would reduce our efforts to have to create it manually every time. We can easily copy the environment variables to different environments.

Please let us know in the comments if you have any queries.

Suggestions are always welcome! 🙂