The EDI Destination Component is an SSIS Data Flow Component for generating EDI format files. Introduced in November, 2018 1.9, but based on time-tested technology used in EDI Source, since 2008.
- 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”
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
- Where can I find the documentation for the EDI Destination?
- Error Message: Must be a minimum of 1 instances of segment . Currently at segment number
- I want to use your EDI Source component. Where can I get the XML configuration file for the specific format I want to process?
- Fixed: Delimiters incorrectly generated for optional
componentelements with NULL input (Thank you, Gavin).
- New: Support for delimiter escape definition.
- Fixed: Redundant sub-component delimiter (Thank you, James).
- Fixed: Various fixes and improvements.
- New: Introduced component.
COZYROC SSIS+ Components Suite is free for testing in your development environment.
A licensed version can be deployed on-premises, on Azure-SSIS IR and on COZYROC Cloud.