November 2, 2012 at 10:02 am
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 ?
November 2, 2012 at 10:39 am
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.
November 2, 2012 at 11:46 am
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...
November 2, 2012 at 11:52 am
Looks like you're typing --outputfolder rather than --output_folder - easy to fix 🙂
November 2, 2012 at 2:06 pm
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 !
May 30, 2013 at 1:11 pm
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!
May 31, 2013 at 11:04 am
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
May 31, 2013 at 11:47 am
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
June 27, 2013 at 10:32 am
It doesn't work... don't waste your time!
June 27, 2013 at 11:03 am
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.
June 27, 2013 at 11:47 am
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.
June 27, 2013 at 12:29 pm
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
June 27, 2013 at 12:36 pm
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?
June 27, 2013 at 5:04 pm
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!
July 2, 2013 at 3:21 pm
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