| Tim B. 2005-12-30, 8:49 pm |
| Never mind. I finally found a post from Larry F, from 2004. That was what I
was looking for.
in case anyone else needs it here it is
..........................
The following is a step by step guide on creating a receive port that
will pull data from the employees table in the NorthWind database and split
the inbound data into one document per record returned.
Create document schema:
1: Open Visual Studio and create a new BizTalk project named
NorthWindSplit
2: Right click on the project in Solution Explorer and select Add,
then Add Generated Items. In the Generated Item wizard highilght Add Adapter
and click Open.
3: Select the SQL entry, make sure that we are pointing to the SQL
server that contains the BizTalk message box database and that the message
box database is selected. Click next.
4: Click the Set button and enter the information to connect to the
SQL server that houses the NorthWind database. Once you have entered the
information click next.
5: For the target namespace enter http://NorthwindEmpSplit, leave the
port set to receive and for the root element enter EmpRoot. Click next.
6: On this page select to use a select statement and click next. For
the select statement enter:
select * from employees for xml auto click next. Then click finish.
You should have a SQLService.xsd file added to the project at this
point.
7: Open the sqlservice.xsd file and select the employees element. In
properties for this set the max occurs and min occurs to 1.
8: Move the employees record to the root level (direct child of <schema> )
and delete the EmpRoot element. Save this schema.
Create the Envelope schema:
1: Add a new schema to the project, name it EmpEnvelope.xsd.
2: Open the new schema and highlight the <schema> element. In the
properties window set the Envelope property to yes. Set the target
namespace to NorthwindEmpSplit. This must match the target namespace
for the SQLService.xsd file.
3: Rename the root element to EmpRoot. In properties for EmpRoot
select the Body XPath property and click the ... button. Select the EmpRoot
element. This should set the Body XPath to /*[local-name()='EmpRoot'
and namespace-uri()='http://NorthwindEmpSplit'
4: Right click the EmpRoot element and insert a schema node, Any
element. This should create a child element named <Any>
5: Select the <Any> element and in properties set the Namespace to
##any. Set the Process Contents property to Lax.
Save this schema.
Adding a custom pipeline:
1: Right click on the BizTalk project in Solution Explorer, add a new
item. For the item select a receive pipeline and name it EmpSplitPipe.btp.
2: Open the pipeline file, from the toolbox drag an xml disassembler
to the disassembler stage in the pipeline.
3: Select the disassembler you added, in the properties dialog set the
Document Schemas to the SQLService.xsd and set the Envelope Schemas to
EmpEnvelope.xsd. Save this file.
Building and deploying:
1: Create a keyfile to sign the assembly and set this in properties
for the project.
2: Build the project, then deploy.
3: Copy the dll for this assembly to the \program files\microsoft
biztalk server 2004\pipeline components folder
Configuring BizTalk:
1: In Biztalk explorer create a new receive port, under this create a
new receive location and set the transport type to SQL.
2: Select the URI entry and click the ... button. Here set the
connection string to point to the SQL server that contains the Northwind
database.
3: Select the SQL Command entry and click the ... button. In the
Project field select the assembly we deployed for our project and in the
Schema field select the SQLService schema.
4: Change the Document Root Element Name to EmpRoot. Ok out of the
SQL Transport Properties.
5: Set the receive pipeline to use the pipeline we created in our
assembly.
6: create a new send port. This should send to a file location. For
filter settings filter on BTS.ReceivePortName==<name of receive port>
7: Start the send and receive locations. The receive location should
start polling the SQL adapter, submit the data to the pipeline which should
split it into individual documents that are written out through the file
adapter.
==================================
Larry Franks
...........................
"Tim B." wrote:
> I did this about a year ago and had found a great document, but can't locate
> it anymore. I need to split an incoming message from the sqladapter.
>
> anyone know where a step by stpe guide for modifying the current schema and
> setting up an envelope is ?
>
> Tim
|