none
Programatically executing package within existing transaction

    Question

  • I want to execute a package using c# within the context of an existing transaction.  I'm using OLEDB and ADO.NET connection managers within the package.

    Basically I want to be able to do this, where the transaction parameter is an already existing transaction:
    DTSExecResult result = _package.Execute(_package.Connections, _package.Variables, packageEvents, null, transaction);


    The API documentation is very unclear about a) whether it is possible to do this and b) what the type of the provided transaction should be.  The method signature accepts an object transaction parameter - no idea of what type constraints there are for this.

    If I create a new System.Transactions.TransactionScope and pass that in I get an exception:
     The package Package1 did not successfully execute
     Error (-1073594108):
     Source: Execute SQL Task
     Subcomponent: 
     Description: The Execute method on the task returned error code 0x8013150C (Type 'System.Transactions.TransactionScope' in Assembly 'System.Transactions, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' is not marked as serializable.). The Execute method must succeed, and indicate the result using an "out" parameter.

    I'm wondering if the only way to pass in a transaction to a package execution is when the package is running in the context of a parent package and the parent package has specified a transaction - which would then get propagated to any child packages...

    Any help would be great. :)

    PS - purpose of all this - unit testing packages.
    Thursday, August 06, 2009 10:05 PM

All replies

  • Further to this - I have already seen this post: http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/cb9cce48-b325-44c8-b4a8-673c24bacdb8/

    But - the API seems to indicate (contrary to the answer to that post) that there is indeed a transaction parameter on the Execute method.  I'm just after more details as to whether that transaction parameter is actually assignable - and if so, how!!
    Thursday, August 06, 2009 10:11 PM
  • The transaction used by SSIS is a COM object, not a managed class. So no, I don't think you can do this, unless you use a "wrapper" task to get the transaction object from an already running package, and pass it through to the other package.

    I do a good bit of unit testing for SSIS, and I'm getting along pretty well without transactions. Of course, I've never relied on the built-in SSIS transactions for other reasons, so I might be a bit biased. Could you describe your scenario in a little more detail? Maybe I can provide some different ideas. 


    John Welch | www.mariner-usa.com | www.agilebi.com | ssisUnit.codeplex.com
    Friday, August 07, 2009 12:36 AM
    Moderator
  • Hi John,

    Yep, lost the trail of the transaction type when it disappeared into the COM call in PackageClass.Execute.  :|

    I've been working on a fairly large unit testing suite for a new SSIS implementation.  I used ssisUnit to get started with the API but I've basically gone with standard C# test classes since I've got a .NET development background (and I do understand why you went with xml-based test defns).   So I've got TeamCity running MBUnit tests against SSIS.  MBUnit is nice because I can use ordered tests to replicate package execution day1, package execution day2, etc,  and I can also use csv as rowtest sources - nice when the dev team here is very new (so they just define a csv row per test and don't have to care about c#).  Very nice getting all the test reporting/feedback goodness from TeamCity - and it means I can get people to do test-first development. :)
    FWIW, in order to keep any test-related cruft out of the packages I'm overriding data source sql stmts and connection managers in the tests.  A table-valued function provides well known test data in the exact same format as the original sql statement.  The component processes my well-known input, inserts/updates the target table.  Then I'm doing rowcount and agg_checksum over the target table - the checksum is compared to another tvf which represents the data I expect to be in the table after the package has executed.

    Anyway, the main issue is that i'm starting to get data dependencies leaking between different packages.  For instance, our database project loads some static data for lookup/code translation tables (we're using DBPro GDRv2).  For a dev/continuousbuild test run we always redeploy the db with new static data.
    Testing a package which loads new/changed records into a lookup table works fine.  Truncate table, execute package with well-known data, do asserts on results.  Increment control dates, execute package with different testdata tvf, do asserts on results.  The asserts are comprehensive (incl full table data compare) and easy to do because I start off with an empty table.
    The problem I have is that when I test a dimension load package (entirely different test scenario), which depends on the lookup table containing the correct static data.  At the moment I'm re-running the static data load in the teardown for the lookup table test - so when the test has finished the lookup table data is back to normal.  It's a bit fugly though and I would definitely prefer to do normal unit testing stuff like transaction rollback to keep everything in a consistent state.

    I have considered your suggestion though - in the test, create a new parent package on the fly, set it to use transactions, add the package under test to it, enlist the child package in the tranasction, have some failing task after the child package execution.  Probably attach a postexecute event handler to the package under test, inside the handler do all the asserts.  Finally run parent package  - which will execute child package, do asserts, rollback parent (and child) package...  Even more fugly and maybe not feasible.

    I would obviously prefer to be able to run the entire package within a simple transaction, and then just rollback... :)

    thanks for your reply though - I wonder if I'm trying to push SSIS maybe that one step too far.

    PS - sometime soon, once i've got this unit test pattern out of the way, I'm going to be blogging the entire process I've got.  I've just taken your ssisUnit examples (which are excellent, thanks heaps!) and extended these to include powershell build scripts incl db deployments, unit tests via mbunit and continuousbuild via teamcity.  I'll let you know when I've got something solid to share.
    Friday, August 07, 2009 2:33 AM
  • Hi Justin,

    I'm looking to achieve the same thing and wondered if you were ever able to get this working (or found a suitable alternative)?

    Thanks,
    Stuart.

    Friday, June 15, 2012 8:53 PM
  • hey there,

    sorry, that was a long time ago - hardly even remember what ssis is, let alone what the result was...!  

    Tuesday, June 19, 2012 9:22 PM
  • Hi,

    Thanks for coming back to me, though it was a bit of a long shot! :-)

    Stuart.

    Thursday, June 21, 2012 10:40 AM