Obtain data from SQL Server for BizTalk instances

Biztalk Server uses the SQL adapter to communicate with SQL Server. The following describes how to obtain data from SQL Server 2000.

In this example, use the SQL adapter to obtain data from SQL Server 2000 and save the data in XML format to the C: \ out folder.

The specific steps are as follows:

1. Open the BizTalk browser in Visual Studio. NET 2003.

2. Create an eport_ SQL receiving port.

3. Add the receive_location_ SQL receiving location for the receiving port.

4. Set the transmission type to SQL and receive and processProgramBiztalkserverapplication and the receiving channel is Microsoft. BizTalk. defaultpipelines. passthrureceive.

5. set the address, open the SQL Transmission Properties window, set the connection string to the northwind database of the localhost host, set the document root element name to salesroot, and set the document target namespace to http: // sales, set the SQL command to select orderid, customerid, orderdate, requireddate from orders where (orderdate = '2017-9-20 ') for XML auto, elements.

Note: In actual applications, you can change the where condition to sorderdate = cast (convert (varchar (10), getdate (), 121) as datetime ), indicates obtaining the record of the current day. In addition, the Unit of polling measurement is set to hour, and the polling interval is 12, which indicates two queries per day.

6. Create a sending port sendport_file. In "Configuration \ transmission \ main", set the transmission type to file and the address to c: \ out \ % messageid %. XML; in "Configuration \ send", set the sending pipeline to Microsoft. biztalk. defaultpipelines. passthrutransmit; in "Configure \ filter and ing \ filter", set BTS. receiveportname = receiveport_ SQL.

7. Select receive_location_ SQL, right-click and choose "enable.

8. Select sendport_file and right-click and choose start from the shortcut menu.

9. After running, the XML file will be output in the C: \ out folder. The content of the file is as follows:

<? XML version = "1.0" encoding = "UTF-16"?> <Salesroot xmlns = "http: // sales"> <orders> <orderid> 10310 </orderid> <customerid> thebi </customerid> <orderdate> 1996-09-20t00: 00: 00 </orderdate> <requireddate> 1996-10-18t00: 00: 00 </requireddate> </orders> <orderid> 10311 </orderid> <customerid> dumon </customerid> <orderdate> 1996-09-20t00: 00: 00 </orderdate> <requireddate> 1996-10-04t00: 00: 00 </requireddate> </orders> </salesroot>

Trackback: http://tb.blog.csdn.net/TrackBack.aspx? Postid = 908987