An SSIS Catalog Connection Literals script is automatically generated using 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 will be used to provide literal overrides. 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.

The next section provides feedback for the professional deploying the script. The feedback includes the same information contained in the head documentation, followed by deployment feedback.

When executed, this documentation section returns messages similar to that shown here:

These messages are intended to be copied and stored in the Notes field of a ticketing system in a DevOps enterprise. Note the detail contained herein:

Script Name – the path to the file used to perform the operation.
Generated From – the SQL Server instance of the SSIS Catalog host from which the script was generated.
Catalog Name – redundant at present because all SSIS Catalogs are named “SSISDB.”
Folder Name – the name of the SSIS Catalog Folder that contains the scripted artifact.
Project Name – the name of the SSIS Project that contains the scripted artifact.
Project Connection Name – the name of the SSIS Project Connection.
Generated By – the name of the enterprise account used to generate the artifact’s script.
- Note: SSIS Catalog Compare respects the security model of the SSIS Catalog. Windows Authentication is required to perform many SSIS Catalog operations.
Generated Date – the date and time the script was generated.
Generated From – the version of CatalogBase used in the generation of the artifact script.
- Executing On – the name of the machine on which CatalogBase was running.
Deployed to Instance – the SQL Server instance hosting the target SSIS Catalog.
Deploy Date – the date and time the deployment script was executed.
Deploy By – the enterprise account used to deploy the artifact script.

Script Support Declarations

The next section of the artifact script is the declaration of parameters used to support the remained of the script’s operations. An example is shown here:

Status and Conditions Checks

The next section of the artifact script checks for the existence of required artifacts like Catalog Folders and Projects. An example of checks for the existence of a Catalog Folder and an SSIS Project.

An example of messages generated by this portion of the script are shown below:

If required preceding artifacts do not exist in the target SSIS Catalog, an error message is generated – similar to that seen here:

Connections Properties Reset

Unlike other scripts generated by SSIS Catalog Compare, Connections Literals scripts reset all related properties (parameters) for a connection manager that are not overridden via Reference Mapping. The portion of the script that manages clearing connection property parameter values is shown here. The results of the execution of this portion of the artifact script are shown below:

Connections Properties Literal Override

The final section of the Connection Property Literal script contains the literal override. This section of the script is laden with existence checks and conditionals.

The results of executing this section of the script are shown below:

(click to enlarge)

Conclusion

As you may glean from this analysis of one script generated for Project Connection Literals 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!