在 SSIS 包中查找表名引用

我們正在遷移到更高版本的 SQL Server，從 2012 年到 2016 年。我們的 SSIS 包部署到 SSISDB，為了找到 SSIS 包引用的表，我們必須打開它並遍歷每個SSIS 包。

有沒有辦法查詢屬於 SSIS 包的表名？

丹尼斯

如果你檢查表

$$ SSISDB $$.$$ catalog $$.$$ packages $$，您將找到 XML 包。從那裡，您可以解析 XML（可能會很痛苦）並提取標籤。 這可能是一個好的開始：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

我已將此用作獲取 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

引用自：https://dba.stackexchange.com/questions/211796