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.

Related Posts

Tips For Migrating SSISDB

Kenneth Fisher shares some thoughts on SSISDB: We’ve been doing a lot of upgrading recently and at one point had to move an instance from one 2016 server to another. In the process, we found out (the hard way) that it’s not that easy to move SSISDB (the SSIS Catalog that may or may not […]

Read More

SQL-to-Excel: A Tool For Automating Exports To Excel

Dave Mason has written a command line tool for Excel-based productivity: In 2018, I’ve found myself frequently running a series of static SQL Server queries and copying/pasting the output of each query into a separate sheet in a Microsoft Excel Worksheet file. They say if you perform a manual task X number of times or […]

Read More

1 Comment

  • Andy Leonard on 2018-09-04

    Thanks for the links and props, Kevin! SSIS Catalog Compare is a labor of love. I am really liking version 3!

    I just published a post about the dangers of deleting artifacts, starting with Catalog Folders. The big danger is deleting an Environment that’s being used elsewhere.

Comments are closed

Categories

September 2018
MTWTFSS
« Aug Oct »
 12
3456789
10111213141516
17181920212223
24252627282930