|
Home > Archive > BizTalk Server Applications Integration > July 2004 > SQL Adapter for Hierarchical Inserts
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
SQL Adapter for Hierarchical Inserts
|
|
|
| Is there any documentation or guidance out there for using the SQL Adapter to do heirarchical inserts?
I would like to setup a business process very similar to the following:
1. Receive a EDI purchase order
2. Transform EDI Customer data and use SQL Adapter to insert it into Northwind.Customers table via sproc. SQL Adapter would also retrieve new customer ID.
3. Send newly retrieved Customer ID to step 4.
4. Transform EDI Order data and use SQL Adapter to insert it into Northwind.Orders table via sproc. SQL Adapter would need not only the Order data in the PO, but also the Customer ID retrieved in step 2. SQL Adapter would also retrieve new Order ID.
5. Send newly retrieved Order ID to step 6.
6. Transform EDI Order Details data and use SQL Adapter to insert it into Northwind.Order Details table via sproc. SQL Adapter would need not only the Order Details data in the PO, but also the Order ID retrieved in step 4.
In 2002, the SQL AIC did not allow for return values, so I did this type of insert without autoincrement identity columns, just using certain fields in the EDI document to create primary keys, and carrying parent primary keys to child tables. By the time
I was four levels down, I had a table with a five column wide Primary Key. In 2004, I can get back an autoincremented identity key, which would certainly allow me to streamline my database design.
Also in 2002, I could construct a target schema with as many insert sproc nodes as I had tables to insert to, and map to that one schema, using a looping functoid as necessary. I am having a little difficulty conceiving of how this would would be done in
2004.
Anyway, any helpful hints or pointers to existing documentation would be greatly appreciated. I have reviewed Scott Woodgate's SQL Adapter tutorial, as well as the SDK (updated). Neither really cover the scenario I depict above. Which is strange, as I
would think it would be a very common scenario.
Should I open a support incident about how to set this up, or is that appropriate?
Thanks.
John Thayer
| |
| larry franks 2004-05-12, 5:26 pm |
| Hi John, for something like this I'd probably use an orchestration bound to
solicit/response ports. Do the send, get the response, do your transform,
do the next send, etc.
As to having multiple sproc nodes in a schema, I haven't tested this but it
should work in theory. Just copy the record element in the generated
schema, then rename the original record, paste in the new record and modify
by hand, etc. There's not a way to automatically do it with the wizard
unfortunately.
Let me know if you have questions on this.
Larry Franks
This posting is provided "AS IS" with no warranties,and confers no rights.
Subscribe at
http://support.microsoft.com/defaul...msdn/nospam.asp
&SD=msdn
--------------------
| Thread-Topic: SQL Adapter for Hierarchical Inserts
| thread-index: AcQ4OaVyHik6BSRCQGiykpACsqQfZA==
| X-WN-Post: microsoft.public.biztalk.appintegration
| From: "=?Utf-8?B?SlQ=?=" <JTnospam@verizon.net>
| Subject: SQL Adapter for Hierarchical Inserts
| Date: Wed, 12 May 2004 08:56:11 -0700
| Lines: 27
| Message-ID: <C9D3424D-26B6-4A8D-91F6-77DB56C9B2C2@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.biztalk.appintegration
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.biztalk.appintegration:6669
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.biztalk.appintegration
|
| Is there any documentation or guidance out there for using the SQL
Adapter to do heirarchical inserts?
I would like to setup a business process very similar to the following:
1. Receive a EDI purchase order
2. Transform EDI Customer data and use SQL Adapter to insert it into
Northwind.Customers table via sproc. SQL Adapter would also retrieve new
customer ID.
3. Send newly retrieved Customer ID to step 4.
4. Transform EDI Order data and use SQL Adapter to insert it into
Northwind.Orders table via sproc. SQL Adapter would need not only the
Order data in the PO, but also the Customer ID retrieved in step 2. SQL
Adapter would also retrieve new Order ID.
5. Send newly retrieved Order ID to step 6.
6. Transform EDI Order Details data and use SQL Adapter to insert it into
Northwind.Order Details table via sproc. SQL Adapter would need not only
the Order Details data in the PO, but also the Order ID retrieved in step 4.
In 2002, the SQL AIC did not allow for return values, so I did this type of
insert without autoincrement identity columns, just using certain fields in
the EDI document to create primary keys, and carrying parent primary keys
to child tables. By the time I was four levels down, I had a table with a
five column wide Primary Key. In 2004, I can get back an autoincremented
identity key, which would certainly allow me to streamline my database
design.
Also in 2002, I could construct a target schema with as many insert sproc
nodes as I had tables to insert to, and map to that one schema, using a
looping functoid as necessary. I am having a little difficulty conceiving
of how this would would be done in 2004.
Anyway, any helpful hints or pointers to existing documentation would be
greatly appreciated. I have reviewed Scott Woodgate's SQL Adapter
tutorial, as well as the SDK (updated). Neither really cover the scenario
I depict above. Which is strange, as I would think it would be a very
common scenario.
Should I open a support incident about how to set this up, or is that
appropriate?
Thanks.
John Thayer
|
| |
|
| Thanks Larry. Do you know if there is an example of this somewhere?
John
| |
| larry franks 2004-05-13, 12:00 pm |
| There's not one exactly like this (that is, one insert/response, transform,
another insert/response) but in the help file under Operations->Connecting
Applications Using Native Adapters->SQL Adapter->Using the SQL
Adapter->Using the SQL Adapter with a Stored Proceedure it walks through
creating a schema that has an insert using a sproc that receives a result
and also an orchestration that demonstrates using this.
Let me know if you have questions on this example.
Larry Franks
This posting is provided "AS IS" with no warranties,and confers no rights.
Subscribe at
http://support.microsoft.com/defaul...msdn/nospam.asp
&SD=msdn
--------------------
| Thread-Topic: SQL Adapter for Hierarchical Inserts
| thread-index: AcQ4XfevxOhmSQG4T7eTfn2k1AEwQQ==
| X-WN-Post: microsoft.public.biztalk.appintegration
| From: "=?Utf-8?B?SlQ=?=" <anonymous@discussions.microsoft.com>
| References: <C9D3424D-26B6-4A8D-91F6-77DB56C9B2C2@microsoft.com>
<MqvJrwFOEHA.3960@cpmsftngxa10.phx.gbl>
| Subject: RE: SQL Adapter for Hierarchical Inserts
| Date: Wed, 12 May 2004 13:16:11 -0700
| Lines: 2
| Message-ID: <4F8946D3-D344-4945-84E6-0F2240652155@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.biztalk.appintegration
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.biztalk.appintegration:6674
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.biztalk.appintegration
|
| Thanks Larry. Do you know if there is an example of this somewhere?
John
|
| |
|
| Hi Larry,
I have tried pasting a couple of generated adapter schemas together. The question arises, how to merge the request Roots. For example, each schema generates a section like the following:
<xs:annotation><xs:appinfo><msbtssql:sqlScript value="exec [procCustIns] @CompName=NULL, @CustID="UPTON"" xmlns:msbtssql="http://schemas.microsoft.com/BizTalk/2003" /></xs:appinfo></xs:annotation>
This is then followed by the request Root:
<xs:element name="reqRoot"><xs:complexType><xs:sequence><xs:element name="procCustIns"><xs:complexType><xs:attribute name="CompName" type="xs:string" /><xs:attribute name="CustID" type="xs:string" /></xs:complexType></xs:element></xs:sequence></xs:complex
Type></xs:element>
My attempt at pasting these together resulted in:
<?xml version="1.0"?><xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="http://nestedSqlTest" version="1.0" xmlns:xs="http://www.w3.org/2001/XMLSchema"><xs:annotation><xs:appinfo><msbtssql:sqlScript value="exec [
procCustIns] @CompName=NULL, @CustID="UPTON"" xmlns:msbtssql="http://schemas.microsoft.com/BizTalk/2003" /></xs:appinfo></xs:annotation><xs:element name="reqRoot"><xs:complexType><xs:sequence><xs:element name="procCustIns"><xs:complexType><xs:at
tribute name="CompName" type="xs:string" /><xs:attribute name="CustID" type="xs:string" /></xs:complexType></xs:element></xs:sequence></xs:complexType></xs:element><xs:element name="respRoot"><xs:complexType><xs:sequence><xs:element name="Success" type="x
s:anyType" /></xs:sequence></xs:complexType></xs:element><xs:annotation><xs:appinfo><msbtssql:sqlScript value="exec [procOrdersIns] @CustID=NULL, @ShipCountry=NULL" xmlns:msbtssql="http://schemas.microsoft.com/BizTalk/2003" /></xs:appinfo></xs:annotation>
<xs:element name="reqRootOrders"><xs:complexType><xs:sequence><xs:element name="procOrdersIns"><xs:complexType><xs:attribute name="CustID" type="xs:string" /><xs:attribute name="ShipCountry" type="xs:string" /></xs:complexType></xs:element></xs:sequence><
/xs:complexType></xs:element></xs:schema>
When I went to put this schema in a map as the target, I get asked which root element to use as the map target. This obviously defeats the purpose of the nested sprocs. Do you understand what I mean? Any more details guidance you could give on how to:
"As to having multiple sproc nodes in a schema, I haven't tested this but it
should work in theory. Just copy the record element in the generated
schema, then rename the original record, paste in the new record and modify
by hand, etc. "
Thanks,
John
| |
| larry franks 2004-05-17, 11:41 am |
| I'll see if I can get a sample working and post it.
Larry Franks
This posting is provided "AS IS" with no warranties,and confers no rights.
Subscribe at
http://support.microsoft.com/defaul...msdn/nospam.asp
&SD=msdn
--------------------
| Thread-Topic: SQL Adapter for Hierarchical Inserts
| thread-index: AcQ5LWd8ouxv5/abTVm71G19ji8Oow==
| X-WN-Post: microsoft.public.biztalk.appintegration
| From: "=?Utf-8?B?SlQ=?=" <JTnospam@verizon.net>
| References: <C9D3424D-26B6-4A8D-91F6-77DB56C9B2C2@microsoft.com>
<MqvJrwFOEHA.3960@cpmsftngxa10.phx.gbl>
| Subject: RE: SQL Adapter for Hierarchical Inserts
| Date: Thu, 13 May 2004 14:01:04 -0700
| Lines: 25
| Message-ID: <581CAF56-B073-4DB7-A891-C4F6F5E88679@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.biztalk.appintegration
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.biztalk.appintegration:6679
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.biztalk.appintegration
|
| Hi Larry,
I have tried pasting a couple of generated adapter schemas together. The
question arises, how to merge the request Roots. For example, each schema
generates a section like the following:
<xs:annotation><xs:appinfo><msbtssql:sqlScript value="exec [procCustIns]
@CompName=NULL, @CustID="UPTON""
xmlns:msbtssql="http://schemas.microsoft.com/BizTalk/2003"
/></xs:appinfo></xs:annotation>
This is then followed by the request Root:
<xs:element name="reqRoot"><xs:complexType><xs:sequence><xs:element
name="procCustIns"><xs:complexType><xs:attribute name="CompName"
type="xs:string" /><xs:attribute name="CustID" type="xs:string"
/></xs:complexType></xs:element></xs:sequence></xs:complexType></xs:element>
My attempt at pasting these together resulted in:
<?xml version="1.0"?><xs:schema attributeFormDefault="unqualified"
elementFormDefault="qualified" targetNamespace="http://nestedSqlTest"
version="1.0"
xmlns:xs="http://www.w3.org/2001/XMLSchema"><xs:annotation><xs:appinfo><msbt
ssql:sqlScript value="exec [procCustIns] @CompName=NULL,
@CustID="UPTON""
xmlns:msbtssql="http://schemas.microsoft.com/BizTalk/2003"
/></xs:appinfo></xs:annotation><xs:element
name="reqRoot"><xs:complexType><xs:sequence><xs:element
name="procCustIns"><xs:complexType><xs:attribute name="CompName"
type="xs:string" /><xs:attribute name="CustID" type="xs:string"
/></xs:complexType></xs:element></xs:sequence></xs:complexType></xs:element>
<xs:element name="respRoot"><xs:complexType><xs:sequence><xs:element
name="Success" type="xs:anyType"
/></xs:sequence></xs:complexType></xs:element><xs:annotation><xs:appinfo><ms
btssql:sqlScript value="exec [procOrdersIns] @CustID=NULL,
@ShipCountry=NULL"
xmlns:msbtssql="http://schemas.microsoft.com/BizTalk/2003"
/></xs:appinfo></xs:annotation><xs:element
name="reqRootOrders"><xs:complexType><xs:sequence><xs:element
name="procOrdersIns"><xs:complexType><xs:attribute name="CustID"
type="xs:string" /><xs:attribute name="ShipCountry" type="xs:string"
/></xs:complexType></xs:element></xs:sequence></xs:complexType></xs:element>
</xs:schema>
When I went to put this schema in a map as the target, I get asked which
root element to use as the map target. This obviously defeats the purpose
of the nested sprocs. Do you understand what I mean? Any more details
guidance you could give on how to:
"As to having multiple sproc nodes in a schema, I haven't tested this but
it
should work in theory. Just copy the record element in the generated
schema, then rename the original record, paste in the new record and modify
by hand, etc. "
Thanks,
John
|
| |
|
| That would be simply fantastic! Thanks Larry.
John
| |
|
| Hi Larry,
Were you able to look at this at issue? What would be great would be the kind of documentation and example that accompanied the old BizTalk Adapter for SQL Server Tutorial.
JT
| |
| larry franks 2004-05-25, 4:37 pm |
| Sorry for not posting sooner JT, was caught up in a few cases here.
I filed a request for comment to the product group and the reply is that
this functionality is not in the SQL adapter in BizTalk 2004. I've pointed
out that this is a loss of functionality from 2002 and that we will need to
do a KB on this.
The only suggestion I have had on a workaround at this point is to have the
inbound map look like the old SQL adapter, then send it into an
orchestration. In the orchestration have parallel calls into maps, each
map maps a section of the inbound document to one of the BizTalk 2004 SQL
schemas, then that's sent on to the outbound port.
I'll try to setup a sample of this today or tomorrow morning.
Larry Franks
This posting is provided "AS IS" with no warranties,and confers no rights.
Subscribe at
http://support.microsoft.com/defaul...msdn/nospam.asp
&SD=msdn
--------------------
| Thread-Topic: SQL Adapter for Hierarchical Inserts
| thread-index: AcRBligG+619kVheRw6KtcT+tKkX1w==
| X-WN-Post: microsoft.public.biztalk.appintegration
| From: "=?Utf-8?B?SlQ=?=" <JTnospam@verizon.net>
| References: <C9D3424D-26B6-4A8D-91F6-77DB56C9B2C2@microsoft.com>
<MqvJrwFOEHA.3960@cpmsftngxa10.phx.gbl>
<581CAF56-B073-4DB7-A891-C4F6F5E88679@microsoft.com>
<OlitWICPEHA.424@cpmsftngxa10.phx.gbl>
| Subject: RE: SQL Adapter for Hierarchical Inserts
| Date: Mon, 24 May 2004 06:51:04 -0700
| Lines: 3
| Message-ID: <0FB624A1-DDC1-4733-9AE9-5CBC29A9DA1B@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.biztalk.appintegration
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.biztalk.appintegration:6718
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.biztalk.appintegration
|
| Hi Larry,
Were you able to look at this at issue? What would be great would be the
kind of documentation and example that accompanied the old BizTalk Adapter
for SQL Server Tutorial.
JT
|
| |
|
| I am a little disheartened by your news, but think I had already figured it out. My vote....
The SQL Adapter should be modelled after the ADO.NET SQL DataAdapter. You should be able to configure it's connection string; assign it select, update, insert, and delete sprocs or scripts; retrieve return_values from it; and structure the use of several
such adapters to insert to a parent table, return the insert's PK value, insert to a child table using the parent PK for dri, then loop back to the next parent table insert. Maybe BTS developers should talk to the VS team? Or maybe I have no idea what
I am talking about and all of this is easy and possible now. Anyway, I extremely eagerly await your post.
| |
| larry franks 2004-05-30, 10:46 am |
| So to summarize this issue:
In the SQL adapter for BizTalk 2002, you could create a schema with
multiple sprocXX records that would send data to multiple stored
procedures, all in one file.
In the SQL adapter for BizTalk 2004, you cannot do this. You can only hit
one sproc per schema. This is by design, it was a design change from the
old version. I do not have specifics on why this decision was made.
I have been able to workaround this design change by using:
a schema that contains multiple sproc records
a specific generated schema for each sproc we are calling
map(s) to map data from the individual sproc records in the combined sproc
schema to the schemas that are submitted to sql
an orchestration to orchestrate the flow of this
I'm including the zip of this project here. Note that you could do
something similar just using receive/send ports with maps and port filters.
But, if you want to do something where you call one sproc, then the other
in sequence then you would want to use an orchestration to ensure the order
of calling into SQL.
Please let me know if you have questions on this. Also JT, if you'd like
to file a design change request for this please contact me with your e-mail
and phone information and I'll create a grace case and file for a design
change request.
Larry Franks
This posting is provided "AS IS" with no warranties,and confers no rights.
Subscribe at
http://support.microsoft.com/defaul...msdn/nospam.asp
&SD=msdn
--------------------
| X-Tomcat-ID: 551982218
| References: <C9D3424D-26B6-4A8D-91F6-77DB56C9B2C2@microsoft.com>
<MqvJrwFOEHA.3960@cpmsftngxa10.phx.gbl>
<581CAF56-B073-4DB7-A891-C4F6F5E88679@microsoft.com>
<OlitWICPEHA.424@cpmsftngxa10.phx.gbl>
<0FB624A1-DDC1-4733-9AE9-5CBC29A9DA1B@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain
| Content-Transfer-Encoding: 7bit
| From: larryfr@online.microsoft.com ("larry franks")
| Organization: Microsoft
| Date: Tue, 25 May 2004 18:57:20 GMT
| Subject: RE: SQL Adapter for Hierarchical Inserts
| X-Tomcat-NG: microsoft.public.biztalk.appintegration
| Message-ID: <BNeJrnoQEHA.3664@cpmsftngxa10.phx.gbl>
| Newsgroups: microsoft.public.biztalk.appintegration
| Lines: 59
| Path: cpmsftngxa10.phx.gbl
| Xref: cpmsftngxa10.phx.gbl microsoft.public.biztalk.appintegration:6726
| NNTP-Posting-Host: tomcatimport2.phx.gbl 10.201.218.182
|
| Sorry for not posting sooner JT, was caught up in a few cases here.
|
| I filed a request for comment to the product group and the reply is that
| this functionality is not in the SQL adapter in BizTalk 2004. I've
pointed
| out that this is a loss of functionality from 2002 and that we will need
to
| do a KB on this.
|
| The only suggestion I have had on a workaround at this point is to have
the
| inbound map look like the old SQL adapter, then send it into an
| orchestration. In the orchestration have parallel calls into maps, each
| map maps a section of the inbound document to one of the BizTalk 2004 SQL
| schemas, then that's sent on to the outbound port.
| I'll try to setup a sample of this today or tomorrow morning.
|
| Larry Franks
|
| This posting is provided "AS IS" with no warranties,and confers no
rights.
| Subscribe at
|
http://support.microsoft.com/defaul...msdn/nospam.asp
| &SD=msdn
| --------------------
| | Thread-Topic: SQL Adapter for Hierarchical Inserts
| | thread-index: AcRBligG+619kVheRw6KtcT+tKkX1w==
| | X-WN-Post: microsoft.public.biztalk.appintegration
| | From: "=?Utf-8?B?SlQ=?=" <JTnospam@verizon.net>
| | References: <C9D3424D-26B6-4A8D-91F6-77DB56C9B2C2@microsoft.com>
| <MqvJrwFOEHA.3960@cpmsftngxa10.phx.gbl>
| <581CAF56-B073-4DB7-A891-C4F6F5E88679@microsoft.com>
| <OlitWICPEHA.424@cpmsftngxa10.phx.gbl>
| | Subject: RE: SQL Adapter for Hierarchical Inserts
| | Date: Mon, 24 May 2004 06:51:04 -0700
| | Lines: 3
| | Message-ID: <0FB624A1-DDC1-4733-9AE9-5CBC29A9DA1B@microsoft.com>
| | MIME-Version: 1.0
| | Content-Type: text/plain;
| | charset="Utf-8"
| | Content-Transfer-Encoding: 7bit
| | X-Newsreader: Microsoft CDO for Windows 2000
| | Content-Class: urn:content-classes:message
| | Importance: normal
| | Priority: normal
| | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| | Newsgroups: microsoft.public.biztalk.appintegration
| | Path: cpmsftngxa10.phx.gbl
| | Xref: cpmsftngxa10.phx.gbl microsoft.public.biztalk.appintegration:6718
| | NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| | X-Tomcat-NG: microsoft.public.biztalk.appintegration
| |
| | Hi Larry,
| Were you able to look at this at issue? What would be great would be the
| kind of documentation and example that accompanied the old BizTalk
Adapter
| for SQL Server Tutorial.
| JT
| |
|
|
| |
|
| Larry:
Have you been able to post an example yet? I'd sure like to see it as well.
Mark
| |
| JTnospam@verizon.net 2004-06-29, 5:51 pm |
| Hi Larry,
Any progress on this one?
JT
""larry franks"" wrote:
> So to summarize this issue:
> In the SQL adapter for BizTalk 2002, you could create a schema with
> multiple sprocXX records that would send data to multiple stored
> procedures, all in one file.
> In the SQL adapter for BizTalk 2004, you cannot do this. You can only hit
> one sproc per schema. This is by design, it was a design change from the
> old version. I do not have specifics on why this decision was made.
>
> I have been able to workaround this design change by using:
> a schema that contains multiple sproc records
> a specific generated schema for each sproc we are calling
> map(s) to map data from the individual sproc records in the combined sproc
> schema to the schemas that are submitted to sql
> an orchestration to orchestrate the flow of this
>
> I'm including the zip of this project here. Note that you could do
> something similar just using receive/send ports with maps and port filters.
> But, if you want to do something where you call one sproc, then the other
> in sequence then you would want to use an orchestration to ensure the order
> of calling into SQL.
>
> Please let me know if you have questions on this. Also JT, if you'd like
> to file a design change request for this please contact me with your e-mail
> and phone information and I'll create a grace case and file for a design
> change request.
>
> Larry Franks
>
> This posting is provided "AS IS" with no warranties,and confers no rights.
> Subscribe at
> http://support.microsoft.com/defaul...msdn/nospam.asp
> &SD=msdn
> --------------------
> | X-Tomcat-ID: 551982218
> | References: <C9D3424D-26B6-4A8D-91F6-77DB56C9B2C2@microsoft.com>
> <MqvJrwFOEHA.3960@cpmsftngxa10.phx.gbl>
> <581CAF56-B073-4DB7-A891-C4F6F5E88679@microsoft.com>
> <OlitWICPEHA.424@cpmsftngxa10.phx.gbl>
> <0FB624A1-DDC1-4733-9AE9-5CBC29A9DA1B@microsoft.com>
> | MIME-Version: 1.0
> | Content-Type: text/plain
> | Content-Transfer-Encoding: 7bit
> | From: larryfr@online.microsoft.com ("larry franks")
> | Organization: Microsoft
> | Date: Tue, 25 May 2004 18:57:20 GMT
> | Subject: RE: SQL Adapter for Hierarchical Inserts
> | X-Tomcat-NG: microsoft.public.biztalk.appintegration
> | Message-ID: <BNeJrnoQEHA.3664@cpmsftngxa10.phx.gbl>
> | Newsgroups: microsoft.public.biztalk.appintegration
> | Lines: 59
> | Path: cpmsftngxa10.phx.gbl
> | Xref: cpmsftngxa10.phx.gbl microsoft.public.biztalk.appintegration:6726
> | NNTP-Posting-Host: tomcatimport2.phx.gbl 10.201.218.182
> |
> | Sorry for not posting sooner JT, was caught up in a few cases here.
> |
> | I filed a request for comment to the product group and the reply is that
> | this functionality is not in the SQL adapter in BizTalk 2004. I've
> pointed
> | out that this is a loss of functionality from 2002 and that we will need
> to
> | do a KB on this.
> |
> | The only suggestion I have had on a workaround at this point is to have
> the
> | inbound map look like the old SQL adapter, then send it into an
> | orchestration. In the orchestration have parallel calls into maps, each
> | map maps a section of the inbound document to one of the BizTalk 2004 SQL
> | schemas, then that's sent on to the outbound port.
> | I'll try to setup a sample of this today or tomorrow morning.
> |
> | Larry Franks
> |
> | This posting is provided "AS IS" with no warranties,and confers no
> rights.
> | Subscribe at
> |
> http://support.microsoft.com/defaul...msdn/nospam.asp
> | &SD=msdn
> | --------------------
> | | Thread-Topic: SQL Adapter for Hierarchical Inserts
> | | thread-index: AcRBligG+619kVheRw6KtcT+tKkX1w==
> | | X-WN-Post: microsoft.public.biztalk.appintegration
> | | From: "=?Utf-8?B?SlQ=?=" <JTnospam@verizon.net>
> | | References: <C9D3424D-26B6-4A8D-91F6-77DB56C9B2C2@microsoft.com>
> | <MqvJrwFOEHA.3960@cpmsftngxa10.phx.gbl>
> | <581CAF56-B073-4DB7-A891-C4F6F5E88679@microsoft.com>
> | <OlitWICPEHA.424@cpmsftngxa10.phx.gbl>
> | | Subject: RE: SQL Adapter for Hierarchical Inserts
> | | Date: Mon, 24 May 2004 06:51:04 -0700
> | | Lines: 3
> | | Message-ID: <0FB624A1-DDC1-4733-9AE9-5CBC29A9DA1B@microsoft.com>
> | | MIME-Version: 1.0
> | | Content-Type: text/plain;
> | | charset="Utf-8"
> | | Content-Transfer-Encoding: 7bit
> | | X-Newsreader: Microsoft CDO for Windows 2000
> | | Content-Class: urn:content-classes:message
> | | Importance: normal
> | | Priority: normal
> | | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
> | | Newsgroups: microsoft.public.biztalk.appintegration
> | | Path: cpmsftngxa10.phx.gbl
> | | Xref: cpmsftngxa10.phx.gbl microsoft.public.biztalk.appintegration:6718
> | | NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
> | | X-Tomcat-NG: microsoft.public.biztalk.appintegration
> | |
> | | Hi Larry,
> | Were you able to look at this at issue? What would be great would be the
> | kind of documentation and example that accompanied the old BizTalk
> Adapter
> | for SQL Server Tutorial.
> | JT
> | |
> |
> |
| |
|
| I don't know what JT's final solution was, but Larry and I worked on this one for a while. I finally decided to use OpenXML within a stored procedure. Works like a champ! I don't think you'd want to push huge amounts of XML through using this technique
, but it's working well for me. We also came up with a workable solution of calling the sproc (that uses OpenXML to insert the XML into the SQL table(s)) from an Orchestration.
I'd be happy to share the orchestration and related code to anyone who needs it.
I think Larry (from Microsoft) is working up a KB article on this... or a related approach.
| |
| JTnospam@verizon.net 2004-06-30, 5:55 pm |
| Hi Mark,
I am not sure if this approach would help me, as I'd be afraid of busting buffers with large XML documents. However, could I please get a look at what you and Larry came up with? Thanks
JT
"MarkS" wrote:
> I don't know what JT's final solution was, but Larry and I worked on this one for a while. I finally decided to use OpenXML within a stored procedure. Works like a champ! I don't think you'd want to push huge amounts of XML through using this techniq
ue, but it's working well for me. We also came up with a workable solution of calling the sproc (that uses OpenXML to insert the XML into the SQL table(s)) from an Orchestration.
>
> I'd be happy to share the orchestration and related code to anyone who needs it.
>
> I think Larry (from Microsoft) is working up a KB article on this... or a related approach.
>
| |
| larry franks 2004-07-01, 7:47 am |
| Sorry for the late followup on this guys, we were able to get the SQL
adapter in 2004 to function at least like the old one, where we can do
multiple inserts/sproc calls from one document.
In Mark's case we needed to do relational inserts using one updategram.
Where the secondary inserts needed to use the primary key of the main
insert. Unfortunately there's not a straightforward way to do this with
the SQL adapter and Mark came up with a way to accomplish this using
OpenXML. The only downside to this is that this moves all the XML
processing to the serverside so it may not be desirable for some
implementations.
I'll see if I can package up some examples today, though due to several
people being out of office for the holidays it may be next week before I
can get everything up here.
Larry Franks
This posting is provided "AS IS" with no warranties,and confers no rights.
Subscribe at
http://support.microsoft.com/defaul...msdn/nospam.asp
&SD=msdn
--------------------
| Thread-Topic: SQL Adapter for Hierarchical Inserts
| thread-index: AcRe4C57dzec12TWQxirLurCCnTRFA==
| X-WBNR-Posting-Host: 141.154.190.62
| From: "=?Utf-8?B?SlRub3NwYW1AdmVyaXpvbi5uZXQ=?="
<JTnospamverizonnet@discussions.microsoft.com>
| References: <C9D3424D-26B6-4A8D-91F6-77DB56C9B2C2@microsoft.com>
<122878D7-E690-4B1E-BAD7-472D8F32C185@microsoft.com>
| Subject: RE: SQL Adapter for Hierarchical Inserts
| Date: Wed, 30 Jun 2004 13:24:01 -0700
| Lines: 12
| Message-ID: <763E5EAA-06CA-49BF-BC29-5D390214E0BC@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.biztalk.appintegration
| NNTP-Posting-Host: TK2MSFTNGXA03.phx.gbl 127.0.0.1
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGXA01.phx.gbl!TK2MSFTNGXA03.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.biztalk.appintegration:6871
| X-Tomcat-NG: microsoft.public.biztalk.appintegration
|
| Hi Mark,
| I am not sure if this approach would help me, as I'd be afraid of busting
buffers with large XML documents. However, could I please get a look at
what you and Larry came up with? Thanks
| JT
|
| "MarkS" wrote:
|
| > I don't know what JT's final solution was, but Larry and I worked on
this one for a while. I finally decided to use OpenXML within a stored
procedure. Works like a champ! I don't think you'd want to push huge
amounts of XML through using this technique, but it's working well for me.
We also came up with a workable solution of calling the sproc (that uses
OpenXML to insert the XML into the SQL table(s)) from an Orchestration.
| >
| > I'd be happy to share the orchestration and related code to anyone who
needs it.
| >
| > I think Larry (from Microsoft) is working up a KB article on this... or
a related approach.
| >
|
| |
|
| JT:
I wrote you an email (offline from the newsgroup) to make sure I had the correct email address for you, but never heard back - therefore I assume I don't have your email address.
I can be reached here (removing the CAP letters in the address):
NOSPAMmark@SPAMFREEmarkscott.net
I'd be happy to send you my project...
Take care,
Mark Scott
"JTnospam@verizon.net" wrote:
[vbcol=seagreen]
> Hi Mark,
> I am not sure if this approach would help me, as I'd be afraid of busting buffers with large XML documents. However, could I please get a look at what you and Larry came up with? Thanks
> JT
>
> "MarkS" wrote:
>
ique, but it's working well for me. We also came up with a workable solution of calling the sproc (that uses OpenXML to insert the XML into the SQL table(s)) from an Orchestration.[vbcol=seagreen]
|
|
|
|
|