COZYROC EDI components, part of COZYROC SSIS+ suite, are third-party plug-ins for Microsoft SSIS that make it easy to parse and generate EDI files. The toolkit is easy to use and follows the same guidelines and principles used by the standard out-of-the-box SSIS components.
The EDI integration package consists of:
- EDI Connection Manager
- Stores the settings for the particular EDI file format (i.e. configuration rules) and the EDI input/output file.
- EDI Source component
- Parses EDI files according to the rules defined in an EDI configuration file.
- EDI Destination component
- Generates EDI files according to the rules defined in an EDI configuration file.
EDI Source and EDI Destination make it easy to parse & construct EDI documents via an EDI configuration file. The field mapping and any necessary transformations are configurable via the standard SSIS means. For transferring the EDI files to business partners or VANs, you can use AS2 Send Task or File Transfer Task.
EDI Destination
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.
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
Overview
EDI Connection Manager is SSIS Connection Manager for for configuring settings of EDI Source (after 1.9) and EDI Destination. It allows specifying the path of the EDI Input / Output File and the EDI Configuration File. The editor also displays the structure of the currently loaded EDI configuration.
Parameters
General
The path of the input/output EDI file (depending on usage context):
- Input EDI File when used by EDI Source
- Output EDI File when used by EDI Destination
Select the used encoding for the EDI file.
Design-Time
An EDI document is comprised of data elements, segments and envelopes that are formatted according to the rules of a particular EDI standard. To be able to parse or generate EDI files, it's necessary to specify a EDI Configuration that describes those rules in XML format.
The column length for EDI elements. The default is 250.
Knowledge Base
- How to remove the segments in Edi File if all the segment elements are empty?
- Where can I find the documentation for the EDI Connection?
- 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?
- Error Message: The connection type "xxxx" specified for connection manager "xxxx" is not recognized as a valid connection manager type. This error is returned when an attempt is made to create a connection manager for an unknown connection type. Check the spelling in the connection type name.
What's New
- New: A new parameter CodePage to set the used code page for the EDI file.
- Fixed: Failed with error "This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms" (Thank you, Ethan).
- New: Introduced connection.
Related documentation
Overview
The EDI Source Component is an SSIS Data Flow Component for parsing EDI format files via an EDI Configuration File. The component has already been successfully used to process 210, 271, 277, 810, 812, 820, 822, 832, 834, 835, 837, 850, 852, 855 X12 and D95B EDIFACT formats.
COZYROC offers consulting services, in case you don't have the resources internally to build the configuration file. For details, contact us.
The latest EDI Source has now a custom editor to select the EDI Connection Manager and configure the InsertSequenceTerminator property. In 2.2, there is also added an optional selection for variable as the source of the EDI data, which can be `string` (the EDI messages) or `object` (for streaming EDI data).
Demonstration
Case Study
Parameters
General
In 1.9 use the General page of the EDI Source dialog to configure the component. For older versions of the EDI Source, use the standard Advanced Editor.
Select an existing EDI connection manager.
Indicates whether an empty line should be inserted at the end of each repeating segment (related to composite records support). The default is false.
Options Description True Empty line will be inserted. False Empty line will not be inserted. Specify the EDI Configuration File.
Indicate whether or not the input file name is stored in a variable. This parameter has the options listed in the following table.
Value Description True The input file name is stored in a variable. Selecting this value displays the dynamic parameter InputFileVariable. False The input file name is specified. Selecting this value displays the dynamic parameter InputFile. Select the input file name.
Select an existing user-defined variable, or click <New variable...> to create a new variable.
Related Topics: Integration Services Variables, Add Variable
Type Usage string The EDI messages text. object Stream variable for EDI data (often used with File Transfer Task). Override the segment delimiter loaded from the configuration file. This parameter is not visible in the setup dialog, but can be set in the data flow task properties pane.
Override the field delimiter loaded from the configuration file. This parameter is not visible in the setup dialog, but can be set in the data flow task properties pane.
Override the component delimiter loaded from the configuration file. This parameter is not visible in the setup dialog, but can be set in the data flow task properties pane.
Override the sub-component delimiter loaded from the configuration file. This parameter is not visible in the setup dialog, but can be set in the data flow task properties pane.
Specify the column length for EDI elements. The default is 250.
Knowledge Base
- How to remove the segments in Edi File if all the segment elements are empty?
- Where can I find the documentation for the EDI Source?
- How to exclude EDI elements you don't need from data flow
- How to upgrade to use the EDI Connnection Manager, introduced in 1.9 version?
- Error Message: Must be a minimum of 1 instances of segment []. Currently at segment number
Related scripts
Samples
What's New
- New: Support for stream input.
- New: A new optional attribute segname for segment element in configuration file. If specified, the component will provide segment name information.
- Fixed: Incorrect processing of escaped symbols when multiple delimiter symbols were specified (Thank you, Gavin).
- New: A custom editor to replace the basic one.
- New: All previous configuration properties are now available in the new EDI Connection. This is the recommended way of configuring the component.
- New: A new parameter InsertSequenceTerminator to support "composite records"-compatible formatting of output data.
- New: A new configuration parameter escape. The parameter allows specification of escape combination to handle special control symbols like segment delimiters used in the data.
- New: Component now setups data flow outputs only for looping segments. The previous setup logic will stay the same for existing packages for backwards compatibility.
- New: Component now provides better diagnostic when column processing fails.
- New: Component now supports removal of unused outputs and columns.
- New: A new parameter skipCharacters configuration file. Used for specifying characters, which has to be ignored. This option obsoletes skipNewline parameter.
- New: A new optional attribute segid for segment element in configuration file. If specified, the component will provide segment identity information.
- New: A new parameter undefinedFail in configuration file. If set to false, the component will not fail when new fields, components and sub-components appear in the input file.
- New: A new parameter ColumnLength to specify column length of EDI elements.
- Fixed: Failed to load configuration files containing special characters like 1D, 1E, etc (Thank you, Bryan).
- Fixed: Component didn’t provide useful diagnostic information when loading configuration file with errors.
- New: A new parameter skipNewline in configuration file for avoiding formatting carriage return / line feed characters.
- New: Component can now match segments by specifying a regular expression in segcode configuration parameter. Also the sub-segments data flow outputs now include the parent segment fields.
- Fixed: Component did not process sub-segments properly.
- New: Introduced component.
Related documentation
Overview
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.
Main functionality:
- 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).
Miscellaneous:
- 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.
Quick Start
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.
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
Knowledge Base
- 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?
What's New
- New: Support for data streaming.
- 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.
Related documentation
An EDI document is comprised of data elements, segments and envelopes that are formatted according to the rules of a particular EDI standard. When encoding data (e.g. a purchase order), in an EDI document, you must adhere to the formatting rules of the corresponding standard. These rules define exactly where and how each piece of information in the document will be found. This enables the receiving side to decode the information stored in every piece of data by its position and corresponding definition (e.g. company name, price, etc).
An EDI Configuration is an XML file that gives you the flexibility to define your own set of rules or use a format predefined by an existing EDI standard. Our clients have successfully used our EDI components to process 210, 271, 277, 810, 812, 820, 822, 832, 834, 835, 837, 850, 852, 855 X12 and D95B EDIFACT formats.
The XML configuration file has the following structure:
Parameters
XML Configuration File
The EDI source/destination configuration is an XML file, that can be validated by this XSD.
The configuration elements of the format are:
This is the top level root element of the XML document.
Element relationships
Relationship Element Parent elements <none> Child elements description, delimiters, segments This element contains a user-friendly description of the configuration file.
Attributes
Attribute Description name Description of the configuration file. version Version of the configuration file. Element relationships
Relationship Element Parent elements edimap Child elements <none> This element contains the EDI file delimiters for the segment, field, component and sub-component parts. Special characters like line feed character are specified using XML Character References.
Attributes
Attribute Description segment Segments delimiter character. field Fields delimiter character. component Components delimiter character. sub-component Sub-components delimiter character. Element relationships
Relationship Element Parent elements edimap Child elements <none> This element contains parameters for controlling the EDI parser.
Attributes
Attribute Description skipNewline 1.3 SR-4 Indicate that carriage return / line feed characters should be ignored. Starting from 1.4 SR-3 this option is replaced by the skipCharacters option below. To get the equivalent of specifying this option to true, set skipCharacters to " " undefinedFail 1.4 SR-2 Specify how to handle the appearance of new field, component and sub-component elements. If set to false the parser will ignore the new elements. skipCharacters 1.4 SR-3 Specify a list of characters to be ignored. escape 1.6 SR-4 Specify escape combination to handle special control symbols like segment delimiters used in the data. Element relationships
Relationship Element Parent elements edimap Child elements <none> This element specifies an EDI segment. The segment is represented as a separate Input (when used by EDI Destination) or Output (when used by EDI Source) in the SSIS data flow, if it repeats. If the segment repeats once, the segment elements are included as part of the parent segment to serve as context for other segments. You can specify multiple segments.
Attributes
Attribute Description segcode Code found in the EDI file, which establishes the mapping relationship to this segment. Starting from 1.3 SR-2, this attribute accepts a regular expression for matching complex segments. xmltag Name of the segment. truncatable If used by EDI Source, when set to true, the parser will not generate errors when the segment does not contain trailing fields, components, and sub-components, which are not required. In case of EDI Destination, the data flow component will not generate empty fields at the end of the segment. minOccurs Minimum amount of times this segment occurs. If not specified the default is 1. maxOccurs Maximum amount of times this segment occurs. Specify -1 if this segment may occur an unlimited number of times. If not specified, the default is 1. segid 1.4 SR-3 Optional attribute which, if specified, will create an additional field for the segment's data flow output. The field will contain a unique segment identifier. segname 2.0 Optional attribute which, if specified, will create an additional field for the segment's data flow output. The field will contain the segment name. Element relationships
Relationship Element Parent elements segments Child elements field This element specifies an EDI field. Each field is represented as a column in the SSIS data flow. You may specify multiple fields.
Attributes
Attribute Description xmltag Name of the field. truncatable When used by EDI Source and set to true, the parser will not generate errors when the field does not contain trailing components or sub-components which are not required. When used by EDI Destination and set to true, the data flow component will not generate non required, empty components at the end of the record. required Specifies whether or not the field is required. By default it is set to false indicating the field is not required. Element relationships
Relationship Element Parent elements segment Child elements component This element specifies an EDI component. Each component is represented as a column in the SSIS data flow. You may specify multiple components.
Attributes
Attribute Description xmltag Name of the component. truncatable When used by EDI Source and set to true, the parser will not generate errors when the component does not contain trailing sub-components which are not required. When used by EDI Destination and set to true, the data flow component will not generate non required, empty sub-components at the end of the record. required Specifies whether the component is required. By default it is set to false indicating the component is not required. Element relationships
Relationship Element Parent elements field Child elements sub-component This element specifies an EDI subcomponent. Each subcomponent is represented as a column in the SSIS data flow. You may specify multiple sub-components.
Attributes
Attribute Description xmltag Name of the subcomponent. required Specifies whether the subcomponent is required. By default it is set to false indicating the subcomponent is not required. Element relationships
Relationship Element Parent elements component Child elements <none>
Knowledge Base
Related documentation
EDI Connection
- New: A new parameter CodePage to set the used code page for the EDI file.
- Fixed: Failed with error "This implementation is not part of the Windows Platform FIPS validated cryptographic algorithms" (Thank you, Ethan).
- New: Introduced connection.
EDI Destination
- New: Support for data streaming.
- 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.
EDI Source
- New: Support for stream input.
- New: A new optional attribute segname for segment element in configuration file. If specified, the component will provide segment name information.
- Fixed: Incorrect processing of escaped symbols when multiple delimiter symbols were specified (Thank you, Gavin).
- New: A custom editor to replace the basic one.
- New: All previous configuration properties are now available in the new EDI Connection. This is the recommended way of configuring the component.
- New: A new parameter InsertSequenceTerminator to support "composite records"-compatible formatting of output data.
- New: A new configuration parameter escape. The parameter allows specification of escape combination to handle special control symbols like segment delimiters used in the data.
- New: Component now setups data flow outputs only for looping segments. The previous setup logic will stay the same for existing packages for backwards compatibility.
- New: Component now provides better diagnostic when column processing fails.
- New: Component now supports removal of unused outputs and columns.
- New: A new parameter skipCharacters configuration file. Used for specifying characters, which has to be ignored. This option obsoletes skipNewline parameter.
- New: A new optional attribute segid for segment element in configuration file. If specified, the component will provide segment identity information.
- New: A new parameter undefinedFail in configuration file. If set to false, the component will not fail when new fields, components and sub-components appear in the input file.
- New: A new parameter ColumnLength to specify column length of EDI elements.
- Fixed: Failed to load configuration files containing special characters like 1D, 1E, etc (Thank you, Bryan).
- Fixed: Component didn’t provide useful diagnostic information when loading configuration file with errors.
- New: A new parameter skipNewline in configuration file for avoiding formatting carriage return / line feed characters.
- New: Component can now match segments by specifying a regular expression in segcode configuration parameter. Also the sub-segments data flow outputs now include the parent segment fields.
- Fixed: Component did not process sub-segments properly.
- New: Introduced component.
Knowledge Base
- How to remove the segments in Edi File if all the segment elements are empty?
- 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?
- How to exclude EDI elements you don't need from data flow
- How to upgrade to use the EDI Connnection Manager, introduced in 1.9 version?
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.