- This topic has 3 replies, 1 voice, and was last updated 9 years, 1 month ago by
.
-
Posts
-
-
I've got a flat file generated from two joined tables created using DTS. The query is something like "SELECT * FROM POHeader JOIN POLine ON POHeader.PONumber = POLine.PONumber". Consequently each line in the file from DTS contains fields from the POHeader and POLines tables combined. The file can contain multiple purchase orders, each with multiple lines, which I need to break up into single Purchase Order messages for further processing. The file looks something like this – first three fields from POHeader, next two from the POLine with lines CRLF delimited and fields TAB delimited:-
PONumber Date Supplier Item Quantity
1 23/11/06 Amazon BizTalk Pro 2006 1
1 23/11/06 Amazon BizTalk 2006 Recipies 2
2 22/11/06 Another Some Other Book 5
2 22/11/06 Another Last Book 3
What I really want to do with this example data is to convert this into two seperate POHeader nodes in the XML (repeating records?) each with two POLine child nodes and get rid of the first row with the column headings. However no matter what I do I can't convince the Flat File Schema wizard that this is the structure of the data. It always pulls in 4 seperate XML nodes instead. I've tried pushing the message with the 4 XML nodes into a map to get the format I want but I haven't had any success here either.
I'm guessing the issue is with the format of the file – Presumably BizTalk can't differentiate between the POHeader and POLine sections because it is all delimited by TABs and so has no clearly defined break. I'm pretty sure that there must be away to get the format I want out of the flat file schema wizard (or by amending the flat file schema it generates afterwards). Once I've got that I'm guessing I can then set the Envelope property to True to allow the pipeline to debatch the individual Purchase Orders?
-
Unfortunately, this will not be as easy as you might imagine.
The Flat File Disassembler will not create a hierarchical structure from a flat structure.
You will need to create a Flat file schema that has the same structure as the your data
i.e. multiple records containing every field.You can then use a map (custom Xslt using Muenchian grouping) to group into a hierarchical document structure.
You can then pass thru a pipeline to debatch the documents ( the output schema for the map will need to be an envelope/document schema) and this means sending the file out a send port and receiving in another receive port.
Alternatively, you can debatch the the output of the map inside an orchestration.
This would all be easier if the output from SQL was already in a hierarchical document structure. Do you have the ability to change the extract mechanism from SQL or do you have to work with the current output.
-
Unfortunately I am stuck with the output from the DTS package as the file is produced by a third party.
I'm glad this isn't as easy as it sounds. This is my first BizTalk solution and I was coming to the conclusion that I must have been missing something really basic! All the examples of flat file usage I could find had flat files with a pretty good structure already defined.
I'm not too sure about the Muenchian grouping that you mention but at least it gives me something to search for on the web. Do you know of any good examples out there that I can read through?
I read an article on someones blog the other day (I forget who's – I've been living in Blogland since starting working with BizTalk!) that explained debatching in an orchestration. However, it called a pipeline to debatch the original message using an Expression shape and explained that this needed to be in an atomic scope and so did not support recoverable interchanges. Does this mean that potentially individual lines from my PO could be lost (or at least suspended) when the document is debatched if an error occurs? Do you know of a better way of handling this in an orchestration?
By the way, to anyone reading this that is a BizTalk 2006 beginner, I would recommend "BizTalk 2006 Recipies: A Problem-Solution Approach" as a really good book to get started in BizTalk – after a basic training course!
-
For Muenchian grouping examples, just google Biztalk and Muenchian.
Recoverable processing is a mechanism for processing a batch of messages and only failing the bad ones.
On Biztalk 2004, if you had a batch of 100 messages, one of which was bad – all 100 would fail.
With Biztalk 2006, you can turn on recoverable interchange processing so the 99 good messages are processed and the one bad one is suspended.
This is optional, it may not be what you want to happen. If for example the sending system is unable to handle individual failures and must reprocess the entire batch, then you would not want to use recoverable interchange processing
-
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.