none
Looping Challenge RRS feed

  • Question

  • Ok guys, I have posted this before and still not received an answer that works. So this is a challenge to everyone to tell me what is wrong with my schema/transform.

    The facts:

    I have a file coming in I need to treat as a flat file. There are a total of 6 fields in this file I need to capture, plus a 7th that I get from the context of the file. If this was a simple single transaction per file, I would have it nailed. The values are pushed to a stored procedure that uses them to create a SQL table insert statement in a row specified by a lookup in the stored proc that makes use of a couple of the values.

    Here is the trick:  In some cases, 3 fields (AK202, IK501 & sometimes IK502) repeat. When they do, I need the values to come over with the others (ISA09, ISA10 & ISA11) plus the context-generated filename so I can use the values to build a new SQL insert. If there are 3 sets of these values (AK202, IK501 & sometimes IK502) I need to create 3 separate loops through the file to create and execute 3 separate insert statements. An example of the data when it repeats (normalized in a segment-per-line view for easier viewing) is as follows:

     

    ISA*00*          *00*          *ZZ*CLAIMSCH       *ZZ*MCK130217390968*130217*2247*U*00501*123132999*0*P*:~
    TA1*000390968*130217*2241*A*000~
    GS*FA*ECGCLAIMS*P391810*20130217*2247*000000001*X*005010X231A1~
    ST*999*0001*005010X231A1~
    AK1*HC*3909681*005010X222A1~
    AK2*837*390968001*005010X222A1~
    IK5*R*1~
    AK2*837*390968002*005010X222A1~
    IK5*R*9~
    AK9*A*000002*000002*000002~
    SE*0000000008*0001~
    GE*000001*000000001~
    IEA*00001*123132999~

     

    So my challenge, seeing the transform I have posted above, is to tell me how I should set this thing up so when there are multiple AK2 and IK5 segments the process loops through and re-grabs the other values in the ISA so we can create the complete insert statements for the stored proc.

     I can provide a picture once this site lets me do it (?!)

    Thanks for your attention in this--

     

    Tom

    Monday, September 30, 2013 8:56 PM

