セールス: 1-800-867-1380
情報
要求されたトピックは次のとおりです。しかし、このトピックはこのライブラリには含まれていません。

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

更新日: 2014年1月

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