Query SSISDB For Components

  • Evening All,

    I want to identify all SSIS packages deployed into SSISDB that contain a specific component. We have over 5000 pacakges deployed (and closer to 100,000 accross other servers) and I am looking to identify some packages that contain certain components -- one being the FTP task, and another some of the Azure Tasks.

    I've been rummaging around the system tables in SSISDB, and the views and can't see how or where this is exposed. Can anyone point me in the right direction?

    I've read through this link https://docs.microsoft.com/en-us/sql/integration-services/catalog/ssis-catalog?view=sql-server-ver15#ProjectsAndPackages and the bits that hang off that to no avail.

    Cheers All,

    Alex

  • Hmm, looks like it gets compiled to binary and stored in the binary column of SSISDB.internal.object_versions

    Annoying! Nevermind.

     

  • I had to do something a little different,band that was to identify packages whose mail relay server name needed to be updated.

    I ended up querying enabled jobs that call a package, and have the smtp in variable value field

    With you the ftp server name is most likely a package variable and you could query this for a set of valid values

     

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply