none
How to implement iterative Counters in BizTalk

    Question

  • Here is my scenario

    I receive multiple files and each file has more than one line item. I need to store the line items in oracle database. I need to tag each line item with an auto-increment counter. Its an external oracle database and the field which holds the counter is not sent to auto-increment, so I have to populate the value when sending to database. Stored procedure or function is not an option.

    How do we implement incrementing counters in BizTalk.


    Racha Rams

    Sunday, July 16, 2017 7:05 PM

All replies

  • Per file or across multiple files?

    From the description it seems to be the across multiple files and it's getting a bit tricky to have a sound and bullet proof solution

    Per file is easy (advanced functoids / iteration)

    /Peter

    Sunday, July 16, 2017 9:50 PM
  • Hi ReyRam,

    Can we get more information on the solution that you have implemented? Are all the line items inserted at the same time or one line item at a time? Depending upon that we can suggest you more pointers.

    Also why not have the changes made to the stored procedure to auto increment the counter while doing an insert? That will be the correct way to take care of the approach in my opinion.


    Mandar Dharmadhikari

    Sunday, July 16, 2017 11:55 PM
    Moderator
  • Is it possible to request a sequence in the Database...

    Oracle Sequence can be used in this scenario.

    Create a sequence in Oracle Server.

    You can use that in your Insert Statement / Map.

    Thanks

    Prabath

    Monday, July 17, 2017 7:25 AM
  • Oracle Sequence would be good solution if the counter is across multiple files, BizTalk needs a little help with it

    /Peter

    Monday, July 17, 2017 8:08 AM
  • Its across mutiple files

    Racha Rams

    Monday, July 17, 2017 2:25 PM
  • The oracle db is not hosted within our environment, so there is some resistance from db side to host a stored proc or create any artifacts on db. I had to handle it in BizTalk

    Racha Rams

    Monday, July 17, 2017 2:27 PM
  • Use BizTalk SQL Server and update Oracle from local staging tables 

    /Peter

    Monday, July 17, 2017 2:42 PM
  • Its across the files

    Racha Rams

    Monday, July 17, 2017 2:43 PM
  • You mean should I use 

    CREATE SEQUENCE FILEROWCOUNTER START WITH 1000 INCREMENT BY 1 NOCACHE NOCYCLE;

    If I use this statement in BizTalk. Each time the orchestration runs, its gonna , create the sequence starting

    with 1000.

    But I want something like say suppose File A has 10 records.. File B has 5 records

    The inserts should be like 1000,1001,1002.....so on till 1010 for all records in File A, and then

    1011, 1012,1013,1014, 1015 for File B records when inserted in DB.




    Racha Rams

    • Edited by Racha Rams Monday, July 17, 2017 2:52 PM
    Monday, July 17, 2017 2:50 PM
  • So...you basically have an impossible situation.  The answer is you can't, or at least not in a guaranteed way.

    First, this is not a problem with you, your app or BizTalk Server...or even Oracle.  This problem is created by the owners of the Oracle database.  Autonumber fields have existed for literally decades and are specifically for this exact reason.

    Second, if they are unable or unwilling to do this 'correctly', the next thing you have to do is inform you management that due to their 'interesting' implementation, you will have to spend a lot of extra time and money to accommodate this situation and you cannot guarantee 100% reliability

    Finally, to do this with any reasonable reliability, you will have to implement a pattern such as:

    1. Implement as Singleton in you BizTalk app for inserting the records into Oracle.
    2. Before every Insert, query the db for the highest value in the 'number' field.
    3. Increment that by one.
    4. Insert your real record with your incremented value.

    If your BizTalk app is the only one doing this, it should mostly work.  If any other app is inserting these records, it will not.

    Make sure your management and the db owner understand that it's is their decision that is requiring the relatively very expensive query to get the current highest value.  If they tell you to maintain the counter yourself...well, no...that just will not work.  Sorry.


    Monday, July 17, 2017 2:52 PM
    Moderator
  • It's not that bad, SSIS can handle it out of the box

    But yes it's like swimming across Mississippi to get a cup of water

    /Peter

    Monday, July 17, 2017 3:08 PM
  • Unfortunately, there are multiple apps that insert into that table.

    :(


    Racha Rams

    Monday, July 17, 2017 5:08 PM
  • Can't be done, end of story

    /Peter

    Monday, July 17, 2017 5:10 PM
  • +1.

    There is no way this can work.

    Don't forget, this is not your problem.

    Monday, July 17, 2017 5:30 PM
    Moderator
  • >>>

    Unfortunately, there are multiple apps that insert into that table.

    >>>

    That's the business case for any DBA to create a sequence. Just push for it.

    Thnaks

    Prabath

    Tuesday, July 18, 2017 9:06 AM
  • As multiple applications inserts data in the table you need access to a sequence on the oracle server

    /Peter

    Tuesday, July 18, 2017 9:28 AM