How to search SSIS Projects for references to specific tables?

Question

Situation

In my new role, I need to check the ETL logic that is populating several of our tables. We deploy SSIS projects to manage our ETL workflow. Often we get feedback that a specific column may not have the expected data. In that situation, I will examine the ETL logic to make sure we are bringing the data correctly.

Complication

It is very time-consuming for me to identify which SSIS projects populate certain tables. We have many SSIS packages.

Question

Can I write a query to search the Integration Services Catalog (SSIS Projects) for a table reference?

Our SSIS Projects are stored in a separate database in a folder called Integration Services Catalog. Packages are not stored in the msdb system database.

SELECT
    f.NAME AS FolderName
    ,sib.description
    ,sib.name as ProjectName
    ,sib.created_time
    ,sib.project_id
  FROM [SSISDB].[internal].[projects] SIB
  INNER JOIN internal.folders F ON F.folder_id = SIB.folder_id

I have found information about the projects but I can't see any XML, or code to search for table references. Also, I know I could search the SSIS project with Powershell, but it seems these are all stored on the server and I have no idea how to find the files to search from PowerShell.

We use SQL Server 2016.

Answer 1

The table references will be inside the XML of the SSIS Package. If you right click on a .dtsx and select edit with notepad++, this will open the xml and allow you to see what is being called. A quick way of doing this to go into you windows search box, type in 'Indexing Options', add the folder where your SSIS solutions are being held, go to advanced options select 'file types' and change the type of index to 'Index Properties and File Options'.

Now when you go to the Folder and type in the table name of the offending column the affected packages will appear. The only thing with this method is if you have some EXECUTE SQL TASKS that are just EXEC usp...xyz. It won't find the table name. You would then need to index the .sql of the saved stored procedures to see if that table name is referenced. Hope this helps.