INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SSIS Package information for documentation

SSIS Package information for documentation

(OP)
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 ('www.microsoft.com/SqlServer/Dts' AS pNS1,
'www.microsoft.com/SqlServer/Dts' AS DTS) -- declare XML namespaces
SELECT c.name as NomPackage,
SSIS_XML.value('./pNS1:Property[@pNS1:Name="ObjectName"][1]','varchar(100)') AS ConnectionManager,
SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]/pNS1:Property[@pNS1:Name="ConnectionString"][1]', 'varchar(MAX)') ConnectionString,
SSIS_XML.value('pNS1:pipeline[1]/pNS1:components[1]/pNS1:component[1]/pNS1:properties[1]/pNS1:property[@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 ?

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close