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 Thursday, June 27, 2013 11:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 9:24 AM
Points: 4, Visits: 41
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.


  Post Attachments 
csharp-dessist.vshost.zip (1 view, 1,011.08 KB)
Post #1468278
Posted Thursday, June 27, 2013 12:29 PM
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
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
Post #1468291
Posted Thursday, June 27, 2013 12:36 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 9:24 AM
Points: 4, Visits: 41
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?
Post #1468293
Posted Thursday, June 27, 2013 5:04 PM
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 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!
Post #1468390
Posted Tuesday, July 02, 2013 3:21 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 9:24 AM
Points: 4, Visits: 41
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
Post #1469771
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse