Copy or Duplicate an SSIS environment in the SSIS 2012 Catalog (SSISDB)

As part of my research for my Managing SSISDB talk, I came across an interesting script by Mike Davis. The script intends to copy an environment in the SSISDB. Mike does a good job explaining why this is useful. The SSIS Catalog does not provide a UI for that.

Mike’s script seems to work, but it has some shortcomings and a potentially problematic flaw. First, it does not actually create the environment, it leaves that up to the user first. Second, it does not copy the references (which projects the environment is linked to) or the permissions. The third shortcoming, and the potential for problems, is that it just copies sensitive variable values from the source environment to the destination environment. This is risky, because each environment gets its own encryption key and certificate.

I have come up with an improved script that addresses the shortcomings (pending some code to copy the permissions, not done yet). It is a much more complicated script because it involves the use of loops (but I avoided using CURSOR). However, instead of directly inserting rows in the database tables (not officially supported), I am using the published stored procedures in the catalog namespace.

The entire script, with inline comments, is available here. However, the different steps in the script are outlined below.

1. Obtain some info about the source environment (name and folder name). To use the script, you only provide it with the source environment’s ID (you can obtain that from the Catalog UI).
2. Create a name and description for the new environment.
   This is easily customizable for your needs. The new name is the source name + the current date in ISO format. The new description is the source description + newline + “Copied by <username> on <date>.”
3. Create the environment, using catalog.create_environment.
4. Copy the variables, including decrypting sensitive values, using catalog.create_environment_variable.
5. Copy the permissions (not currently included).
6. Create references to the new environment in all projects that reference the source environment, using catalog.create_environment_reference.

The code for #6 is more complicated than I’d like it to be, due to Microsoft’s decision to provide two different ways of referencing variables which is based on folder names and environment names instead of IDs. I find it hard to explain, but here’s a go at it.

Understanding the internal.environment_references table

This table contains the information about which projects reference which environments. It uses the ID of the project, but the name of the environment and sometimes the name of the folder of the environment, no IDs there.

Normally, you’d reference an environment to a project using the Catalog UI. You can reference an environment from the current folder or from another folder. If you reference an environment from the current folder, this becomes a relative (type = R) reference. If you reference an environment in a project deployed to a different folder, this becomes an absolute (type = A) reference. R references do not store the environment’s folder name in the internal.environment_references table. The value of the environment_folder_name is NULL for R type references. For A type references, the environment_folder_name is set to the name of the folder. (That’s part of the flaw in Microsoft’s decision… why use names when each folder has an ID available?)

Because there is no restriction to create two environments with the same name in different folders, this poses a problem when you’re reading the contents of the table. It requires that you know which folder the environment you’re copying is stored in. Then, you need to find the environment references that have an environment with the name of your source environment, but you need to have info about the project (you need to join with the internal.projects table for that, using the project_id column). It’s only by knowing the folder in which the referenced project is deployed that you can properly determine if the reference you’re looking at is the reference to your environment or to an environment with the same name in a different folder. But remember the different types of references… what if the environment_folder_name is NULL? See above: it means that the environment is created in the same folder as the project.

That is why there is a fairly complex compound condition in the WHERE clause of the SELECT statement that picks up the next reference:

((R.environment_folder_name = @environment_folder_name AND R.reference_type = 'A'
    AND F.name <> R.environment_folder_name)
OR (R.reference_type = 'R' AND r.environment_folder_name IS NULL
    AND F.name = @environment_folder_name))

• Part I: If the reference type is absolute, then the environment_folder_name in the table needs to be different from the name of the folder of the project, but the environment_folder_name needs match that of the source environment’s folder name (remember the source and new environment are both in the same folder).
  OR
• Part II: If the reference type is relative, then the environment_folder_name in the table needs to be NULL, but the project’s folder name needs to match the folder name of the source environment.

A few more notes

• I am working on providing additional code to copy the permissions.
• You might think it would be nice to be able to create the copy of the environment in a different folder. However, the Catalog UI does provide Move command. So, after you execute this script (and COMMIT the transaction), you can then use the GUI to move the new environment to any folder you like.

by Sven Aelterman.