Topic
4 replies Latest Post - ‏2013-03-26T12:29:26Z by BrundaKarthikPandit
BrundaKarthikPandit
BrundaKarthikPandit
6 Posts
ACCEPTED ANSWER

Pinned topic Bulk DB Insert/Update result row count

‏2013-03-07T12:16:40Z |
Hi,
we are loading data from DB to Salesforce (after data transaformation) using CastIron - update/insert activity with Delivery rules as following:
Deliver Messages : At Least Once
checked Batch: 200 rows at a time
checked Get Row Counts

After the bulk insert/update I need to figure out which salesforce record was successful and failed. The DB row count is returned as
<?xml version="1.0" ?>
  • <rowCounts>
<rowCount>1</rowCount>
<rowCount>1</rowCount>
.....
...
</rowCounts>

Can anyone please let me know how to figure out from this rowCount which record got processed? Also if any record is with rowCount zero then will the entire batch roll back?

Thanks in advance
Updated on 2013-03-26T12:29:26Z at 2013-03-26T12:29:26Z by BrundaKarthikPandit
  • SystemAdmin
    SystemAdmin
    1250 Posts
    ACCEPTED ANSWER

    Re: Bulk DB Insert/Update result row count

    ‏2013-03-07T13:17:16Z  in response to BrundaKarthikPandit
    If the activity throws an exception (due to connection or data error), the entire set will rollback. For example, if you had 500 records to be processed in batches of 200 and an exception occurs in the processing record 540, all three batches are undone (none of the 250 will.

    You say that you are doing a update/insert. Do you mean an update configured to insert if the row does not exist? If so, I would think that each rowCount value would be at least 1. If there were one or more matching rows, they will be updated; otherwise one new row will be added. More than one row can be updated if the fields that make up your keys match multiple rows.
    • BrundaKarthikPandit
      BrundaKarthikPandit
      6 Posts
      ACCEPTED ANSWER

      Re: Bulk DB Insert/Update result row count

      ‏2013-03-07T13:55:39Z  in response to SystemAdmin
      Thank you very much for the response :)

      I am using seperate Insert and Update DB activites. I have few more questions:
      1) Is it possible to do both bulk insert and update if update activity with insert if row does not exist is checked?
      2) Will there be a situation where the DB rowCount output has 1 and 0 for bulk update? For Example:
      <?xml version="1.0" ?>
      • <rowCounts>
      <rowCount>1</rowCount>
      <rowCount>0</rowCount>
      <rowCount>1</rowCount>
      <rowCount>0</rowCount>
      <rowCount>1</rowCount>
      <rowCount>1</rowCount>
      </rowCounts>
      If the above output happens then how will we know which one of the input record did not process?
      • SystemAdmin
        SystemAdmin
        1250 Posts
        ACCEPTED ANSWER

        Re: Bulk DB Insert/Update result row count

        ‏2013-03-11T13:50:57Z  in response to BrundaKarthikPandit
        The rowCount value will reflect the number of table rows affected by the input data row. If the rowCount is zero, it indicates that there are no table rows matching the corresponding input data row's key values; if the rowCount value is greater than 1, it indicates that the corresponding input data row's key values matched more than 1 table row.

        To use the "0" to indicate which rows now need to be inserted into the table, it is best to match the rowCounts with the corresponding input row. Though nested loops can produce the desired effect, it is probably suboptimal. I would probably merge the input rows and the rowCount document with a stylesheet that retains only the rows that need to be inserted. (I don't this the Data Quality Merge will yield a correct result.)

        If you have the luxury of time, you can see how differing approaches perform with respect to your data. For example, you can use the Data Quality Lookup to identify the rows with a matching key with its output being used to populate two rows sets (one for update and one for insert).
        • BrundaKarthikPandit
          BrundaKarthikPandit
          6 Posts
          ACCEPTED ANSWER

          Re: Bulk DB Insert/Update result row count

          ‏2013-03-26T12:29:26Z  in response to SystemAdmin
          Thanks so much for the help :)

          Since I am novice in writing style sheet, can you please let me know if come across any style sheet which has been used to merge the input data with the rowcount results.