Share via

Step 5: Transform the Flat File Schema to the Insert Schema for OrderDetails Table

  • Article

 

Important

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 topic lists the steps to create a Transform to map the flat-file message schema to the schema of an Insert operation on the OrderDetails table in SQL Server. Before we create a Transform, let’s have a look at the two schemas and then let’s understand what needs to be done as part of the Transform.

Here’s a snapshot of the two schemas:

Flat-file and Insert schemas

The following table lists the mapping requirements and the Map Operations using which this is accomplished:

What to map?

How to achieve?

OrderId node in the source schema maps directly to OrderId node in the destination schema.

You directly connect the two nodes in the two schemas.

Because OrderDetails node in the flat-file schema is a repeating node, all the values in the Quantity node should be added up and the cumulative value should be passed on to the QuantityOrdered node in the destination schema.

You connect the Quantity and QuantityOrdered nodes using a Cumulative Sum Map Operation.

The TotalAmount node in the destination schema must contain the cumulative value of all (Unit Price * Quantity).

Instructions on how to set this are provided at To map to the TotalAmount element

To map to the TotalAmount element

  1. In Visual Studio, right click the FTP_EAI_Tutorial project, point to Add, and then select New Item.

  2. In Add New Item, select Map, enter Map.trfm as the map name, and then select OK.

  3. In Solution Explorer, double click the Map.trfm file to open the Transform. On the Transform design area, set the source schema to PO.xsd and set the destination schema to FTPEAITutorial_TableOperation.dbo.OrderDetails.xsd.

  4. On the Toolbox, from the List Operations category, drag-and-drop a Create List Map Operation on the Transform design area. Double-click the Map Operation, select the plus sign, and enter Amount as the member name. Enter the Member Type as Number and then select OK.

    By doing this, you are creating an in-memory variable called Amount.

  5. Within a List Map Operation, add a ForEach Loop Map Operation, and then connect it to the OrderDetails element in the source schema. You do so because OrderDetails element is a repeating element in the message schema.

  6. Within a ForEach Loop Map Operation, add an Arithmetic Expression  Map Operation. Connect the UnitPrice and Quantity elements from the source schema to this Map Operation. Double-click the Map Operation to open the Configure Arithmetic Expression dialog box, and in the Enter arithmetic expression box, type UnitPrice * Quantity.

  7. By now, we have calculated the value of UnitPrice * Quantity for each iteration of the OrderDetails element. We now need to assign this value to the Amount variable we created as part of the List operation. To do so, we include an Add Item to List operation within the ForEach Loop operation and connect it to the Arithmetic Expression operation. Doing this assigns the value from the Arithmetic Expression Map Operation (which is Unit Price * Quantity) to the Amount variable created as part of the List operation.

  8. A List operation could have many variables defined. Even though in our tutorial, we have just one variable (Amount) defined, we still need to configure the Transform to pick that variable. To do so, we use the Select Entries Map Operation.

    Drag and drop the Select Entries Map Operation to the design surface and connect it to the Create List operation. Double-click the Select Entries Map Operation to open the Configure Select Entries dialog box, and from the Select members box, select the check box against Amount.

  9. We now need to do a cumulative sum of the value in the Amount variable. So, we add a Cumulative Sum Map Operation and then connect it to the Select Entries Map Operation. Double-click the Cumulative Sum Map Operation and in the text box, enter the following expression:

    item.Amount

    We must now connect the Cumulative Sum Map Operation to the TotalAmount element in the destination schema. This is how the Transform should resemble (only for mapping the TotalAmount element in the destination schema):

    Map operation to calculate total amount

    Note

    You can also look at the map available as part of the FTP_EAI_Tutorial sample available at https://go.microsoft.com/fwlink/?LinkId=247973.

See Also

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