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

Header Documentation

The script begins with a header documentation similar to that shown here:

When executed, the Project Reference script header documentation portion displays in the Messages output window of SQL Server management Studio (SSMS):

The statements returned in the Messages tab of SQL Server Management Studio (SSMS) are designed to be copied and stored. The authors recommend enterprises use a ticketing system to manage and track the deployment of enterprise scripts. Before closing a ticket to create a Catalog Reference, the deploying agent is advised to copy the contents of the Messages tab and paste them into the Notes section of the ticket for auditing purposes.

Status and Conditions Checks

The next portion of the Catalog Reference script tests for the existence of prerequisite artifacts:

Prerequisites for a Project Reference include:
• Catalog Folder
• SSIS Project
• Catalog Environment

When executed, this portion of the script returns status messages for prerequisites similar to those shown below:

Create the Reference

The next portion of the Reference Script creates the Reference which is a relationship between an SSIS Catalog Environment and an SSIS Project (or Package). An example of this portion of the script is shown here:

Once this portion of the script is executed, a message similar to that shown in the figure above is returned if the reference is created:

If the script detects the reference already exists, a message similar to that shown below is returned:

Clear the Parameter Value

The next portion of the Reference script clears the parameter value:

The messages generated by this portion of the References script appear similar to those show below:

Set the Parameter Value

The final portion of the script builds the Reference Mapping – the relationship between a Catalog Environment Variable and a Parameter that the Environment Variable value will override at execution-time:

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

After executing the Project Reference 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 as shown here:

Conclusion

As you may glean from this analysis of one script generated for SSIS Catalog References management, the Transact-SQL for scripting SSIS Catalog artifacts in SSIS Catalog Compare is rigorous and includes 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!