Step 7: Transform the Flat File Schema to the Insert Schema



Microsoft Azure BizTalk Services (MABS) is being retired, and replaced with Azure Logic Apps. If you currently use MABS, then Move from BizTalk Services to Logic Appsprovides some guidance on moving your integration solutions to Logic Apps.

If you're brand new to Logic Apps, then we suggest getting started here:

This section demonstrates how to map the elements in the incoming flat file message to the elements in the Insert message for the Claims table. To understand which elements in the source schema map to which elements in the destination schema, it is important to first look at the two schemas.

Flat file and Input schemas

The following table lists the mapping requirements and the map operations to use to accomplish the message transformation:

What to map?

How to achieve?

All the elements in the flat-file schema map to relevant elements in the Insert message schema. For example, ClaimID maps to ClaimID, ClaimType maps to ClaimType, and so on.

You directly connect the two nodes in the two schemas.

Because the ClaimTypeDescription element in the Insert message schema gets its value by using the lookup configuration defined earlier, ClaimType element in the flat file schema also maps to the ClaimTypeDescription element in the Insert schema by using a Data Lookup map operation.

See To use a Data Lookup map operation

After defining the map, include it in the bridge configuration defined in Step 6: Configure a One-Way Bridge.

To use a Data Lookup map operation

  1. Right-click the FlatFile_Bridge project, point to Add, and then click New Item.

  2. In the Add New Item dialog box, select Map, specify the map name as FF_SQL_Transform.trfm, and then click OK.

  3. On the map surface, select the source schema to InsuranceClaim.xsd and the destination schema to FlatFile_Bridge_TableOperation.dbo.Claims.xsd (Insert operation).

  4. On the Toolbox, from the Misc Operations category, drag-and-drop a Get Context Property Map Operation on the map surface. Double-click the map operation, and for the Property Name text box, enter ClaimTypeDescription, and then click OK.

  5. Connect the Get Context Property Map Operation to the ClaimTypeDescription element in the destination schema.

    As part of the bridge configuration, you had already configured the lookup such that the claim type description is already retrieved from the Microsoft Azure SQL Database and is assigned to a ClaimTypeDescription property that gets created at runtime. Using the Get Context Property Map Operation, you assign the value of that property to the ClaimTypeDescription element in the destination schema.

    Map the other elements in the source and destination schemas, as shown below.

    Configure the map

    Save the map.

  6. Save the project.

To add the map to the bridge configuration

  1. In the FlatFile_Bridge project, open the .bridgeconfig file, and select the Xml Transform activity.

  2. From the Properties pane, click the ellipsis button (…) against the Maps property to open the Map Selection dialog box.

  3. Select the check box for the FF_SQL_Transform map and then click OK.

  4. Save changes to the project.

See Also

Tutorial: Using BizTalk Service Bridges to Lookup Data from Azure SQL Database