Using Integration Services

Has anyone tips or techniques for connecting to and pulling metadata from SSISDB catalogs on 10+ SSIS instances?


  • An exploratory effort on my part (i.e., no viable code source yet).
    • A colleague is using a SQL query/ sproc to gather basic data down to the dtsx package level from one instance.
    • Runtime per scheduled job seems to have been the focus thus far.
    • CMS is likely for the planned scale-up. Server management is such that this may become complex for SQL coding.
  • Leadership would like to see each package task text body to collect named objects (to determine commonalities between jobs, functions, etc.).
    • SQL Full Text in our environments is still much underutilized; and MS SQL Server lacks strong regex implementation (even its XQuery comes up lacking).
      • Ergo, this post.
  • A check of this forum and elsewhere states that the assembly has been specific to the version.
  • I’ve raised the missing type and check assembly load errors with test scripts.

Many thanks in advance for the advice!