- Supports generating a wide variety of EDI standard documents (X12, EDIFACT, etc.) and custom EDI-compatible formats. The format is specified in an EDI Configuration File.
- Very fast, streaming generation of large EDI files.
- Configurable via EDI Connection Manager that allows specifying EDI Configuration File and EDI Output File.
- Supports generating EDI files via multiple SSIS inputs (see COZYROC's SSIS composite records modelling).
- Together with EDI Source can be used for tweaking existing EDI files, i.e. load data from an existing EDI file, perform any kind of transformation, and generate output EDI file. NOTE: The EDI Source needs to have "Insert Null Terminators" property enabled.
- Reduced complexity - if there is a segment definition in the EDI Configuration File that can occur just once, the segment definition is attached to the input of a parent definition for which maximum possible occurrence is greater than one (or eventually the root), thus reducing the number of the inputs making it more easy to use while maintaining all the capabilities of the EDI format.
To demonstrate how to use an EDI Destination, we would create an SSIS package to automate a sample scenario - exporting DVD discs orders to an EDI-compliant format.
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”
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:
<medi:description name="DVD Order" version="1.0" /> <medi:delimiters segment=" " field="*" component="^" sub-component="~" /> <medi:segments xmltag="Order"> <medi:segment segcode="HDR" xmltag="header" maxOccurs="-1"> <medi:field xmltag="order-id" /> <medi:field xmltag="status-code" /> <medi:field xmltag="net-amount" /> <medi:field xmltag="total-amount" /> <medi:field xmltag="tax" /> <medi:field xmltag="date" /> <medi:segment segcode="CUS" xmltag="customer-details"> <medi:field xmltag="username" /> <medi:field xmltag="firstname" /> <medi:field xmltag="lastname" /> <medi:field xmltag="state" /> </medi:segment> <medi:segment segcode="ORD" xmltag="order-item" maxOccurs="-1"> <medi:field xmltag="position" /> <medi:field xmltag="quantity" /> <medi:field xmltag="product-id" /> <medi:field xmltag="title" /> <medi:field xmltag="price" /> </medi:segment> </medi:segment> </medi:segments>
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).
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
- New: Introduced component.
Ready to give it a try?
COZYROC SSIS+ Components Suite is free for testing in your development environment.