weird problem with transaction in ssis package

• Sunday, June 10, 2012 2:34 PM

   

   
  

  0

  Hi,

   

  I have a package with a sequence container with transaction option set to required. The container contained a single dataflow task which for 5 tables did as fallows:

  - read one table

   - do some lookup

  - insert results into another table. Nothing special as you can see. It worked fine.

   

  Then, I decided to split the dataflow task into 5 tasks - each for a table. Although I did nothing just split the task. I got the following error:

  [authors [1]] Error: System.Data.SqlClient.SqlException: Distributed transaction completed. Either enlist this session in a new transaction or the NULL transaction.    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)    at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()    at System.Data.SqlClient.SqlDataReader.get_MetaData()    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)    at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)    at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)    at Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.PreExecute()    at Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostPreExecute(IDTSManagedComponentWrapper100 wrapper)

   

  Any help will be appreciated,

  Rafal

  
  
  

  • Edited by Rafcio666 Sunday, June 10, 2012 2:34 PM
  • Edited by Rafcio666 Sunday, June 10, 2012 2:34 PM
  • Moved by Iric WenModerator Tuesday, June 12, 2012 1:38 AM (From:SQL Server Data Access)
  •  
  • Reply
  • Quote