Exporter (0) Imprimer
Développer tout

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

Mis à jour: janvier 2014

This step provides instructions on how to create a Transformation the map the flat-file message schema to the schema of an Insert operation on the OrderDetails table in SQL Server. Before we create a Transformation, let’s have a look at the two schemas and then let’s understand what needs to be done as part of the Transformation.

Here’s a snapshot of the two schemas:

The following table lists the mapping requirements and the Opération de mappages 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 Opération de mappage.

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

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

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

  3. In the Solution Explorer, double click the Map.trfm file to open the Transformation. On the Transformation surface, select the source schema to PO.xsd and the destination schema to FTPEAITutorial_TableOperation.dbo.OrderDetails.xsd.

  4. On the Toolbox, from the List Operations category, drag-and-drop a Create List Opération de mappage on the Transformation surface. Double-click the Opération de mappage, click the plus sign, and enter Amount as the member name. Specify the Member Type as Number and then click OK.

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

  5. Within a List Opération de mappage, add a ForEach Loop Opération de mappage, 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 Opération de mappage, add an Arithmetic Expression Opération de mappage. Connect the UnitPrice and Quantity elements from the source schema to this Opération de mappage. Double-click the Opération de mappage 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 Opération de mappage (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 Transformation to pick that variable. To do so, we use the Select Entries Opération de mappage.

    Drag and drop the Select Entries Opération de mappage to the design surface and connect it to the Create List operation. Double-click the Select Entries Opération de mappage 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 Opération de mappage and then connect it to the Select Entries Opération de mappage. Double-click the Cumulative Sum Opération de mappage and in the text box, enter the following expression:

    item.Amount
    

    We must now connect the Cumulative Sum Opération de mappage to the TotalAmount element in the destination schema. This is how the Transformation should resemble (only for mapping the TotalAmount element in the destination schema):

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

Voir aussi

Afficher:
© 2014 Microsoft