|
Home > Archive > BizTalk Server Orchestration > March 2004 > SQL Transport in Ports
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 Transport in Ports
|
|
|
| Hi,
Can someone please point me to the docs in Biztalk 2004 re
how to use SQL transport in a port?
I want to pass a parameter into a simple select statement
(or stored proce..whichever is easier).
Eg: I want my port to execute 'select mycol from mytable
where myid = %myPassedInID%
The transport properties dialog mentions a SQL transport
wizard but I cant find it...
Thanks!
| |
|
| Check this out!
ilija
----
Using SQL Receive in an orchestration
(1)Create following stored procedure in Northwind database
create PROCEDURE SP_SelectCustomers
@Title nvarchar(30),
@Country nvarchar(15)
AS
select *
from Customers
where ContactTitle = @Title and Country = @Country
for xml auto, xmldata
GO
(2)Create an empty BizTalk Server Project
(3)Generate an XSD schema for the SQL receive.
a. Launch the SQL Adapter Schema Wizard.
Right click on the project in the Solution Explorer
window, select Add, then Add Generated Items.
Click OK on the Add Generated Item dialog box with all
default.
In Add Adapter Wizard dialog, select SQL, click Next
button. (The Server, Database and Port field can be left
if using local BizTalk database)
b. Configure Database Information
In the Database Information page of SQL Transport Schema
Generation Wizard, click Set button.
In the Data Link Properties dialog, set the Server, Log
in
and Northwind database. Test, then click OK to close the
dialog.
Click Next on the Wizard.
c. Configure Schema Information
Enter Target namespace: http://SQLReceiveSample
Select Receive port option for port type
Enter Input root element name: InRootName
Click Next on the Wizard
d. Configure Statement Information
Select Stored procedure option
From the stored procedure combo box select:
SP_SelectCustomers
Set the parameter Title: Marketing Manager
Set the parameter Country: USA
Click Generate button
Click Next on the wizard
e. Complete the wizard
Click Finish the wizard
The wizard will generate a schema file (SQLService1.xsd)
for the incoming receive documents, along with an
Orchestration file (BizTalk Orchestration1.odx)
(4)Create an orchestration
a. Drop a port on the port surface, and name it:
SQLReceivePort. Choose to Use an existing Port Type.
b. In the port direction, choose: I will always be
receiving messages on this port; In the Port binding
choose: Specify later.
c. Drop an other port on the port surface, name it:
FileSendPort. Choose to Create a new Port Type. Set Port
Type Name: FileSendPortType. It is a One-Way port.
d. In the port direction, choose: I will always be
sending messages on this port; In the Port binding
choose:
Specify later.
e. Drop a Receive shape followed by a Send shape on
the work flow surface.
f. Connect SQLReceivePort to the Receive shape, then
connect Send shape to FileSendPort. Both Receive and Send
will be processing Message_1.
g. Compile and deploy the project
(5)Create Ports and Bindings
a. Create a One-Way Receive port: ReceivePort_SQL
b. Create a Receive location for the receive port
just created: Receive_Location_SQL. Select Transport
Type:
SQL. Choose Receive Handler: BizTalkServerApplication,
and
Receive Pipeline: XMLReceive
c. Configure the Address(URI) property. This will
bring up the SQL Adapter custom property page:
Set Polling Interval 3.
Scroll down to SQL Command field, click on the . button
to
bring up the Inport information from a generated schema
dialog. Select Project: SQLAdapterSample. Select Schema:
SQLAdapterSample.SQLService1.
Click OK. The Document Root Element Name and Document
Target Namespace field will be automatically filled.
Set the Connection String field.
d. Create a Static One Way Send Port: SendPort_FILE.
Drop files to location C:\. Use PassThruTransmit Pipeline.
e. Bind the two ports to the orchestration
(6)Test the application
a. Make a small change to the stored procedure: Take
the XMLDATA predicate out. Note: The predicate XMLDATA is
only useful when you generate a schema.
create PROCEDURE SP_SelectCustomers
@Title nvarchar(30),
@Country nvarchar(15)
AS
select *
from Customers
where ContactTitle = @Title and Country = @Country
for xml auto
GO
b. Enlist the orchestration and start all.
c. Every three seconds an XML file will be drop to
C:\ directory. The file has following content:
Using SQL Send in a Message Only Scenario
(7)Create a table in Northwind database
CREATE TABLE SQLSendTable(
PrimaryID bigint IDENTITY(1, 1) NOT
NULL,
Col_varchar varchar(50) NULL,
Col_datetime datetime NULL,
Col_money money NULL
)
(8)Using the BizTalk Project in the previous example
(9)Generate an XSD schema for SQL Send
a. Follow (3)a through (3)b to launch the
wizard.
b. Configure Schema Information
Enter Target namespace: http://SQLSendSample
Select Send port option for port type
Enter Input root element name: InRootName
Enter Output root element name: OutRootName
Click Next on the Wizard
c. Configure Statement Information
Select Insert for the type of updategram
Select SQLSendTable from table list.
Select all columns except the PromaryID
Click Next on the Wizard
d. Complete the wizard
Click Finish the wizard
The wizard will generate a schema file
(InsertSQLSendTableService1.xsd) for the send (request
and
response) documents, along with an Orchestration file. In
this sample scenario we do need the orchestration file.
(10)Create Ports
a. Create a One-Way receive port: ReceivePort_FILE
b. Create a receive location for the port just
created: ReceiveLocation_FILE
Set Transport Type: FILE
Receive Handler: BizTalkServerApplication
Receive Pipeline: PassThruReceive
Receive folder: C:\
File mask: SQLSendSample.xml
Batch size: 1
c. Create a Static One-Way send port: SendPort_SQL
Select Transport Type: SQL
Set Address (URI) property:
Set Send Pipeline: PassThruTransmit
Set Filter: BTS.ReceivePortName = ReceivePort_FILE
(11)Test the scenario
a. Generate a document instance from the schema.
b. Make following changes to the document instance.
Note: This has something to do with a bug, it is a
temporary solution.
Change the Col_datetime attribute value:
Old value: Col_datetime="1999-05-
31T13:20:00.000-05:00"
New value: Col_datetime="1999-05-
31T13:20:00.000"
Add a $ in front of the Col_money attribute value:
Old value: Col_money="10.4"
New value: Col_money="$10.4"
c. Rename the document to: SQLSendSample.xml and drop
it the C:\
d. Start the Send port and then enable the receive
location.
e. Check the SQLSendTable in Northwind database, one
row was inserted.
Jay wrote:
> Hi,
>
> Can someone please point me to the docs in Biztalk 2004 re
> how to use SQL transport in a port?
>
> I want to pass a parameter into a simple select statement
> (or stored proce..whichever is easier).
>
> Eg: I want my port to execute 'select mycol from mytable
> where myid = %myPassedInID%
>
> The transport properties dialog mentions a SQL transport
> wizard but I cant find it...
>
> Thanks!
>
| |
| Vince Sefcik 2004-03-18, 2:00 pm |
| There are a series of SQL adapter samples, with Visual Studio screens shots,
available at http://weblogs.asp.net/scottwoo/arc...3/11/88387.aspx
"ilija" <ilijalazarov@nospamming.hotmail.com> wrote in message
news:ecNu6JuCEHA.2908@TK2MSFTNGP09.phx.gbl...[color=darkred]
> Check this out!
>
> ilija
>
> ----
> Using SQL Receive in an orchestration
> (1)Create following stored procedure in Northwind database
> create PROCEDURE SP_SelectCustomers
> @Title nvarchar(30),
> @Country nvarchar(15)
> AS
> select *
> from Customers
> where ContactTitle = @Title and Country = @Country
> for xml auto, xmldata
> GO
> (2)Create an empty BizTalk Server Project
> (3)Generate an XSD schema for the SQL receive.
> a. Launch the SQL Adapter Schema Wizard.
> Right click on the project in the Solution Explorer
> window, select Add, then Add Generated Items.
> Click OK on the Add Generated Item dialog box with all
> default.
> In Add Adapter Wizard dialog, select SQL, click Next
> button. (The Server, Database and Port field can be left
> if using local BizTalk database)
> b. Configure Database Information
> In the Database Information page of SQL Transport Schema
> Generation Wizard, click Set button.
> In the Data Link Properties dialog, set the Server, Log
> in
> and Northwind database. Test, then click OK to close the
> dialog.
> Click Next on the Wizard.
> c. Configure Schema Information
> Enter Target namespace: http://SQLReceiveSample
> Select Receive port option for port type
> Enter Input root element name: InRootName
> Click Next on the Wizard
> d. Configure Statement Information
> Select Stored procedure option
> From the stored procedure combo box select:
> SP_SelectCustomers
> Set the parameter Title: Marketing Manager
> Set the parameter Country: USA
> Click Generate button
> Click Next on the wizard
> e. Complete the wizard
> Click Finish the wizard
> The wizard will generate a schema file (SQLService1.xsd)
> for the incoming receive documents, along with an
> Orchestration file (BizTalk Orchestration1.odx)
> (4)Create an orchestration
> a. Drop a port on the port surface, and name it:
> SQLReceivePort. Choose to Use an existing Port Type.
> b. In the port direction, choose: I will always be
> receiving messages on this port; In the Port binding
> choose: Specify later.
> c. Drop an other port on the port surface, name it:
> FileSendPort. Choose to Create a new Port Type. Set Port
> Type Name: FileSendPortType. It is a One-Way port.
> d. In the port direction, choose: I will always be
> sending messages on this port; In the Port binding
> choose:
> Specify later.
> e. Drop a Receive shape followed by a Send shape on
> the work flow surface.
> f. Connect SQLReceivePort to the Receive shape, then
> connect Send shape to FileSendPort. Both Receive and Send
> will be processing Message_1.
> g. Compile and deploy the project
> (5)Create Ports and Bindings
> a. Create a One-Way Receive port: ReceivePort_SQL
> b. Create a Receive location for the receive port
> just created: Receive_Location_SQL. Select Transport
> Type:
> SQL. Choose Receive Handler: BizTalkServerApplication,
> and
> Receive Pipeline: XMLReceive
> c. Configure the Address(URI) property. This will
> bring up the SQL Adapter custom property page:
> Set Polling Interval 3.
> Scroll down to SQL Command field, click on the . button
> to
> bring up the Inport information from a generated schema
> dialog. Select Project: SQLAdapterSample. Select Schema:
> SQLAdapterSample.SQLService1.
> Click OK. The Document Root Element Name and Document
> Target Namespace field will be automatically filled.
> Set the Connection String field.
> d. Create a Static One Way Send Port: SendPort_FILE.
> Drop files to location C:\. Use PassThruTransmit Pipeline.
> e. Bind the two ports to the orchestration
> (6)Test the application
> a. Make a small change to the stored procedure: Take
> the XMLDATA predicate out. Note: The predicate XMLDATA is
> only useful when you generate a schema.
> create PROCEDURE SP_SelectCustomers
> @Title nvarchar(30),
> @Country nvarchar(15)
> AS
> select *
> from Customers
> where ContactTitle = @Title and Country = @Country
> for xml auto
> GO
> b. Enlist the orchestration and start all.
> c. Every three seconds an XML file will be drop to
> C:\ directory. The file has following content:
>
> Using SQL Send in a Message Only Scenario
> (7)Create a table in Northwind database
> CREATE TABLE SQLSendTable(
> PrimaryID bigint IDENTITY(1, 1) NOT
> NULL,
> Col_varchar varchar(50) NULL,
> Col_datetime datetime NULL,
> Col_money money NULL
> )
> (8)Using the BizTalk Project in the previous example
> (9)Generate an XSD schema for SQL Send
> a. Follow (3)a through (3)b to launch the
> wizard.
> b. Configure Schema Information
> Enter Target namespace: http://SQLSendSample
> Select Send port option for port type
> Enter Input root element name: InRootName
> Enter Output root element name: OutRootName
> Click Next on the Wizard
> c. Configure Statement Information
> Select Insert for the type of updategram
> Select SQLSendTable from table list.
> Select all columns except the PromaryID
> Click Next on the Wizard
> d. Complete the wizard
> Click Finish the wizard
> The wizard will generate a schema file
> (InsertSQLSendTableService1.xsd) for the send (request
> and
> response) documents, along with an Orchestration file. In
> this sample scenario we do need the orchestration file.
> (10)Create Ports
> a. Create a One-Way receive port: ReceivePort_FILE
> b. Create a receive location for the port just
> created: ReceiveLocation_FILE
> Set Transport Type: FILE
> Receive Handler: BizTalkServerApplication
> Receive Pipeline: PassThruReceive
> Receive folder: C:\
> File mask: SQLSendSample.xml
> Batch size: 1
> c. Create a Static One-Way send port: SendPort_SQL
> Select Transport Type: SQL
> Set Address (URI) property:
> Set Send Pipeline: PassThruTransmit
> Set Filter: BTS.ReceivePortName = ReceivePort_FILE
> (11)Test the scenario
> a. Generate a document instance from the schema.
> b. Make following changes to the document instance.
> Note: This has something to do with a bug, it is a
> temporary solution.
> Change the Col_datetime attribute value:
> Old value: Col_datetime="1999-05-
> 31T13:20:00.000-05:00"
> New value: Col_datetime="1999-05-
> 31T13:20:00.000"
> Add a $ in front of the Col_money attribute value:
> Old value: Col_money="10.4"
> New value: Col_money="$10.4"
> c. Rename the document to: SQLSendSample.xml and drop
> it the C:\
> d. Start the Send port and then enable the receive
> location.
> e. Check the SQLSendTable in Northwind database, one
> row was inserted.
>
>
>
> Jay wrote:
| |
|
| Please Help: When I try to compile in 4G, I go an error.
Error: Orchestration.odx(170): you must specify at least
one already-initialized correlation set for a non-
activation receive that is on a non-selfcorrelating port
>-----Original Message-----
>There are a series of SQL adapter samples, with Visual
Studio screens shots,
>available at
http://weblogs.asp.net/scottwoo/arc.../03/11/88387.as
px
>
>
>"ilija" <ilijalazarov@nospamming.hotmail.com> wrote in
message
>news:ecNu6JuCEHA.2908@TK2MSFTNGP09.phx.gbl...
database[color=darkred]
then[color=darkred]
Send[color=darkred]
SendPort_FILE.[color=darkred]
Pipeline.[color=darkred]
Take[color=darkred]
is[color=darkred]
In[color=darkred]
drop[color=darkred]
one[color=darkred]
2004 re[color=darkred]
statement[color=darkred]
mytable[color=darkred]
transport[color=darkred]
>
>
>.
>
|
|
|
|
|