Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SSIS structure for Documentation


SSIS structure for Documentation

Author
Message
sunnynavman
sunnynavman
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 103
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('./pNS1Tongueroperty[@pNS1:Name="ObjectName"][1]','varchar(100)') AS ConnectionManager,
SSIS_XML.value('pNS1:ObjectData[1]/pNS1:ConnectionManager[1]/pNS1Tongueroperty[@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 ?
ted 84432
ted 84432
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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.
sunnynavman
sunnynavman
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 103
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...
ted 84432
ted 84432
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 21
Looks like you're typing --outputfolder rather than --output_folder - easy to fix Smile
sunnynavman
sunnynavman
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 103
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 !
jakehlucas
jakehlucas
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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!
ted 84432
ted 84432
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
jakehlucas
jakehlucas
Forum Newbie
Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)Forum Newbie (2 reputation)

Group: General Forum Members
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
svantchev
svantchev
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 42
It doesn't work... don't waste your time!
ted 84432
ted 84432
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search