Is there a way to save all queries present in a ssis package/dtsx file?

Question

I need to run some analysis on my queries (specifically finding all the tables which a ssis calls). Right now I'm opening up every single ssis package, every single step in it and copy and pasting manually the tables from it. As you can imagine it's very time consuming and mind-numbing.

Is there a way to do export all the queries automatically ?

btw i'm using sql server 2012

Answer 1

Retrieve Queries is not a simple process, you can work in two ways to achieve it:

Analyzing the .dtsx package XML content using Regular Expression

SSIS packages (.dtsx) are XML files, you can read these file as text file and use Regular Expressions to retrieve tables (as example you may search all sentences that starts with SELECT, UPDATE, DELETE, DROP, ... keywords)

There are some questions asking to retrieve some information from .dtsx files that you can refer to to get some ideas:

• Reverse engineering SSIS package using C#
• Automate Version number Retrieval from .Dtsx files

Using SQL Profiler

You can create and run an SQL Profiler trace on the SQL Server instance and filter on all T-SQL commands executed while executing the ssis package. Some examples can be found in the following posts:

• How to capture queries, tables and fields using the SQL Server Profiler
• How to monitor just t-sql commands in SQL Profiler?
• SSIS OLE DB Source Editor Data Access Mode: “SQL command” vs “Table or view”
• Is there a way in SQL profiler to filter by INSERT statements?
• Filter Events in a Trace (SQL Server Profiler)

Also you can use Extended Events (has more options than profiler) to monitor the server and collect SQL commands:

• Getting Started with Extended Events in SQL Server 2012
• Capturing queries run by user on SQL Server using extended events