Extract data from dtsx

Question

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

Answer 1

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

Answer 2

There are documentation tools, you can find some examples in this article: https://dbmstools.com/categories/ssis-documentation-tools .

If you want to do it yourself then it can range from a simple to a very high complexity task.

In the end a .dtsx is an XML file. If you know what tables you are looking for and you just need to know if those tables are named somewhere in the package, then a simple search can give you the answers.

Now, if you need to know which tables and how are they used in the packages (e.g. input, output, via OLE or sql command, etc) then you may need to analyse the XML file with xpath, via powershell or SQL and this can be very complex.

If the packages are deployed to an SSIS catalog you can find some information in SSISDB tables.

If custom logs are enabled and the packages have run, you may be able to get the information in the logs.