Getting Anatomical With SSIS Catalog Compare

Andy Leonard shows off parts of SSIS Catalog Compare.  First up is the catalog reference script:

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.

Then an environment script:

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.

And connection literals:

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.

Andy has put a lot of thought into SSIS Catalog Compare, so go check it out.