Tutorial 2: Using Azure BizTalk Bridges to Insert Flat File Messages into an On-premises SQL Server

Fabrikam and Contoso are two business partners. Fabrikam (the retailer) sends sales order messages to Contoso (the supplier). Contoso maintains all the sales order data in table called Orders, which is housed in a SQL Server database within Contoso’s premises. Fabrikam intends to send flat-file messages to Contoso using an FTP server. Hence, Contoso has to implement a solution on Windows Azure, which enables the following:

• Contoso must pull the flat file messages from the FTP server at which Fabrikam drops the sales order messages.
  
  
• Contoso must process the message received from Fabrikam and map it to the message for inserting the sales order in its SQL Server database.
  
  

To enable this scenario, Contoso does the following:

• Generates the schema of the flat-file message that it will receive from Fabrikam.
  
  
• Configures an XML One-Way Bridge as part of the BizTalk Service project to enable message validation and transformation. This bridge takes a flat-file message, validates it against the schema generated earlier, and then transforms it to the schema required to enter a message into the SQL Server database.
  
  
• Uses BizTalk Adapter Service to enable connectivity to the on-premise SQL Server database from the XML One-Way Bridge deployed on the Service Bus.
  
  

For instructions on how to prepare for the BizTalk Services scenario, see How do I Start Using the Windows Azure BizTalk Services SDK.

• Step 1: Create the BizTalk Services Project
  
  
• Step 2: Generate the Schema for the Flat-file Message
  
  
• Step 3: Add an FTP Source
  
  
• Step 4: Create and Configure the LOB Target
  
  
• Step 5: Transform the Flat File Schema to the Insert Schema for OrderDetails Table
  
  
• Step 6: Configure an XML One-Way Bridge
  
  
• Step 7: Build and Deploy the Project
  
  
• Step 8: Test the Solution
  
  

Other Resources