Query SSIS XML Package to get Destination table names

[capitanosol]

Hi,

I already have this query that works fine to extract information from the package store on SSIS Server.

;WITH XMLNAMESPACES ('

http://www.microsoft.com/SqlServer/Dts'

AS pNS1,
'

http://www.microsoft.com/SqlServer/Dts'

AS DTS) -- declare XML namespaces
SELECT c.name,
SSIS_XML.value('./pNS1roperty[@pNS1:Name="ObjectName"][1]',
'varchar(100)') AS ConnectionManager,
SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]
/pNS1roperty[@pNS1:Name="ConnectionString"][1]', 'varchar(MAX)') ConnectionString
FROM
--
( SELECT id ,
CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
FROM [msdb].[dbo].[sysssispackages]
) PackageXML
CROSS APPLY PackageXML.nodes('/DTS:Executable/DTS:ConnectionManager') SSIS_XML ( SSIS_XML )
INNER JOIN [msdb].[dbo].[sysssispackages] c ON PackageXML.id = c.id
Where Name not in('QueryActivityUpload','PerfCountersUpload','SqlTraceUpload','Manual Backup','TSQLQueryUpload',
'TSQLQueryCollect','QueryActivityCollect','SqlTraceCollect','PerfCountersCollect')

Now I want to extract the desstination table...

I try :

;WITH XMLNAMESPACES ('

http://www.microsoft.com/SqlServer/Dts'

AS pNS1,
'

http://www.microsoft.com/SqlServer/Dts'

AS DTS) -- declare XML namespaces
SELECT c.name,
SSIS_XML.value('ObjectData[1]/pipeline[1]/components[1]/properties[@name="OpenRowset"][1]',
'varchar(100)') AS DestinationTable
FROM
--
( SELECT id ,
CAST(CAST(packagedata AS VARBINARY(MAX)) AS XML) PackageXML
FROM [msdb].[dbo].[sysssispackages]
) PackageXML
CROSS APPLY PackageXML.nodes('DTS:Executable') SSIS_XML ( SSIS_XML )
INNER JOIN [msdb].[dbo].[sysssispackages] c ON PackageXML.id = c.id
Where Name not in('QueryActivityUpload','PerfCountersUpload','SqlTraceUpload','Manual Backup','TSQLQueryUpload',
'TSQLQueryCollect','QueryActivityCollect','SqlTraceCollect','PerfCountersCollect')

But I have no chance...

Help ! please !