Sunday, March 13, 2005

Debatching Sql receive adapter resultsets

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:

Anonymous said...

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.

Anonymous said...

Hugo,
Could you send me the SQL port debatching example please?


My email is jstrydom@bigpond.net.au

Anonymous said...

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

hung said...

I would also really appreciate if you could send me the example for SQL port debatching

My email:
HungLukeNguyen@yahoo.com

hung said...

Send sample to my email please:
HungLukeNguyen@yahoo.com

Pedro da Silva said...

Can you please e-mail me the sample code to pedro.home at gmail.com

Thanks,
Pedro da Silva

Anonymous said...

This is exactly what i need,
Please email your sample to kruger.johan@gmail.com.

Terry A. said...

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.

Hugo Rodger-Brown said...

Terry - I'll need your email address if you want to receive the sample.

Anonymous said...

Hi,

Please mail me the sample yo have !

My mail is sendrajat@gmail.com

Thanks in advance

Anonymous said...

Hugo,

I'd love to see this example. tangel@brickyard.com

thanks!

Terry

Anonymous said...

Hi Hugo,

I would like to see the sample. Could you please send it to aldebaran75@gmail.com

Thanks & Best regards

Marcus

Anonymous said...

I would really appreciate if you could send me the example for SQL port debatching.
my email is sivap515@yahoo.com.

Thanks in Advance.

Anonymous said...

Hi Hugo,
Can you send me the sample of your SQL debatching program to
charles.emes@uk.ciber.com
Many thanks

Anonymous said...

I would really appreciate if you could send me the example for SQL port debatching.
my email is cemberton@iccohio.com

Anonymous said...

If anyone can host this example for download it would surely be a big hit! Thanks alot Hugo et al.

Perry said...

Could you send me the example please?

Mike Murray said...

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.

Anonymous said...

Hi Hugo,

Nice article.

I would be thankful if you could send me the sample at
ruchiramathur@gmail.com

Thanks

Anonymous said...

I would be thankful if you could send me the sample at
dinesh.kansal@bryn.com.au

Sandeep said...

Hi Hugo,
Could you send me the debatching sql results example..to svhanda@gmail.com..
Cheers

Weiguang said...

I wonder if you can send the samples?

My email is: liweiguang#126.com (Please change # to @ in order to avoid sparm)

Many thanks.

Anonymous said...

Hi Hugo,

Please mail me the sample to the below mentioned email

neha.gurajala@gmail.com

Thanks,
Neha

Anonymous said...

Hi,

I would be thankful if you could send me the sample at

gvhari@gmail.com

Thanks,

Hari krishna

Sravanthi said...

Can u please email the sample code of "Debatching Sql receive adapter resultsets".

My Email Id:
sravanthik06@gmail.com