SSIS Package information for documentation

[capitanosol]

Hi,

I am trying to extract some information from SSIS packages store on the server in [msdb].[dbo].[sysssispackages]

For all packages, i need :

PackageName
All Sources information (Server, Databases, list of tables or views that are used in the source)
All Transformations source table or views
All Destination table names with server/databases information

The main goal is to write a program for maintenance in our BI environement. (See witch table or view are no more in use in all our package, cubes, etc...)

Is there a way to do that ?

I have started with this :
;WITH XMLNAMESPACES ('

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

AS pNS1,
'

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

AS DTS) -- declare XML namespaces
SELECT c.name as NomPackage,
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,
SSIS_XML.value('pNS1ipeline[1]/pNS1:components[1]/pNS1:component[1]/pNS1roperties[1]/pNS1roperty[@name="OpenRowset"][1]','varchar(100)') AS DestOrSourceTable


--SSIS_XML.value('(//property[@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/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')

But I am not very good a understanding XML and I lost haft of my hair in the last 2 days tring to make it work.

Is there another way ?