Has anyone tips or techniques for connecting to and pulling metadata from SSISDB catalogs on 10+ SSIS instances?
Remarks
- 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
dtsxpackage 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.
- A colleague is using a SQL query/ sproc to gather basic data down to the
- 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.
- SQL Full Text in our environments is still much underutilized; and MS SQL Server lacks strong regex implementation (even its XQuery comes up lacking).
- 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!