(0) exportieren Drucken
Alle erweitern

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

Letzte Aktualisierung: Januar 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:

Flatfile- und INSERT-Schema

The following table lists the mapping requirements and the Zuordnungsvorgangs 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 Zuordnungsvorgang.

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 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 Zuordnungsvorgang on the Transformation surface. Double-click the Zuordnungsvorgang, 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 Zuordnungsvorgang, add a ForEach Loop Zuordnungsvorgang, 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 Zuordnungsvorgang, add an Arithmetic Expression Zuordnungsvorgang. Connect the UnitPrice and Quantity elements from the source schema to this Zuordnungsvorgang. Double-click the Zuordnungsvorgang 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 Zuordnungsvorgang (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 Zuordnungsvorgang.

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

    item.Amount
    

    We must now connect the Cumulative Sum Zuordnungsvorgang 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):

    Zuordnungsvorgang zur Berechnung der Gesamtsumme

    noteHinweis
    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.

Siehe auch

Community-Beiträge

Microsoft führt eine Onlineumfrage durch, um Ihre Meinung zur MSDN-Website zu erfahren. Wenn Sie sich zur Teilnahme entscheiden, wird Ihnen die Onlineumfrage angezeigt, sobald Sie die MSDN-Website verlassen.

Möchten Sie an der Umfrage teilnehmen?
Anzeigen:
© 2014 Microsoft