SSIS - How to Find An Object ( Table,Function or Stored Procedure etc.) If It Is Used in SSIS Package/s?

Scenario:

On daily basis we come to the point where we want to know if the table/view is used in a stored procedure or function. Same way while debugging SSIS Package we need to know if the SSIS Package is using table or Stored procedure so we can further look into the issue.

Solution:

The very first thing came in my mind, open an SSIS Package, Press Ctrl+F or Ctrl+H and see if something show up like the way we find and replace in SSMS or MS Office products.

Unfortunately, that does not work in SSIS Package. So how do I find if the Stored Procedure is used in my package?

I have created Two SSIS Packages and in each of them I have used the Execute SQL Task and each Execute SQL Task is executing dbo.Test Stored Procedure as shown in Fig

Fig 1: Execute SQL Task executing Stored Procedure ( dbo.Test)

The SSIS Package files are saved as xml file. Open all the SSIS Packages in which do you want the stored procedure in BIDS or SSDT. In my case I have already opened two SSIS Packages.

Follow the steps in Fig 2

Fig 2: Find Text in SSIS Packages

Fig 3: Find and Replace in SSIS Package

Put the object name or text that you want to find in SSIS Package.

Check the results in Find Results Pane as shown 

Fig 4: SSIS Package Find Results Pane.

As we can see that the both SSIS Packages are shown in Find Results with Task where dbo.Test is found.

To initiate Find Wizard, in Solution, we need to use Ctrl+Shift+F.