This article provides detailed explanation required to create a BizTalk application to retrieve records from SQL server and insert them into db2 database.
For this, we use 2 major adapters:
· SQL Adapter at the receive port
· Db2 Adapter at the send port
To start with, there are 4 major steps involved.
· Creating the table and stored procedure
· Creating the BizTalk application
· Configuring the ports
· Testing the application
Creating the table and stored procedure
Note: For this sample we will be working with the Address table of AdventureWorks2000 database
Create the stored procedure as follows, to insert records to the Address table.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TestSP]
AS
BEGIN
SET NOCOUNT ON;
SELECT * from TestTable for XML auto, xmldata
END
Creating the BizTalk Application
Open Microsoft Visual Studio 2005. Select File->New->Projects. From the Project Types: select BizTalk projects and from the right pane select Empty BizTalk Project. Provide a folder location(C :/) for the project and specify a name for the project (UsingDb2Adapter) and click OK. We have to develop 5 artifacts:
· SQL service Schema
· MsDb2SendUpdategram Schema
· Map
· Receive Pipeline
· Orchestration
Creating SQL service Schema
Right click on the newly created project and select Add->Add Generated Items. In the dialog box that appears, select Add Adapter Metadata from the left pane and Add Adapter Metadata from the templates and click ADD.
Select SQL from the list of registered adapters. Set SQL Server to the local machine and Database to BizTalkMgmtDb. Leave the port option blank and proceed.
Set the connection string. Select the SQL server name. Select “Use Windows NT Integrated Security” as log on information. Select the respective database where the table you are referring to in the stored procedure resides from database drop down list and click Test Connection. The test connection should succeed.
In the Schema Information dialog box, provide target namespace in the following manner. http://projectname
Select receive port option. Type in a unique Receive document root element name. (E.g. TestResponse)
Select stored procedure from Statement type information and click next. Select stored procedure from the drop down list and generate script.
Click next and then finish.
The adapter wizard generates an SQL Service schema.
Creating MsDb2SendUpdategram Schema
Right click on the project name and select Add->Add Generated Items. In the dialog box that appears, select Add Adapter Metadata from the left pane and Add Adapter Metadata from the templates and click ADD.
Select DB2 from the list of registered adapters. Set SQL Server to the local machine and Database to BizTalkMgmtDb. Leave the port option blank and proceed.
Click next on theDdb2 Adapter Schema Generation wizard welcome dialog box.
Select “Create a new connection” and give next. (If you already have a saved *.udl connection file for the same file on db2 database, you can select that using, “Existing Connection string”)
On the Data Source screen, select the following:
· Data Source Platform: DB2/AS400
· Network Type: TCP/IP
On the TCP/IP Network Connection screen, type the following:
· Address or Alias: xx.xx.xx.xx (IP Address of the host System)
· Port: 446 (By default, the TCP/IP port number for an IBM iSeries (AS/400) system is 446.)
On the DB2 database screen, do the following:
· Initial catalog: (Name of the AS/400 RDBNAME)
· Package collection: (Name of the AS/400 library in which the packages for the provider are created and stored.)
· Default schema: (Default schema name. Typically, this is the name of the AS/400 library in which the tables are accessed. )
· Default qualifier: (Name of the default qualifier. )
If the library that hosts the tables differs from the library that is specified in the default schema, the Default qualifier box should contain the name of the library that hosts the table. This value is used as the second value in a three-part naming scheme.
If the library that hosts the table is the same as the default schema, leave this box blank.
Accept the default settings on the Locale screen and proceed.
Provide the User name, Password and Password Confirmation on the Security screen. (The user name and password details are the ones used to login to the host system.)
Select Connection Pooling and Derive Parameters from Advanced Options.
When you give Connect on Validation screen, the connection will succeed.
After the connection test succeeds, click Sample Query. A listing of the tables in the destination library should appear. If a listing of the tables does not appear, the connection string is configured incorrectly.
Give next. Provide the following information on the Schema Information screen.
· Target Namespace: (http://projectname)
· Request Document Root Element Name: TestRequest
· Response Document Root Element Name: TestResponse
Under statement type information, select Updategram.
Select “Insert” from the Select type of Updategram drop down. The list of tables appears on the Table name list box. Select the respective table. The list of fields appears on the grid view. Check all, give next and finish.
The MsDb2SendUpdategram is generated.
Creating Map
Right click on project name and select Add->New Item. Select Map and provide suitable name.
In the Open Source Schema, select the SQL Service schema that we created. In the destination schema, select MsDb2SendUpdategram schema and select request root from the option provided.
Connect each field of the Source schema with the Destination Schema.
Creating Receive Pipeline
Right click on project name and select Add->New Item. Select receive pipeline and provide suitable name.
Drag and drop the XML Disassembler component from the list of BizTalk pipeline components into the Disassembler box in the pipeline design area.
In the property pane of XML Disassembler component, set the Document Schema to SQL Service schema created.
Save the application.
Creating Orchestration
The SQL Service generation wizard would have automatically created an orchestration. Rename it if necessary. The MsDb2SendUpdategram generation wizard will also have generated an orchestration. Right click on it, and Exclude from project.
In the Orchestration view, create two messages with the following specifications
Message_1
· Identifier: Db2_Msg
· Message Type: Schemas-> MsDb2SendUpdategram Request Schema
Message_2
· Identifier: SQL_Msg
· Message Type: Schemas->SQL Service Schema
In the Orchestration view, create two port types with the following specifications
PortType_1
· Identifier: SQL_Rcv_PortType
· Message Type: SQL Service Schema
PortType_2
· Identifier: Db2_Snd_PortType
· Message Type: MsDb2SendUpdategram Request Schema
In the Orchestration Design Window, drag and drop the following shapes and set the specific properties.
Port_1
· Name: SQL_Rcv_Port
· Port Type: SQL_Rcv_PortType
· Port Direction: Receive
· Port Binding: Specify later
Port_1
· Name: Db2_Snd_Port
· Port Type: Db2_Snd_PortType
· Port Direction: Send
· Port Binding: Specify later
Receive
· Message: SQL_Msg
· Operation: SQL_Rcv_Port.Operation_1.Request
Construct Message
· Message Constructed: Db2_Msg
Transform
Click Ellipses from the Input Messages property and from the Transform Configuration dialog box that appears, select Existing map. From Source select SQL_Msg and for Destination, select Db2_Msg.
Send
· Message: Db2_Msg
· Operation: Db2_Snd_Port.Operation_1.Request
Save the application. Generate a strong name key. Build and deploy the application.
Now, Open the stored procedure being used in the project and remove the xmldata tag from it and execute the stored procedure, so that it appears as follows:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[TestSP]
AS
BEGIN
SET NOCOUNT ON;
SELECT * from TestTable for XML auto
END
Configuring the ports
Open BizTalk Administration Console. Expand the nodes and arrive at the newly created application. The orchestration appears in the admin console. Now we need to create Send and receive ports.
Creating Receive Port
Right click on Receive Port and select New->One Way Receive Port. Provide a name and select Receive location from the left pane. Select new from right pane and provide a name.
Select SQL from Type and click Configure.
Set the connection string and test connection.
Type document namespace as earlier (http:// http://projectname)
Response Document Root Element Name: TestResponse
In Receive pipeline drop down box, select the pipeline created in the application.
Creating Send Port
Right click on Send Port and select New->Static One Way Send Port. Provide a name and select DB2 from Type and click Configure.
Set the connection string. (Follow the same instruction as when generating the MsDb2SendUpdategram schema)
Type document namespace as earlier (http:// projectname)
Response Document Root Element Name: TestResponse
Set Filters: BTS.ReceivePortName== (name of the receive port)
Configure the application and start it.
Testing the Application
Once the application and Host instances are started, the records would have been picked up from the SQL server database table and inserted into db2 database file. You can check this by logging into AS/400 machine with login credentials and check for the file.