I spent Friday with a client advising them on some basic design principles, specifically around the use of the Sql receive adapter. It was a fairly simple scenario - extracting data from a SQL Server database, then processing the output messages with some very simple orchestrations. It seemed fairly obvious to me that the easiest way to process the data was to split the output from the receive location, then process each record individually, which is where it started to go awry.
Problem I
The easiest way to split out messages from a batch is to use an envelope schema within a receive port. However, if you create a schema using the SQL adapter wizard (as most people do when using the SQL adapter), the resulting auto-generated schema is not an envelope.
Solution: the solution to this is to map this schema to one that is an envelope.
Problem II
Maps are executed after receive pipelines; if you put the map in the receive port, by the time the map is executed, it is too late to debatch, and you would end up with the envelope in the messagebox, rather than the split documents.
Solution: use a loopback send port to get the envelope back into a receive port for debatching.
Problem III
As per the discussion here, it is not possible to map to an envelope schema in a send or receive port (fixed post-SP1?).
Solution: pass the original batch message into an orchestration, map to the envelope, then use a loopback to get the split messages into the messagebox.
Phew.
By now, the proposed solution was so complicated that, although it worked, it was hard to persuade anyone that it represented a 'best practice'.
A day on the train and a bit of free thinking time got me to the better solution - hacking the auto-generated schema.
Although the generated sql schema isn't initially marked as an envelope, there is nothing to prevent you from doing just that. This means that the direct output of the sql receive location will be split in the pipeline, and that all of the individual messages will be arrive at the messagebox. As a note, the documents that are split out conform to a partial schema, and so if you want to use these, or better still, map them to some common canonical form, then you will need to create a schema with the same namespace that matches this portion of the original sql schema. (If you do decide to do this, remember to mark each element 'Form' as "Qualified".)
I have a sample project that is available on request (I have nowhere to host files, so I'll have to email it out I'm afraid) that selects first and last names from a source table, debatches the output and then maps each document message to a canonical Fullname message. Two separate orchestrations then subscribe to the Fullname message, one that extracts the firstname and inserts into a second table, and another that does the same with the lastname. The sample includes the BizTalk project, sql scripts and a binding file.
STOP PRESS: after three years, I've finally gotten around to uploading the solution for all those who are interested. It's on Codeplex - http://www.codeplex.com/biztalkdebatch
25 comments:
I would like to recieve the sample you used for debatching the results from the sql adapter. Please mail them to martijn dot veldkamp at sogeti dot nl
Thanks in advance.
Hugo,
Could you send me the SQL port debatching example please?
My email is jstrydom@bigpond.net.au
Hi Hugo,
I would also really appreciate if you could send me the example for SQL port debatching.
thanks a lot
martin.kopal@gmail.com
I would also really appreciate if you could send me the example for SQL port debatching
My email:
HungLukeNguyen@yahoo.com
Send sample to my email please:
HungLukeNguyen@yahoo.com
Can you please e-mail me the sample code to pedro.home at gmail.com
Thanks,
Pedro da Silva
This is exactly what i need,
Please email your sample to kruger.johan@gmail.com.
Hugo - I'd love to see your example if you could email it to me - assuming that it is a Biztalk 2004 example. Thank you! Terry Angel - Indianapolis Motor Speedway Corp.
Terry - I'll need your email address if you want to receive the sample.
Hi,
Please mail me the sample yo have !
My mail is sendrajat@gmail.com
Thanks in advance
Hugo,
I'd love to see this example. tangel@brickyard.com
thanks!
Terry
Hi Hugo,
I would like to see the sample. Could you please send it to aldebaran75@gmail.com
Thanks & Best regards
Marcus
I would really appreciate if you could send me the example for SQL port debatching.
my email is sivap515@yahoo.com.
Thanks in Advance.
Hi Hugo,
Can you send me the sample of your SQL debatching program to
charles.emes@uk.ciber.com
Many thanks
I would really appreciate if you could send me the example for SQL port debatching.
my email is cemberton@iccohio.com
If anyone can host this example for download it would surely be a big hit! Thanks alot Hugo et al.
Could you send me the example please?
I don't like how the SQL Adapater wizard spits out schemas. So I used it a few times to get the idea of what the adapter expects in a schema. Now, I create my own schemas everytime. I like it much better that way. You also don't have to deal with a new empty orchestration being created (a weird "feature" in my opinion). I too found that you can make your schema an envelope and the SQL Adapter doesn't seem to mind at all.
Hi Hugo,
Nice article.
I would be thankful if you could send me the sample at
ruchiramathur@gmail.com
Thanks
I would be thankful if you could send me the sample at
dinesh.kansal@bryn.com.au
Hi Hugo,
Could you send me the debatching sql results example..to svhanda@gmail.com..
Cheers
I wonder if you can send the samples?
My email is: liweiguang#126.com (Please change # to @ in order to avoid sparm)
Many thanks.
Hi Hugo,
Please mail me the sample to the below mentioned email
neha.gurajala@gmail.com
Thanks,
Neha
Hi,
I would be thankful if you could send me the sample at
gvhari@gmail.com
Thanks,
Hari krishna
Can u please email the sample code of "Debatching Sql receive adapter resultsets".
My Email Id:
sravanthik06@gmail.com
Post a Comment