none
Bulk Insert with another outstanding result set should be run with XACT_ABORT on RRS feed

  • Question

  •  

    Before today when I run the package, everything is fine. Today when we use similiar database with small  schema change, my package can not be run successfully.  Always got '

    Bulk Insert with another outstanding result set should be run with XACT_ABORT on

    ' error. 

     

    Any help will be appreciated. Thanks

    Tuesday, April 1, 2008 5:06 AM

All replies

  • Are you Enabled the Transaction ? Did you need that ?

     

    If Yes, use native SQL Server transactions

    Tuesday, April 1, 2008 5:32 AM
  • Yes, I use transaction. currently I config sequence container property, set transactionOption to required.

    How  to config native  sql server transaction ?

    Why nothing change on package, just run on different database and got different execution result with transaction?

     

    Tuesday, April 1, 2008 5:44 AM
  •  set transactionOption to Supported.

     

    Refer this and get back if u find any issues

     

    http://geeks.netindonesia.net/blogs/kasim.wirama/archive/2007/10/29/managing-transaction-in-ssis.aspx

    Tuesday, April 1, 2008 5:53 AM
  • Thanks Manikandan S.

    My package has two sequence containers. first sequence container do not apply transaction, second sequence container need to use transaction, so I set transactionOption to required. In second sequence container, there are multiple tasks such as multiple data flow tasks and t-sql tasks.  Can I use native  sql server transaction for second container? I suppose native sql server transaction can use only inside one T-SQL task.
    Question:
    1. If I need to use transaction on second sequence container which will perform multiple kind of tasks and transfer data to multiple tables, can I use  native sql server transaction? If I can, how to set for this container.

    2. If I have to use DTC to control transaction, how to deal with issue about 'Bulk Insert with another outstanding result set should be run with XACT_ABORT on'

    Great Thanks.
    Tuesday, April 1, 2008 10:16 AM
  • I'm still waiting for help. Thanks.

    Tuesday, April 1, 2008 9:14 PM
  • For this case, I got answer because OLE DB doesn't allows to fire triggers and my destination table just create trigger serveral days before. Thanks. Please also answer the question for

    ---If I need to use transaction on second sequence container which will perform multiple kind of tasks and transfer data to multiple tables, can I use  native sql server transaction? If I can, how to set for this container.

     

     

    Tuesday, April 1, 2008 10:13 PM
  • You can, but you need to set the RetainSameConnection property to true for the connection manager that you want to use. You'd need to use an Execute SQL before and one after all the tasks to issue the BEGIN and COMMIT TRANS statements.

     

    Wednesday, April 2, 2008 1:06 AM
    Moderator
  • For this case, I got answer because OLE DB doesn't allows to fire triggers and my destination table just create trigger serveral days before.

    I just encountered the same problem.  Changing the triggers to include 'set nocount on'  resolved the problem.  If you change the destination to ADO.Net, a different error is returned that indicates the problem with the trigger returning a result set.
    Friday, June 25, 2010 7:35 PM