locked
SSIS - Execute SQL Task RRS feed

  • Question

  • Is there a way I can specify commint point (or max batch size to process)  while executing - for eg. - an update statement in SSIS execute SQL task. 

    I know that there is an insert commit size in the OLEDB destination in the data flow task, but i was looking for something like this in the execute sql task or anywhere else.


    Because if my update statement tries to update millions of records, I want to be able to set a commit size, I think most etl tools have this feature. 


    Any help appreciated.

    Thanks
    Pramod


    Tuesday, October 28, 2008 12:48 PM

Answers

  • Execute SQL is made to run SPs as well as query and for both Insert and Update. Thus it does not have a option of Commit per Batch .

     

    If you want to achieve this You need to handle this in the SP by writing Update for a Range and commiting it when there is no error and then again looping the Update.

    Tuesday, October 28, 2008 1:31 PM

All replies

  • Execute SQL is made to run SPs as well as query and for both Insert and Update. Thus it does not have a option of Commit per Batch .

     

    If you want to achieve this You need to handle this in the SP by writing Update for a Range and commiting it when there is no error and then again looping the Update.

    Tuesday, October 28, 2008 1:31 PM
  • okay thanks for that. That clarifies it all.
    Tuesday, October 28, 2008 2:24 PM