How to Generate SQL Command Schemas for the CData BizTalk Adapter for Certinia

This article will show how to generate a schema to process Certinia data retrieved using the CData BizTalk Adapter. You will generate a schema for a SELECT command.

This guide shows how to use the CData BizTalk Adapter for Certinia to execute SQL commands to Certinia.

Add the Adapter for Certinia to Your Project

Follow the steps below to add the adapter to a BizTalk Server project.

1. If you have not already done so, create a new BizTalk Server project in Visual Studio.
2. Right-click on the project in the Solution Explorer and click Add -> Add Generated Items.
3. Select Add Adapter Metadata in the resulting dialog box.
4. In the resulting Add Adapter wizard, select the adapter from the list.
5. In the Port menu, leave the selection blank. You can also select a receive location or send port that has the adapter configured as its transport type.

Generate a Schema for an SQL Command

After you select the adapter in the Add Adapter wizard, the Schema wizard is displayed. Follow the steps below to configure connection properties and retrieve the metadata for the results of the command.

1. In the Connection String page, enter authentication credentials and other connection properties, if you did not select an adapter that you have already configured in your BizTalk application. Below is a typical connection string: User=myUser;Password=myPassword;Security Token=myToken;InitiateOAuth=GETANDREFRESH

   There are several authentication methods available for connecting to Certinia: login credentials, SSO, and OAuth.

   Authenticating with a Login and Token

   Set the User and Password to your login credentials. Additionally, set the SecurityToken. By default, the SecurityToken is required, but you can make it optional by allowing a range of trusted IP addresses.

   To disable the security token:

   1. Log in to Certinia and enter "Network Access" in the Quick Find box in the setup section.
   2. Add your IP address to the list of trusted IP addresses.

   To obtain the security token:

   1. Open the personal information page on certinia.com.
   2. Click the link to reset your security token. The token will be emailed to you.
   3. Specify the security token in the SecurityToken connection property or append it to the Password.

   Authenticating with OAuth

   If you do not have access to the user name and password or do not want to require them, use the OAuth user consent flow. See the OAuth section in the Help for an authentication guide.

   Connecting to Certinia Sandbox Accounts

   Set UseSandbox to true (false by default) to use a Certinia sandbox account. Ensure that you specify a sandbox user name in User.

2. In the General Options section on the next page, Schema Information, select Send Port or Receive Location, depending on the adapter configuration.
3. If you want to generate the schema for the entire result set, select the Single Message option in the Message Mode menu. If you want to generate one schema for each row in the result set, select the Message Per Row option and confirm that the root element of the message is 'row'.
4. In the Command Type menu, select SQL Command.

5. In the SQL Command Text box on the Statement Information page, enter an SQL command. (If you have configured a receive location or send port, the SQL command you configured is entered in the box.) This example uses the query below:

   SELECT BillingState, Name FROM Account WHERE Industry = 'Floppy Disks'

6. Confirm the settings in the summary displayed by the wizard and click Finish to generate the schema.xsd file.

Processing Schemas

To use schemas in a simple BizTalk application, see the tutorial.