Extract data from dtsx

I need to create an automatic process that searches in several *.dtsx (version 2017) which tables are being used, is it possible to do this?

And at the end, keep the package name in a table and which table is being used in that package.

Thank you for your time

Solution

DTSX is basically XML file. You can load the content to xml variable and query the table names being used there.

Reference MSDN wiki link. I have modified the code little bit, for your needs. The below code is not tested.

declare @t table
(
x xml
)
 
declare @ObjectName varchar(100) = <Your Object Name To be Searched>
 
INSERT @t
SELECT *
FROM OPENROWSET(BULK '<Full Package Path>',
   SINGLE_BLOB) AS x;

WITH XMLNAMESPACES ('www.microsoft.com/SqlServer/Dts' AS DTS,'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask)
   SELECT   t.u.value('.','varchar(100)')
   FROM DTSPackages
   CROSS APPLY x.nodes('/DTS:Executable/DTS:Property[@DTS:Name="ObjectName"]')t(u)
   WHERE
   x.exist('//SQLTask:SqlTaskData[contains(./@SQLTask:SqlStatementSource,sql:variable("@ObjectName"))]')=1