none
Need to guarantee one insert transaction for ole db insert transform

    Question

  • Hi,

    I have a data flow that has an insert ole db at the end

    Is increasing the DefaultBufferMaxRows property to a large number the correct way to guarantee a one transcation for insert ?

    Sunday, July 27, 2014 9:53 AM

Answers

  • Hi denisroyi2,

    In OLE DB destination, if we are using FastLoad mode and keep "FastLoadMaxInsertCommitSize" as default(2147483647), the data will be committed in one transaction.

    DefaultBufferMaxRows is to define the maximum number of rows in each buffer. Only when we can FastLoadMaxInsertCommitSize to be 0, we need to increase both the DefaultBufferMaxRows  and DefaultBufferSize. FastLoadMaxInsertCommitSize equal to 0 means the OLE DB commit data after every buffer. That is why need to increase the buffer size and rows.

    Thanks,
    Jinchun Chen

    • Marked as answer by danisroyi2 Monday, July 28, 2014 10:31 AM
    • Unmarked as answer by danisroyi2 Tuesday, July 29, 2014 12:50 PM
    • Marked as answer by danisroyi2 Tuesday, July 29, 2014 12:52 PM
    Monday, July 28, 2014 8:28 AM