Re: Help in sending Biztalk messages to SQL Server 2005



"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: problem - get the @@identity in MAP
    ... this is my updategram schema structure ... This my input schema structure(i.e Source Schema for Map) ... The adapter failed to transmit message going to send port ...
    (microsoft.public.biztalk.general)
  • Re: problem - get the @@identity in MAP
    ... this is my updategram schema structure(i.e Destination schema for Map) ... The adapter "SQL" raised an error message. ... The adapter failed to transmit message going to send port ...
    (microsoft.public.biztalk.general)
  • Re: SQL Adapter
    ... Jeff, you will need to map from your inbound XML to the SP schema, as ... the SQL adapter expects the input in its format, ... If you set the SQL port up as a request-respose, ...
    (microsoft.public.biztalk.general)
  • Re: Help in sending Biztalk messages to SQL Server 2005
    ... Use the SQL Adapter to generate an insert schema for you, then map ... After that when you deploy it will create the SQL port for you and ...
    (microsoft.public.biztalk.general)
  • Re: Filtering on a send port based on node name
    ... define a property schema with the property you want to use to route on -- ... Setup your filters on the send port to route based on this property. ... > 2) Create a map from your original schema to this new schema. ...
    (microsoft.public.biztalk.general)