Discover how a bimodal integration strategy can address the major data management challenges facing your organization today.
Get the Report →How to Generate SQL Command Schemas for the CData BizTalk Adapter for Sage US
The CData BizTalk Adapter makes it easy to process Sage US data in BizTalk by returning the data as XML. You can use this data in a BizTalk orchestration by creating an XML schema file for the table you want to access in BizTalk.
This article will show how to generate a schema to process Sage US 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 Sage US to execute SQL commands to Sage US.
Add the Adapter for Sage US to Your Project
Follow the steps below to add the adapter to a BizTalk Server project.
- If you have not already done so, create a new BizTalk Server project in Visual Studio.
- Right-click on the project in the Solution Explorer and click Add -> Add Generated Items.
- Select Add Adapter Metadata in the resulting dialog box.
- In the resulting Add Adapter wizard, select the adapter from the list.
- 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.
- 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:
ApplicationId=8dfafu4V4ODmh1fM0xx;CompanyName=Bellwether Garden Supply - Premium;
The Application Id and Company Name connection string options are required to connect to Sage as a data source. You can obtain an Application Id by contacting Sage directly to request access to the Sage 50 SDK.
Sage must be installed on the machine. The Sage.Peachtree.API.dll and Sage.Peachtree.API.Resolver.dll assemblies are required. These assemblies are installed with Sage in C:/Program Files/Sage/Peachtree/API/. Additionally, the Sage SDK requires .NET Framework 4.0 and is only compatible with 32-bit applications. To use the Sage SDK in Visual Studio, set the Platform Target property to "x86" in Project -> Properties -> Build.
You must authorize the application to access company data: To authorize your application to access Sage, restart the Sage application, open the company you want to access, and connect with your application. You will then be prompted to set access permissions for the application in the resulting dialog.
While the compiled executable will require authorization only once, during development you may need to follow this process to reauthorize a new build. To avoid restarting the Sage application when developing with Visual Studio, click Build -> Configuration Manager and uncheck "Build" for your project.
- In the General Options section on the next page, Schema Information, select Send Port or Receive Location, depending on the adapter configuration.
- 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'.
- In the Command Type menu, select SQL Command.
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 Name, LastInvoiceAmount FROM Customer
- 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.