I have 1 'output' schema that I want to populate by calling 2 stored
procedures.  I use the same 'variable' as the input to both SPROCS.  The
first SPROC gets some 'header' information that only occurs once in the
output schema.  Then I call a second SPROC to populate the 'detail' section
of the output schema.  The detail section can occur multiple times.

The way I tackled this was to have an orchestration call the 2 stored procs,
and apply 2 maps in the orchestration.  The 1st map applies the output from
the 1st stored proc to the 'output' schema, then the 2nd map applies the
output from the 2nd stored proc to the same 'output' schema.  As you may
have guessed, after applying the 2nd map, the results from the first map are
wiped out, and the only data in my 'output' schema are the results from the
2nd map/SPROC call.  I was only guessing that this would work, but it
didn't, so I am stuck now.

Any suggesstions?  Thanks in advance!

Brian


here is a sample of what the XML looks like...
<message>
<message-header>
<message-id>123</message-id>
<transaction-name>sales-order-submission</transaction-name>
<partner-name>midwest</partner-name>
</message-header>
<sales-order-submission>
<header>
<customer-id>12345</customer-id>
<business-name>midwest</business-name>
<customer-information>
<customer-first-name>Barbara</customer-first-name>
<customer-last-name>Isenberg</customer-last-name>
<customer-information>
<order-header>
<customer-order-number>020319-271971</customer-order-number>
<customer-order-date>20020410</customer-order-date>
</order-header>
</header>
<detail>
<line-item>
<line-no>1</line-no>
<item-code>NOK8890</item-code>
<product-name>Nokia 8890</product-name>
</line-item>
</detail>
</sales-order-submission>
</message>

[ Post a follow-up to this message ]

You probably can (and want to) create a variable that hold the input to both
sprocs.  that way you can use that variable to initialize both messages.
You can use a map, set a default value for that attribute in the message
goign to sqlserver - and then use an message assignment shape after the
transform shape to set that variable (you'll need to make that attribute an
distinguished field in both schemas to do this).


--
Jon Flanders [DevelopMentor]
http://staff.develop.com/jfland/
http://www.develop.com/courses/biztalk
"Brian C" <brian.cesafsky@midwestwireless.com> wrote in message
news:uxRtLVdHFHA.3688@TK2MSFTNGP10.phx.gbl...
>I have 1 'output' schema that I want to populate by calling 2 stored
>procedures.  I use the same 'variable' as the input to both SPROCS.  The
>first SPROC gets some 'header' information that only occurs once in the
>output schema.  Then I call a second SPROC to populate the 'detail' section
>of the output schema.  The detail section can occur multiple times.
>
> The way I tackled this was to have an orchestration call the 2 stored
> procs, and apply 2 maps in the orchestration.  The 1st map applies the
> output from the 1st stored proc to the 'output' schema, then the 2nd map
> applies the output from the 2nd stored proc to the same 'output' schema.
> As you may have guessed, after applying the 2nd map, the results from the
> first map are wiped out, and the only data in my 'output' schema are the
> results from the 2nd map/SPROC call.  I was only guessing that this would
> work, but it didn't, so I am stuck now.
>
> Any suggesstions?  Thanks in advance!
>
> Brian
>
>
> here is a sample of what the XML looks like...
> <message>
>    <message-header>
>        <message-id>123</message-id>
>        <transaction-name>sales-order-submission</transaction-name>
>        <partner-name>midwest</partner-name>
>    </message-header>
>    <sales-order-submission>
>        <header>
>            <customer-id>12345</customer-id>
>            <business-name>midwest</business-name>
>            <customer-information>
>                <customer-first-name>Barbara</customer-first-name>
>                <customer-last-name>Isenberg</customer-last-name>
>            <customer-information>
>            <order-header>
>
> <customer-order-number>020319-271971</customer-order-number>
>                <customer-order-date>20020410</customer-order-date>
>            </order-header>
>        </header>
>        <detail>
>            <line-item>
>                <line-no>1</line-no>
>                <item-code>NOK8890</item-code>
>                <product-name>Nokia 8890</product-name>
>            </line-item>
>        </detail>
>    </sales-order-submission>
> </message>
>
>

[ Post a follow-up to this message ]