All replies

  • So, I guess the first question.  This looks like a standard 999.  Why do you have to treat it as a flat file?

    You can receive it as EDI and parse it into the 999 xml format, then do some additional processing in an Orchestration. 

    If you use a Composite Operation for the SQL update, you don't have to loop or debatch.  The Composite Operation is a series of SQL Operations in one message so you can map directly from the 999 to the repeating stored procedure command. 

    I'll assume you need the incoming file name.  What you can do here is create another Orchestration Message with this and any other context information from the file.

    You would then use both these messages as input to a multi-source Map for creating the SQL composite message.

    Monday, September 30, 2013 10:10 PM
  • So, I guess the first question.  This looks like a standard 999.  Why do you have to treat it as a flat file?

    --Because the EDI setup in BizTalk requires Trading Partner data to be setup, and this file is coming in with dynamic values (different every file) where it normally looks for the trading partner identifier.

    You can receive it as EDI and parse it into the 999 xml format, then do some additional processing in an Orchestration. 

    If you use a Composite Operation for the SQL update, you don't have to loop or debatch.  The Composite Operation is a series of SQL Operations in one message so you can map directly from the 999 to the repeating stored procedure command. 

    -- Can you show me an example of this?

    I'll assume you need the incoming file name.  What you can do here is create another Orchestration Message with this and any other context information from the file.

    --I do, & I have done that in other iterations but the contextaccessor funcoid I am using pulls it in nicely.

    You would then use both these messages as input to a multi-source Map for creating the SQL composite message.


    Tuesday, October 1, 2013 4:13 PM
  • I also am supposed to try and achieve the looping without using XSLT and minimal C# code, if possible.
    • Edited by TBAN Tuesday, October 1, 2013 6:09 PM added "if possible"
    Tuesday, October 1, 2013 6:09 PM
  • Is there a reason for those restrictions?  And are you refereeing only to user generated xsl and C#?

    What I suggested above would likely require no custom code or custom xsl.

    Tuesday, October 1, 2013 6:25 PM
  • Those restrictions because I would like to implement this without the need for a programmer.

    In your initial response, you mentioned the following:

    "..The Composite Operation is a series of SQL Operations in one message so you can map directly from the 999 to the repeating stored procedure command. .."

    Have you an example of this? Or would you prefer I share with/send you my templates and maps? I am looking at a time crunch here, with an impending deadline. What I have handles single transaction files well enough, but too many are multiple transaction files.

    Tuesday, October 1, 2013 6:45 PM
  • Composite Operations are pretty simple. Here's a walk through on how to set them up.

    http://msdn.microsoft.com/en-us/library/dd788136.aspx

    Unless you're comfortable editing .btm files manually, you will have to remap to the Composite schema.

    Also, if you use a second message to inject the file name into the Map, be sure to set that up at the beginning so the Mapper can properly create the template.

    Tuesday, October 1, 2013 7:06 PM
  • Ok--so there is no way to add looping or a modification to my existing schema to make it read through the files it already handles fine when they are single transactions?

    I read through the walk through and it sounded like a lot of what I have already done. What exactly is the advantage of a composite schema?

    Tuesday, October 1, 2013 8:59 PM
  • If you're referring to the SQL Schema, that would be a Composite Operation schema.

    If you want to keep using the single operation SQL schema, then you'd have to loop somewhere else, like in an Orchestration, and make the calls one-by-one.

    Or, debatch the 999 at which ever AK/IK works for you and map at the Port.

    For clarification, are you treating the 999 as EDI (EdiDisassembler) or a flat file (FlatFileDisassembler)?

    Tuesday, October 1, 2013 9:40 PM
  • FlatFileDisAssembler.
    Tuesday, October 1, 2013 10:25 PM
  • Then your options are limited to either using the Composite Operation or looping in an Orchestration. Only the EDI Disassembler supports full sub-document breaking which is likely needed for your scenario.

    Between the two, looping in the Orchestration would be more work, for you and BizTalk, vs. mapping to a Composite Operation SQL schema.

    Tuesday, October 1, 2013 10:58 PM
  • So if I loop in the Orchestration, I will then need to add something in the Expression Editor, yes?

    Also I am unclear on how adding a loop to the orchestration automatically makes the transform grab the correct (ie, next) iterations of the AK2 & IK5 segments and adds them to the remaining, "to reuse" ISA field values.

    If I can get this down, it will become the basis for future projects I am on the hook for....

    Thanks in advance--

    Wednesday, October 2, 2013 1:57 PM
  • If I can get this down, it will become the basis for future projects I am on the hook for....

    Well...take no offense, but the direction you're heading is not a good practice, BizTalk wise.

    The Orchestration Looping solution, usually referred to as xPath Debatching, will require a lot more that an Expression shape, actually a several Expression Shapes, two xmlDocuments, a counter, intermediate Messages, a Loop...and that's just speculation.

    Versus 1 Composite Operation Schema and 1 Map.

    To note again, you'll actually have more options and less risk by beginning your process with the EDI Disassembler instead of the Flat File Disassembler.

    Wednesday, October 2, 2013 2:54 PM
  • But can I use the EDI Disassembler and still treat the file as a Flat File?

    I am all for easier, I just need to wrap my head around the Composite Operation Schema scenario...

    Wednesday, October 2, 2013 3:36 PM
  • The thing is, it's a plain text file, but it's really an EDI file.

    Both the Flat File Disassembler and EDI Disassembler convert to xml but the EDI Disassembler has the specific capabilities to handle EDI specific structures, Loops, Segments, Elements, Sub-Elements, etc.

    Additionally, the EDI disassembler has the ability to debatch while preserving the entire hierarchy.

    Finally, BizTalk already ships with a schema for the 999.  If you go the Flat File route, you will have to complete recreate the schema in order to handle all the permutations.  Everything below...AK2?...is optional.

    Give the Composite Schema a quick try, it really is as simple as referencing the single Operation.

    Wednesday, October 2, 2013 4:03 PM
  • I am well aware of the 999 schema---I have looked at it and even considered using it. However when I did immediately BizTalk wanted to utilize the trading partner part in the Administrator, something I couldn't setup/modify due to the variance in the one field that throws it off.

    I will try using the EDI Disassembler and see if it makes a difference, but I expect I would have to update the schemas in order to handle the added benefits, unless you think otherwise. Have you looked at my map at all (above up top) to be able to tell if I can use that as is with the EDI Disassembler?

    Unfortunately, I need the AK2 and IK5 segments specifically....

    Wednesday, October 2, 2013 4:11 PM
  • A) If you're receiving the 999, the trading partner should be using the same identities, just in reverse, at least that's how x12 is supposed to work.

    B) The 999 schema included with BizTalk supports the entire 999 specification so no modifications should be necessary. In fact, they would be 'out of compliance' if they did.

    Wednesday, October 2, 2013 5:16 PM
  • Ok, it appears I cannot use the EDI Disassembler without using the EDI X12 version of the schema, otherwise it fails and I get the following:

    "X12 service schema not found"

    So, is there a way to use it without using that schema?

    Wednesday, October 2, 2013 6:10 PM
  • The problem I have with the trading partner setup is this client sends us a 999 with the original filename as the ISA-08 field; usually this field is the Interchange Receiver ID. By definition, this is expected to be a static value:

    "..Identification code published by the receiver of the data; when sending, it is used by the sender as their sending ID, thus other parties sending to them will use this as a receiving ID to route data to them.."

    When the X12 schema is used, as I previously mentioned, BizTalk wants the Trading Partner data setup in the BizTalk Administrator. Since this value is expected to be static so it can be used as an identifier, the file I am receiving and processing would NEVER be recognized as the value in our files is the original filename (bold and italics below) because this value changes for every inbound 999 we receive:

    ISA*00*          *00*          *ZZ*CLAIMSCH       *ZZ*MCK130217390968*130217*2247*U*00501*123132999*0*P*:~

    So I have a chicken & the egg situation; I would LOVE to treat this as EDI and use all the canned wonder that BizTalk includes, but the files I will be processing won't comply. As such, it has forced me to the Flat File solution. I do not expect using the phrase "out of compliance" is an option for me, so I gotta make this work.

    If this makes sense, I hope you can see why I am trying to work through this as a flat file. I can get single transaction files to map in just fine--I just need the multiple transaction versions to be able to be processed as well.

    Hence, The Challenge.


    • Edited by TBAN Wednesday, October 2, 2013 6:27 PM correction
    Wednesday, October 2, 2013 6:26 PM
  • There may be a relatively easy solution to that problem.

    Review this article on how BizTalk EDI resolves inbound interchanges: http://msdn.microsoft.com/en-us/library/bb246089.aspx

    Pay attention to #2.  It describes how the EDI Disassembler can resolve on ISA05/06 (Sender ID) only.  Since in your situation, the Receiver ID is variable, you should be able to use this method.

    You would have to create a separate Agreement with the mutually agreed ISA05/06 and BT/HostX12Recvr for ISA07/08.

    Wednesday, October 2, 2013 7:00 PM
  • Rebuilding project--using an EDI version I had saved---and will try this last config. If this doesn't work I will be going the long way around the mountain.
    Wednesday, October 2, 2013 9:03 PM
  • Good luck. But, please let us help you solve these easy problems. That other route is long, dangerous and full of even worse pitfalls. 
    Wednesday, October 2, 2013 10:57 PM
  • Thanks for the heads up---I don't mind hard work if it leads to a solid solution. But it would be nice to utilize the functionality of the application to its fullest.
    Thursday, October 3, 2013 2:25 PM
  • Ok, I have
    the composite schema in place and now get 3 errors when I try to process the
    999 file:<o:p></o:p>

    First:<o:p></o:p>

    Error: 1 (Miscellaneous error)<o:p></o:p>

    6: Cannot locate document specification because multiple
    schemas matched the message type
    "http://schemas.microsoft.com/BizTalk/EDI/X12/2006#X12_00501_999". <o:p></o:p>

     <o:p></o:p>

    Next:<o:p></o:p>

    A message received by adapter "FILE" on receive
    location
    "EDI_PROCESSING_1.0.0.0_EDI_PROCESSING.BizTalk_Orchestration_999_RECEIVE_999_INBOUND_c563370af1dbbefc_ReceiveLocation"
    with URI "C:\EDI\999\InBound\*.txt" is suspended. <o:p></o:p>

    Error details: The output message of the receive pipeline
    "EDI_PROCESSING.Iv5010_999.Pipelines.ReceivePipelineEDI, EDI_PROCESSING,
    Version=1.0.0.0, Culture=neutral, PublicKeyToken=c563370af1dbbefc" failed
    routing because there is no subscribing orchestration or send port.<o:p></o:p>

    The sequence number of the suspended message is 1.  <o:p></o:p>

    MessageId:  {44870163-821A-4FFB-9659-CFE872836FDB}<o:p></o:p>

    InstanceID: {F6EEA336-757C-42DD-9A1E-5F02E81013B8}<o:p></o:p>

    Finally
    (similar to first):<o:p></o:p>

    A message received by adapter "FILE" on receive
    location
    "EDI_PROCESSING_1.0.0.0_EDI_PROCESSING.BizTalk_Orchestration_999_RECEIVE_999_INBOUND_c563370af1dbbefc_ReceiveLocation"
    with URI "C:\EDI\999\InBound\*.txt" is suspended. <o:p></o:p>

    Error details: An output message of the component
    "EDI disassembler" in receive pipeline
    "EDI_PROCESSING.Iv5010_999.Pipelines.ReceivePipelineEDI, EDI_PROCESSING,
    Version=1.0.0.0, Culture=neutral, PublicKeyToken=c563370af1dbbefc" is suspended
    due to the following error: <o:p></o:p>

         Error encountered during
    parsing. The X12 transaction set with id '' contained in functional group with
    id '000000001', in interchange with id '123132999', with sender id
    'CLAIMSCH       ', receiver id 'MCK130217390968'
    is being suspended with following errors:<o:p></o:p>

    Error: 1 (Miscellaneous error)<o:p></o:p>

    6: Cannot locate document specification because multiple
    schemas matched the message type
    "http://schemas.microsoft.com/BizTalk/EDI/X12/2006#X12_00501_999". <o:p></o:p>

    .<o:p></o:p>

    The sequence number of the suspended message is 2.  <o:p></o:p>

    MessageId:  {93C92103-1493-46D3-8436-32B20C05A35D}<o:p></o:p>

    InstanceID: {2D2F6485-DCE1-4EC7-B861-E55D069A2699}<o:p></o:p>

     <o:p></o:p>

    Now, my initial reaction was to try to tweak the
    configuration of the X12 Fallback Settings, but putting
    "HostX12Recvr" in the ISA8 value didn't quite do it. Is it as simple
    as adding that elsewhere? I initially tried modifying the TargetNamespace of
    the X12 canned BizTalk 999 Schema but it pooched things and made the
    project not compile--it threw 75 errors. <o:p></o:p>

     <o:p></o:p>

    My settings for the Fallback Settings are:<o:p></o:p>

    Ideas? Suggestions? Jumping off a cliff is not an option at this point because I live in Illinois and any "cliffs" here are non-lethal, and a lifetime of paralysis is worse than not getting this to work (but just barely).

     

    Thanks in advance as always--


    P.S. - N0F3AR = TBAN----I must have changed something on my profile.
    • Edited by N0F3AR Monday, October 21, 2013 7:17 PM correction
    Monday, October 21, 2013 7:17 PM
  • Welcome back!

    1 & 3 are the same issue.  In this case, the message is quite accurate.  You have two schemas with the same message type deployed in the Group: http://schemas.microsoft.com/BizTalk/EDI/X12/2006#X12_00501_999

    You have two options:

    1. Remove one.  That is the default Microsoft schema so you'd only need the one.

    2. In your app owned 999, change the namespace, http://My999Project for example.  In the Local Host Settings tab of the Them->You Agreement, configure this namespace for the 999 transaction.

    Fix this, then worry about #2.

    Monday, October 21, 2013 8:29 PM