Hello Biztalk Gurus
I need your help to come up with best design for the following scenario. This may sound like a big description and permutations of queries.
Sorry about that.
Overall requirement – We need to capture changes in source tables in SIEBEL and synchronize it to Salesforce via APIs.
Design – At Siebel side we have decided to put up triggers on table or column level and create messages in SQL Service Broker queue which is then polled/subscribed by BizTalk WCF-SQL adapter.
The message type of queue decides which biztalk interface will trigger. During the Biztalk flow a stored proc is triggered to retrieve actual data from source tables that has changed and then this is transformed and processed and loaded to target tables in salesforce via SOAP APIs.
Questions: –
1) How do I set the message_type in queue (as part of trigger on tables or columns).
Should it be based on table name or column name or overall scenario that I need to deal in a particular subscribing Biztalk interface?
2) If there are 3 tables getting updated as part of single change my above design will create 3 messages in queue and trigger 3 biztalk instances. I don’t want this. What could be work around?
Can I deal that with timestamp. Like last fired timestamp of stored proc greater than current queue time of message? So that my biztalk instance will trigger but not process further cosidering this condition is not met.
3) Also since there are large number of underlying tables and type of data example… person identity, person address, education etc. So i wanted to group them per biztalk interface.
That said if i club Identity and address, and want to update only the updated object in salesforce how shall i do that. (Eg. only id changed so update only ID in salesforce not address)
Can i have further code_type kinda thing in queue message based on which i can further put up a decide activity in Biztalk and separately handle id & address in two parallel flows?
Regards
Shivani