Re: Sql adapter using SP - insert parent-child with a nextnumber-k

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance



Well - I've gone for the second solution

It is indeed batched...I even do 4 things right now in 1 "batch"
- Insert into a messagetable that will be used fot the receiving process to
start processing first with a status that means 'don't process yet' (with the
messagenumber=nextnum)
- Insert into a headertable
- Insert into a linestable (several lines)
- Update the status of the line I inserted in first step , that means 'ok
you can process now'
(I've added timestamps to see the inserts actually happen in that order ,
status may not be updated if header and lines are not there yet - seems to
be working perfectly)

I have created an orchestration indeed but use two sendports....I don't mind
that if something fails in the second SP-call ,the nextnum was updated with
value +1 in the first SP.

To assign my resulting nextnum I've used a trick I often use....I create a
new xsd similar to the schema I start off with (unless you have rights to
change the original schema off course :-) it only has an extra node for the
nextnum.
I make that a distinguished field and create a map for going from original
to the extra-nextnum schema . (just remember to fill that node in the map
with empty or something else!)
Do the same with the result you get from SP1 (i needed to set the node to
unbounded...but is no problem I can and may get back only one result)

After mapping , set both distinguished fields equal to eachother and you
can use your nextnum whereever you want....

Actually the orchestraions looks quite simple , just some transforms , a
little bit of code to assign the distinguished fields and send and receive
ports....

It's my first attempt with the SQL-adapter but so far I'm liking it a lot....
Now I'll try implementing it on the real database/tables in stead of my
small POC :-)


Isabelle


"Dan Rosanova" wrote:

I just mean batched as in all the commands (for header and children)
will be sent in one request which it sounds like you may already be
doing (which impresses me quite a bit because that’s a very good
practice). This will mean there is one trip to the Message Box rather
than one per line item plus one for the header.

Unfortunately I do think you will need to use an orchestration since
you want to call that SP, although there is a database lookup functoid
for maps, but I have never used it (if I get time I might give it a
look though).

Now to assign the whole XML to a string (be sure to use NTEXT or
NVARCHAR(MAX) or something as your SP parameter) you have to get a
little ugly. You probably need to do the old “assign to XmlDocument
and use .OuterXml. This is not my preference and I thought there was
another way to do this, but I haven’t taken the time to find (or
rediscover) it.

As for the second solution you look like you’re on the right track
there and as for your step 3 I am really glad to see you also don’t
like this map in the orchestration. If you want you can create it
there and then move it to the other project in your solution. I have
done that, but then it gets tough to edit them. Alternatively I’ve
also created a fake orchestration in my Maps project, created the map,
then deleted the orchestration. Still a maintenance problem, but
keeps the separation of concerns. Generally I sort of live with it
because that is a map used internally in the orchestration and not on
a port.

As for the single SQL Port, you don’t have to, but here is my blog
entry on doing that. It is not quite complete so please forgive the
messiness; I’m just moving my blog now. It is located here:
http://www.novaenterprisesystems.com/blog for now. I use the SQL
Adapter a lot and find it took me a long time to learn a lot of its
intricacies.

Kind Regards,
-Dan Rosanova

P.S. I’d like to chat sometime about solution structure with you (or
anyone else who reads this). I’m trying to compile some best
practices.

.



Relevant Pages

  • Re: one file to multiple records in map
    ... "Tatyana Yakushev " wrote: ... the map does separate the data ... >>orchestration though and pull out data from the 'product node', ... The xml shows multiple records within ...
    (microsoft.public.biztalk.general)
  • RE: Passing parameters to SPs (SQL Adapter)
    ... I understand the initialisation aspect. ... The orchestration basically gets some data from SQL server and ... This is why i dont have any message that i could map to the SP schema. ... There are many posts ...
    (microsoft.public.biztalk.general)
  • Re: Map in XML Receive Pipeline or within orchestration.....
    ... Map in XML Receive Pipeline or within orchestration..... ... I am using a Orchestration exposed as web service. ...
    (microsoft.public.biztalk.general)
  • Re: Output multiple messages from single message
    ... I figured that a map can't ... output more than a single message - although I understand that within ... orchestration to do this. ... array variable. ...
    (microsoft.public.biztalk.general)