SSIS structure for Documentation

  • 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 ?

  • 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.

  • 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...

  • Looks like you're typing --outputfolder rather than --output_folder - easy to fix 🙂

  • 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 !

  • 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!

  • 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

  • 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

  • It doesn't work... don't waste your time!

  • 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.

  • Well, I created a simple package by going via SSMS and doing Export Data, then just saving the dtsx package. It is simple select statement with one column only from one table to export to already existing (but empty) excel file.

    It creates the Program.cs output file, but it is empty... the cmd screen says when I try to run this

    c:\SSIS>csharp-dessist.exe --ssis_filename=c:\ssis\pckg2008.dtsx --output_folder

    =c:\ssis

    SYNTAX ERROR:

    Method '--ssis_filename=c:\ssis\pckg2008.dtsx' is not recognized.

    I've attached a zip file with my examples.. I tried few other packages, but to the same avail.

  • Thanks for the quick feedback. I have noticed that most of the problems are with the command line user interface; I've replaced it with a little WinForms thingy:

    https://code.google.com/p/csharp-dessist/

    The latest package is here, could you try again and let me know if the simple UI helps avoid some of your errors?

    https://code.google.com/p/csharp-dessist/downloads/detail?name=csharp-dessist_2.0.zip

  • Not sure if it is related to the command line anymore, but this is what I get

    Calling ParseSsisPackage with the parameters:

    System.String ssis_filename = C:\ssis\pckg2.dtsx

    System.String output_folder = C:\ssis csharp_dessist.SqlCompatibilityType SqlMode = System.Reflection.Missing

    Boolean UseSqlSMO = System.Reflection.Missing

    6/27/2013 2:34:36 PM STDOUT Exception: System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.NullReferenceException: Object reference not set to an instance of an object.

    at csharp_dessist.Program.WriteProgram(IEnumerable`1 variables, IEnumerable`1 functions, String filename, String appname)

    at csharp_dessist.Program.ProduceSsisDotNetPackage(String projectname, SsisObject o, String output_folder)

    at csharp_dessist.Program.ParseSsisPackage(String ssis_filename, String output_folder, SqlCompatibilityType SqlMode, Boolean UseSqlSMO)

    --- End of inner exception stack trace ---

    at System.RuntimeMethodHandle._InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeType typeOwner)

    at System.RuntimeMethodHandle.InvokeMethodFast(IRuntimeMethodInfo method, Object target, Object[] arguments, Signature sig, MethodAttributes methodAttributes, RuntimeType typeOwner)

    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture, Boolean skipVisibilityChecks)

    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)

    at System.Reflection.MethodBase.Invoke(Object obj, Object[] parameters)

    at CommandWrapLib.ExecuteMethod(MethodInfo mi, Object[] parameters, Form f)

    Should the dtsx package be of specific version? I tried 2012 and 2008 packages... one package selects one table (as built by the sql server) and one package does a simple statement for one column for one table.

    Should have I built the packages using visual studio and not rely on the SQL server built one? How did you test it on your end?

  • Hi Svantchev,

    Now that I'm working using your sample code as a test, I can indeed confirm that you've produced an SSIS package that has some constructs for which I haven't yet written support (SSIS.Pipeline.3, etc). I'm working on a new version of the program that decompiles those objects and will let you know when I make some progress!

  • Sorry for the late reply, I've been busy trying to get this thing working.

    Do you think that might be because I am using SSMS 2012? I can use another version if need be.

    I'll try another very simple package that copies from one sql table to another table w/o the user of SSIS.Pipeline.3

    Can you upload a dtsx file that works for you?

    Ultimately, here is my issue... I am trying to create a solution that would take any number of sql statements and export them as separate sheets/tables to Excel/Access via SSIS API (building a package on the fly) or SSIS package (where the package already exists with params being passed to it via .net code).

    I have a solution that works like a charm using the old DTS API. I was able to work it out by saving the DTS package as VB Script (the old SQL Server allowed us to save it like that). Then I took the VB script and turned it into .Net code... easy breezy... then comes SSIS and I no longer can do such a gimmick, thus having to learn the API from the scratch, which is insane. So, I figure, there has to be a way to decompile the dtsx into C#/VB code and see the step by step I need to take to get to a solution.

    That being the context of why I am trying to use your solution as a guide, let me know if you think of any other way that I could try to get to a solution. That would be very very helpful. I've been beating my head against this wall for 3 weeks now :). In fact, I was able to export one table to Excel tab, but the code fails when it tries to export 2 tables. Ugh...

    Thanks

    Stefan

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply