Re: Help in sending Biztalk messages to SQL Server 2005

Hi Oliver,

You are absolutely right, then a stored procedure would be the way to go.
That is how I have solved that problem in the past. What I do then in the
procedure is return a varchar that tells my Orchestration what it did,
Inserted the record, Found duplicate .... you get the picture. One thing to
note when using a stored procedure in BizTalk, sort your input parameters
alphabetically, don't know the reason it just has to be that way or at least,
"had" to be that way last time I wrote a SP for Biztalk.

Happy Biz-talking,
Baldur

"Oliver" wrote:



"Dan Rosanova" wrote:

Hi Oliver,
Use the SQL Adapter to generate an insert schema for you, then map
your XML to this generated schema. I assume you already have the
table you want to put the data into.
In Visual Studio right click your project and select Add Generated
Items...
Then select Add Adapter Metadata (click Add).
Select the SQL adapter from the list
Set the connection string / settings for the SQL Adapter
Fill in some usefull namespaces and root element names hit Next
Select Updategram click Next
Select Insert and the table you want to insert to, and the columns you
want to update, click Next then Finish
This will actually add an orchestration and a schema to your solution,
you don't really need the orchestration, but you can certainly use it
to start your solution.
From here you will want to create a map from your current schema to
the new generated schema (for the request).
After that when you deploy it will create the SQL port for you and
just enable and start everything and you should be set.

As I mentioned, you may not need an orchestration at all. Supposed
you are receiving an XML file that contains your fields, you could
create a receive port & location, then create a send port with a
filter for the BTS.ReceivePortName == "whatever you called your
receive port". This will create a direct subscription. On this port
if you set the outbound map to the map you created it will all work
fine without even using orchestration.

Hope this helps.

Kind Regards,
-Dan



Hi Dan,

Thank you so much for sharing your expertise.I tried it and it works pretty
fine. I have another question.What about if I'm going to check the id of a
record if it exists already in the database before inserting the record to
avoid duplicates. Can I still used the updategram for such query or used a
stored procedure? As what I have seen in the updategram,it has only the
insert,update,and delete options.So I think the stored procedure is the right
one to use. Have you experienced this situation? Can you share your knowledge
about this?

I really appreciate your help on this.


Kind regards,

Oliver

.

Relevant Pages

  • RE: Message Subscription
    ... port to MQ use XmlDocument, just as the MQ Adapter SDK sample does. ... look in the subscription viewer and I see my orchestration with two ... > later on to a schema if you needed to. ...
    (microsoft.public.biztalk.general)
  • RE: Message Subscription
    ... Have you used the Subscription Viewer in the SDK to check and see what ... What message type did you define in your Orchestration Port? ... Note they're not our schema and we must be able to use ...
    (microsoft.public.biztalk.general)
  • Re: Subscription problem BTS 2006
    ... Do you have "Routing enabled for failed messages" set on the receive port ... Have you tried not specifying the schema on the XmlDisassembler. ... The orchestration will be subscribing to the MessageType - this is taken ... You can add additional filters to the subscription by setting filters on ...
    (microsoft.public.biztalk.general)
  • RE: Number of Ports
    ... > through CBR to a send port. ... > expected by a particular orchestration, you want that orchestration to also ... > You can make an orchestration accept messages compliant with any XSD schema ... all xml documents (received through the port ...
    (microsoft.public.biztalk.general)
  • RE: Side-by-Side Deployment Issue
    ... Then enlist and start the new orchestration version ... I moved my SQL schemas into their own assembly, ... only a specific version number of the schema. ... Start the receive port. ...
    (microsoft.public.biztalk.general)