An SSIS Catalog Environment script is automatically generated from several methods in SSIS Catalog Compare’s Catalog object named CatalogBase.

Declarations and Header Documentation

The script begins with a declaration of Transact-SQL parameters that support SSIS Catalog Environment Variables contained within an SSIS Catalog Environment. These parameters are placed at the top of the script for easy access by release management personnel, DBAs, DevOps, or other specialists responsible for deployment and deployment testing:

Script documentation follows and is recorded as both Transact-SQL documentation and then printed so it will be part of the output found in the Messages window:

When executed, this portion of the script outputs messages suitable for copying and pasting into the Notes field of a ticket used by enterprise DevOps teams:

The last piece of the script header is the deployment output message, for which the script is shown here:

When executed, this portion of the script produces output similar to that shown here:

Status and Conditions Checks

The next section of the artifact script checks for the existence of the Catalog Folder which is the only prerequisite for a Catalog Environment. An example:

When executed, this portion of the script produces a message similar to that shown:

Catalog Environment Check / Creation

The next portion of the script checks for the existence of the Catalog Environment and creates it if it does not exist:

If the script creates the Environment, the output appears similar to this:

If the environment does not exist, the script informs the individual executing the script:

Environment Variables

The final portion of the script checks for the existence of the Environment Variables and responds accordingly. This is a three-step process:

1. Drop the Environment Variable if it exists.
2. Create the Environment Variable.
3. Set the Environment Variable value.

If the Environment Variable exists the script drops it. Why? SSIS Catalog Compare wants to be sure the environment variable is created with the proper data type and initial values.

The next step is creation of the Environment Variable.

Finally, the Environment Variable value is set. This is somewhat redundant as the value of the Environment Variable is initialized when the Environment Variable is created in the previous step.

An example of the Transact-SQL for this portion of the script is shown here:

After executing this portion of the script, messages similar to those shown below are displayed in the Messages output:

If the SSIS Catalog Environment Variable exists when the script is executed, it is first dropped and the output messages reflect this:

Catalog Environments, Post-Script-Execution

After executing the Catalog Environment script in the target instance, click the Refresh button in SSIS Catalog Compare to observe the updated SSIS Catalog state of the target SSIS Catalog instance:

Conclusion

As you may glean from this analysis of one script generated for SSIS Catalog Environments management, the Transact-SQL for scripting SSIS Catalog artifacts in SSIS Catalog Compare is rigorous, containing several existence and error-condition checks prior to performing any updates. The script is designed to be idempotent, as well, meaning the script will succeed and the results will be repeatable and predictable each time the script is executed – and that the script itself is re-executable.

Learn more about SSIS Catalog Compare!