Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

SSIS structure for Documentation Expand / Collapse
Author
Message
Posted Friday, November 2, 2012 10:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:11 PM
Points: 5, Visits: 94
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 ?
Post #1380480
Posted Friday, November 2, 2012 10:39 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 13, 2013 12:49 PM
Points: 9, Visits: 21
I wrote an open source SSIS decompiler that can deconstruct your package and replicate it in roughly equivalent C# code. I'd love to hear your feedback if this tool works for you.

http://code.google.com/p/csharp-dessist/downloads/list

I think specifically you'll find it useful because the program can extract all the SQL code from your package into individual statements; you can then easily see where each table is used.
Post #1380503
Posted Friday, November 2, 2012 11:46 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:11 PM
Points: 5, Visits: 94
Thanks for the response...

I downloaded it and tried with a test package but I have an error using it. Maybe i am missing something ?

C:\Tools\csharp-dessist_1.6>csharp-dessist.exe --ssis_filename=c:\test.dtsx --ou
tputfolder=C:\Tools\test
csharp-dessist 2.0.0.0
Copyright (C) Ted Spence 2012

CSHARP-DESSIST - Read in data from an SSIS package and produce an equivalent C#
program using .NET 4.0.

USAGE:
csharp-dessist.exe [parameters]

PARAMETERS:
--ssis_filename=System.String
--output_folder=System.String
[--SqlMode=csharp_dessist.SqlCompatibilityType] (optional)
[--UseSqlSMO=System.Boolean] (optional)

SYNTAX ERROR:
Unrecognized option --outputfolder=C:\Tools\test

C:\Tools\csharp-dessist_1.6>

I tried with paramters in double quotes but no chance...
Post #1380536
Posted Friday, November 2, 2012 11:52 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 13, 2013 12:49 PM
Points: 9, Visits: 21
Looks like you're typing --outputfolder rather than --output_folder - easy to fix :)
Post #1380538
Posted Friday, November 2, 2012 2:06 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 2:11 PM
Points: 5, Visits: 94
Yeah you're right my mistake...

That's very nice what you did even if I dont understand why you did that because I find it easier to work with data in SSIS. But I'am imprest to see that it is possible to extract information without the XML querys since I saw some DLL in your Zip file from MS :

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo.dll

I will try to get information on these DLL

Nice job !

I think i am starting to see the light

Thanks !
Post #1380583
Posted Thursday, May 30, 2013 1:11 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 31, 2013 11:46 AM
Points: 2, Visits: 1
Hi,

I'm interested in viewing this project. I've downloaded version 1.8 from:

http://code.google.com/p/csharp-dessist/downloads/list

I can't get this to run. Does this program work? Is there a newer version I should be using. I'm using following syntax to run program.

charp-dessist.exe --ssis_filename=c:\Projects\Desist\DataLoad.dtsx --output_folder=C;\Projects\Desist\Output

Thanks for you help!
Post #1458385
Posted Friday, May 31, 2013 11:04 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 13, 2013 12:49 PM
Points: 9, Visits: 21
Hi Jake,

Yeah, the program does work - but there was a silly bug in 1.8 that caused the program to throw an error if you ran it from a different directory. I've uploaded version 1.9 - can you check it out here and let me know if it works for you?

https://code.google.com/p/csharp-dessist/downloads/detail?name=csharp-dessist_1.9.zip
Post #1458806
Posted Friday, May 31, 2013 11:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, May 31, 2013 11:46 AM
Points: 2, Visits: 1
Hi Ted,

I downloaded v1.9 and did get it to work. It still reported an syntax error on the --ssis_filename parameter but it still generated output to output folder. I'm reviewing it now to see what kind of output it generated.

Thanks!
-Jake
Post #1458821
Posted Thursday, June 27, 2013 10:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 16, 2014 11:46 AM
Points: 4, Visits: 42
It doesn't work... don't waste your time!
Post #1468257
Posted Thursday, June 27, 2013 11:03 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Saturday, July 13, 2013 12:49 PM
Points: 9, Visits: 21
Sorry to hear that Svantchev. I've used it to decompile a few programs in the past and have had some good experiences, but I haven't been able to get many testable SSIS packages to experiment with. Would love to hear your feedback, if you're able to share what prevented it from working for you.
Post #1468264
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse