EDI Destination

Modelling orders in the database

Let DVD discs orders be stored in MS SQL Server in three tables: Order, Customer and OrderDetails with the following relationships:

Setup an OLEDB Source to retrieve rows from the “Order” and “Customer” tables

We will do a simple join to get this data.

Setup Query component to retrieve the details for each order

Let’s add a Multicast Transformation, as we would want to send the output rows to multiple downstream components.

For each order we need to retrieve the corresponding “OrderDetails” records (i.e. make a join on “OrderId” column respectively).

Here is how to setup the Query transformation this for the “OrderDetails” table:

• In the “General” tab specify for SQL Query “SELECT * FROM OrderDetails”
• Tick the Insert sequence terminator checkbox to insert blank rows after processing each order. NOTE: These blank rows would be used by the EDI Destination to determine parent-child relationships.
• In the “Columns” tab, create a connection between the two “OrderId” column in the lists of “Available Input Columns” and “Available Query Columns”

Create an EDI Destination component

Drag&drop an EDI Destination component to the design surface and open it editor.

Configure an EDI Connection Manager

Click “New...” to create an EDI Connection Manager. Specify the location for the generated file and choose the EDI XML configuration.

The sample EDI XML configuration file has the following content:

Connect the EDI Destination to the upstream components

After completing the configuration of the EDI Connection Manager and closing its editor, two inputs (“header”, “order-item”) would get created in the EDI Destination, because we have only one customer for every header (order). So the number of inputs is reduced from three as it’s described in XML to two.

Let’s now connect the EDI Destination to the upstream OLE DB Source and the Query Transformations. (NOTE: We also need to have two Data Conversion Transformation to control how numbers with a decimal point (e.g. “Price”) gets converted to string).

Create mapping for “Order” and “Customer” columns in EDI Destination

In the “header” input map table columns to EDI format fields.

Create mapping for “Order Details” columns in EDI Destination

In the “order-item” input map table columns to EDI format fields.

Run the package to generate the output EDI file

The result from execution of the package should be successful and the generated file will have an EDI-compliant content similar to this one:

HDR*1*0*59.970*64.920*4.950*2018-12-11 00:00:00.0000000 +02:00
CUS*user1*Harry*Fletcher*SD
ORD*1*1*364*Star trek*30
ORD*2*1*299*Pulp Fiction*30
HDR*2*0*60.000*67.000*7.000*2018-11-12 00:00:00.0000000 +02:00
CUS*user2*Ivan*Ivanov*SD
ORD*1*1*363*Star Wars*29
ORD*2*1*200*Aurora*30