1

We are in the process of migration onto a later version of SQL Server, from 2012 to 2016. Our SSIS packages are deployed to SSISDB, in order to find the tables being referenced by the SSIS packages, we have to open it and go through each SSIS package.
Is there a way to query the table names belonging to SSIS packages?

Dennis

1

If you check the table [SSISDB].[catalog].[packages], you'll find the packages XML. From there, you can parse the XML (might be quite painful) and extract the tag.

This might be a good start: https://social.msdn.microsoft.com/Forums/sqlserver/en-US/6c31e43f-4f25-476c-8cfa-87f91fbf609e/help-navigating-xml-file-with-sql-nodes-method-against-a-column-of-xml-type?forum=sqlxml

  • I can see the table but not the XML containing the package definition (SQL 2016) – Sir Swears-a-lot Mar 14 at 3:18
0

I've used this as the base for getting tables and procedures referenced in SSIS (2012)

CREATE TABLE #SSIS (  pkg_name varchar(255), pd xml);

INSERT INTO #SSIS
SELECT s.name, CONVERT(XML,CONVERT(VARBINARY(MAX), packagedata))
FROM [msdb].[dbo].[sysssispackages] s
WHERE description not in ('System Data Collector Package')
    and description not like '%Auto-generated%'


;WITH XMLNAMESPACES( 
    'www.microsoft.com/SqlServer/Dts' AS DTS)
SELECT 
    IDENTITY(INT,1,1) rn,
    r.pkg_name,
    ISNULL (
        y.Mgr.value('./@DTS:ObjectName[1]', 'varchar(MAX)')
        ,y.Mgr.value('./@DTS:ConnectionString[1]', 'varchar(MAX)') 
        ) AS [Source]
    ,y.Mgr.value('./@DTS:DTSID[1]', 'varchar(MAX)')  as SQLConnection
into #Connections
FROM #SSIS r 
CROSS APPLY pd.nodes('//DTS:ConnectionManager') as y(Mgr)
ORDER BY 1,2

;WITH XMLNAMESPACES( 
    'www.microsoft.com/SqlServer/Dts' AS DTS,
    'www.microsoft.com/sqlserver/dts/tasks/sqltask' AS SQLTask )
SELECT 
    IDENTITY(INT,1,1) rn,
    r.pkg_name,
    x.c.value('./@SQLTask:SqlStatementSource[1]', 'varchar(MAX)') AS SQLCmd,
    x.c.value('./@SQLTask:Connection[1]', 'varchar(MAX)') AS SQLConnection
into #SQL
FROM #SSIS r 
CROSS APPLY pd.nodes('//DTS:ObjectData/SQLTask:SqlTaskData') as x(c)
ORDER BY 1,2


select 
    isnull( s.pkg_name , c.pkg_name )  pkg_name
    ,c.Source
    ,s.SQLCmd
    ,c.rn crn,s.rn srn
from #Connections c
full outer join #SQL s on s.SQLConnection = c.SQLConnection and s.pkg_name = c.pkg_name
order by c.pkg_name, s.pkg_name

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.