Продажи: 1-800-867-1389

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

Обновлено: Январь 2014 г.

This step provides instructions on how to create a Преобразование 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 Преобразование, let’s have a look at the two schemas and then let’s understand what needs to be done as part of the Преобразование.

Here’s a snapshot of the two schemas:

The following table lists the mapping requirements and the Операция сопоставленияs 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 Операция сопоставления.

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 Преобразование. On the Преобразование 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 Операция сопоставления on the Преобразование surface. Double-click the Операция сопоставления, 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 Операция сопоставления, add a ForEach Loop Операция сопоставления, 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 Операция сопоставления, add an Arithmetic Expression Операция сопоставления. Connect the UnitPrice and Quantity elements from the source schema to this Операция сопоставления. Double-click the Операция сопоставления 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 Операция сопоставления (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 Преобразование to pick that variable. To do so, we use the Select Entries Операция сопоставления.

    Drag and drop the Select Entries Операция сопоставления to the design surface and connect it to the Create List operation. Double-click the Select Entries Операция сопоставления 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 Операция сопоставления and then connect it to the Select Entries Операция сопоставления. Double-click the Cumulative Sum Операция сопоставления and in the text box, enter the following expression:

    item.Amount
    

    We must now connect the Cumulative Sum Операция сопоставления to the TotalAmount element in the destination schema. This is how the Преобразование should resemble (only for mapping the TotalAmount element in the destination schema):

    noteПримечание
    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.

См. также

Была ли вам полезна эта информация?
(1500 символов осталось)
Спасибо за ваш отзыв
Показ:
© 2014 Microsoft