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:
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.
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.
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! 🙂
© All Rights Reserved. Inkey IT Solutions Pvt. Ltd. 2023