This section provides information about source and target connectors from H to O

The Health Insurance Portability and Accountability Act of 1996 (HIPAA) mandates that health care organizations must adopt transaction and information security standards to support the electronic exchange of administrative and financial health care data for administrative simplification.

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For more details, see Determining Field Width in Characters or Bytes.

The HIPAA (Health Insurance Portability and Accountability Act of 1996) requires the use of certain standard formats for documentation of medical claims that are related to services and other transactions associated with medical record keeping. Following is the list of HIPAA form titles and the corresponding form number:

Definitions of each of the naming structure components:

The ANSI standards reflect generic supersets designed to address a wide range of data exchange requirements. You may decide to define your own "subset" and develop rules that describe the data you submit. If you elect to develop your own schema, be aware that many errors can be traced to schema "version mismatch". It is important that all trading partners agree on the specific version of the schema to be used in transactions. Addenda schemas especially, should be examined to verify version match with all parties involved in the transaction.

Changes to, or customization of the schema must be communicated among and mutually agreed upon by all trading partners prior to "live" data exchange. A customized or modified schema should be verified by each partner to ensure version match at each point of exchange within the trading group. Complete preparation is the key to minimize production errors.

You are provided with a template file that contains the structure for your HIPAA file connections. If your HIPAA file structure is different, you may need to modify the template.

The template file is designed to parse source data to generate a target schema. You cannot use this file to parse source data in a map. Doing this will cause an error.

To obtain a template file, contact your sales representative. These template files are standard schemas.

To connect to an HIPAA source or target file, you must set a schema in the HIPAASchemaFile property. You cannot connect directly to the schema. You must select a schema file from your current project that contains the schema you want to use.

The following is the HIPAA message schema hierarchy. If you make changes to the default HIPAA schema, make sure you adhere to the HIPAA schema rules defined below to prevent the updated schema from becoming invalid.

(Do not edit) A container of one or more functional groups. The interchange/message container must include a header (ISA) and trailer (IEA). The segments can appear only once in the message. The record type name is always Loop.Function.

(Do not edit) A collection of messages. The functional group container must include a header (GS) and trailer (GE) for the functional group container and must reside inside the interchange enveloping segment. The record type name is always Loop.Function.

(Do not edit) A container of one or more segments and loops; also referred to as a message. The transaction container must include a header (ST) and trailer (SE) for the transaction container, and reside inside of the functional group. The record type name is always Loop.Message.

Defines a group of loops or segments in which only one loop or segment can be selected. The record type name always includes Choice as the prefix. For example, Choice.2000

Repeating data within the message that is mapped to the JSON field. The record type name always includes the Loop prefix. For example, Loop.1000A

A collection of fields that share a particular data type.

Defines a group of elements in a sequence that is mapped to struct_type_ref. Prefixed with C.

The element ID uses the prefix E. For example, E.I12.13.

Below are the steps to create a segment or a loop. You can also add these parts of a schema by duplicating existing parts and making appropriate edits.

The data types that you see under Type for the source or target schema are HIPAA data type names, such as DT, AN, or ID. However, the integration platform reads them as text. To use a DT (DateTime) data type in an expression, treat the data type as text that must be converted to a (DateTime) data type. For more information about converting data types, search for "commonly used functions" in the documentation.

Loading and unloading HIPAA structural information takes a few seconds longer when compared with other connectors. After clicking various lists and choosing HIPAA Health Claim adapter files, you may notice a pause of a few seconds. The status text displays the message "Building tree structure, please wait." Other status messages indicate that schemas are getting built.

This limitation affects HIPAA selections at the following locations:

You can set the following source (S) and target (T) properties.

The following element separators are supported:

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For more details, see Determining Field Width in Characters or Bytes.

To verify that you are working with valid HIPAA EDI files, check the value of the second record in the file. This should be something like "004010XNNN" where "NNN" is the numeric value that corresponds to the appropriate HIPAA X-code transaction set (for example, "004010X098" for the Professional 837).

The integration platform includes the HIPAA (eDoc) connector for helping to speed the creation, parsing, transformation and integration of HIPAA transactions with existing systems. It provides built-in HIPAA transaction parsing logic and compliant target templates for mapping native data (without the need to modify existing applications). The connector provides a tool to develop and test transactions while maintaining data integrity.

The integration platform offers bi-directional translation of the ANSI ASC X12N 837 Health Care Claims (837) transaction, for professional (x098src-24975746), dental (x097src-24975746) and institutional (x096src-24975746) claims to any format required by existing systems. For incoming transactions, the most complex 837 is automatically parsed into a flat presentation for drag-and-drop mapping of hierarchical EDI elements to native target structures or schemas. For outbound 837 transactions, the integration platform provides empty 837 compliant target structures, stored as templates, for mapping. Rules for reconciling data syntax and semantic differences required by the output are constructed through an event driven programming model coupled with a powerful visual RIFL Script Editor. During the entire map process actual "live" data is used, not the metadata, which eases the design process and provides immediate testing and debugging of the map designs.

A list of HIPAA transaction sets, that includes Guide ID, Guide Description, Primary Trading Partners and Exceptions/Limitations, follows:

270/271: Health Care Eligibility/Benefit Inquiry and Information Response

GUIDE ID: 004010X092

Guide Description: The Health Care Eligibility/Benefit Inquiry and Information Response Implementation Guide describes the use of the Eligibility, Coverage or Benefit Inquiry (270) Version/Release 3070 transaction set and the Eligibility, Coverage, or Benefit Information (271) Version/Release 3070 transaction set for the following business usage:

PRIMARY TRADING PARTNERS: Health care providers, such as hospitals and physicians. Health care payers, such as insurance companies, HMOs, PPOs and state and federal agencies, such as Medicare, Medicaid and CHAMPUS.

EXCEPTIONS/LIMITATIONS: Intended to use for health care eligibility/benefits. It does not provide a history of benefit usage, and is not intended for property and casualty or workers' compensation usage.

To learn about how HIPAA/EDI terminology differs from other data types used in integration tools, see Property Options.

The ISA Interchange Control Header is the first segment in all transaction set files. If this segment does not appear first, or is not exactly 105 bytes, the file is not a valid HIPAA/EDI format.

If you include expressions in your transformation, be sure to include element separators for the segments that are unused (no data in segments that are not required, called optional or situational). This is essential if there are any segments that are required after the unused segment(s). The separators should be used to indicate the location of each segment string. For instance, if you do not have data in the optional NM107 (Name Suffix) segment, you still must put in a placeholder, since there are other segments that are required after the NM107 (Name Suffix) segment.

Optional segments, as the name implies, are not required. Situational segments are also optional, however, they are required if another segment is used that requires the situational segment. Do not use optional/situational segments to reset any flags you have set. If that flagged segment is not used, the flag does not operate as you originally planned. Use flags on required segments only.

The Health Insurance Portability and Accountability Act of 1996 (HIPAA) mandates that the health care organizations must have transaction and information security standards to support the electronic exchange of administrative and financial health care data.

In HIPAA regulations, the service provider is responsible for the accuracy and timely submission of claims and other reporting documents. If you are a service provider, then:

If you are a vendor, then understand:

The HIPAA (eDoc) connector supports the HIPAA 5010 guideline, featuring the use of repeating field elements and is defined in ISA11 as 165.

EDI X12 and HIPAA connectors are not interchangeable as they were in version 9. In version 10, you must use the correct connector type and schema. To decide the connector that must be used, you can use any of the following ways:

HIPAA (Health Insurance Portability and Accountability Act of 1996) uses certain standard formats for documentation of medical claims related to services and other transactions associated with maintaining the medical records. The following table provides the HIPAA form titles and the corresponding form numbers.

 

Addenda schemas use the following naming structure:

where,

The ANSI standards reflect generic supersets designed to address a wide range of data exchange requirements. You may decide to define your own "subset" and develop rules that describe the data you submit. If you want to develop your own schema, many errors can occur due to the schema “version mismatch”. Hence, all trading partners must agree on the specific version of the schema to be used in transactions. Addenda schemas must be examined to verify version match with all parties involved in the transaction.

To verify the version of a schema, open the schema in a text editor. Check the header information. In the header, note the date created and date (last) modified fields. These fields determine precise version match.

Any schema changes or customizations must be mutually agreed upon by all the trading partners prior to "live" data exchange. A customized or modified schema must be verified by each trading partner to make sure that the version matches at each point of exchange within the trading group.

You can obtain an HIPAA (eDoc) schema file from HIPAA that contains the structure for your HIPAA (eDoc) file connections. If your HIPAA (eDoc) file structure is different, you may have to modify the template.

The template file is designed to parse source data to generate a target schema. You cannot use this file to parse source data in a map. This causes an error. Instead, perform the following steps to generate a schema from a target connector that you can use in a map.

To obtain a template file, contact your sales representative. These template files are standard schemas.

To connect to a HIPAA source or target file, you must set a schema in the HIPAASchemaFile property. You cannot connect directly to the schema. You must select a schema file from your current project that contains the schema you want to use.

If you make changes to the default HIPAA schema, make sure you adhere to the following HIPAA schema rules to prevent the updated schema from becoming invalid.

A container of one or more functional groups. The interchange/message container must include a header (ISA) and trailer (IEA). The segments can appear only once in the message. The record type name is always Loop.Function.

Elements:

A collection of messages. The functional group container must include a header (GS) and trailer (GE) and must reside inside the interchange enveloping segment. The record type name is always Loop.Function.

 

Elements:

A container of one or more segments and loops. Also, referred to as a message. The transaction container must include a header (ST) and trailer (SE) and reside inside of the functional group. The record type name is always Loop.Message.

Elements:

Defines a group of loops or a group of segments in which only one loop or segment can be selected. The record type name always includes Choice as the prefix. For example, Choice.2000

Elements:

Repeating data within the message that is mapped to the JSON field. The record type name always contains the Loop prefix. For example, Loop.1000A

Elements:

A collection of fields that shares a particular data type.

Elements:

Defines a group of elements in a sequence that is mapped to struct_type_ref. Prefixed with C.

Elements:

The element ID uses the prefix E. For example, E.I12.13.

This section provides information that specific to the connector.

The data types that are displayed under the Type for source or target schema are HIPAA data type names, such as DT, AN, or ID. However, the integration platform reads them as text. To use a DT (DateTime) data type in an expression, consider the data type as text that must be converted to a (DateTime) data type.

Loading and unloading HIPAA structural information takes a few seconds longer than the other connectors. After clicking various lists and choosing HIPAA Health Claim adapter files, you may notice a pause for a few seconds. The status text displays the message "Building tree structure, please wait." Other status messages indicate that schemas are being built.

This limitation affects HIPAA selections at the following locations:

You can set the following source (S) and target (T) properties.

The following element separators are supported:

This connector sets field width in bytes. The number of characters allowed for a field varies. For more details, see Determining Field Width in Characters or Bytes.

To verify that you are working with valid HIPAA EDI files, check the value of the second record in the file. This value must be similar to "004010XNNN" where "NNN" is the numeric value that corresponds to the appropriate HIPAA X-code transaction set (for example, "004010X098" for the Professional 837).

The integration platform includes the HIPAA (eDoc) connector to speed the creation, parsing, transformation, and integration of HIPAA transactions with existing systems. It provides built-in HIPAA transaction parsing logic and compliant target templates for mapping native data (without the need to modify existing applications). The connector provides a tool to develop and test transactions while maintaining data integrity.

The integration platform offers bi-directional translation of the ANSI ASC X12N 837 Health Care Claims (837) transaction, for professional (x098), dental (x097), and institutional (x096) claims to any format required by existing systems. For incoming transactions, the most complex 837 is automatically parsed into a flat presentation for drag-and-drop mapping of hierarchical EDI elements to native target structures or schema. For outbound 837 transactions, the integration platform provides empty 837 compliant target structures, stored as templates, for mapping. Rules for reconciling data syntax and semantic differences required by the output are constructed through an event driven programming model coupled with a powerful visual Script Editor. During the entire map process, actual "live" data is used and not the metadata, which eases the design process and provides immediate testing and debugging of map designs.

A listing of HIPAA transaction sets, which includes Guide ID, Guide Description, Primary Trading Partners, and Exceptions/Limitations, follows:

270/271: Health Care Eligibility/Benefit Inquiry and Information Response

GUIDE ID: 004010X092

Guide Description: The Health Care Eligibility/Benefit Inquiry and Information Response Implementation Guide describes the use of the Eligibility, Coverage or Benefit Inquiry (270) Version/Release 3070 transaction set and the Eligibility, Coverage, or Benefit Information (271) Version/Release 3070 transaction set for the following business usage:

PRIMARY TRADING PARTNERS: Health care providers, such as hospitals and physicians. Health care payers, such as insurance companies, HMOs, PPOs and state and federal agencies, such as Medicare, Medicaid and CHAMPUS.

EXCEPTIONS/LIMITATIONS: Intended for use for health care eligibility/benefits. Does not provide a history of benefit usage. Is not intended for property and casualty or workers' compensation usage.

To learn about how HIPAA/EDI terminology differs from other data types used in integration tools, see Property Options.

The ISA Interchange Control Header is the first segment in all transaction set files. If this segment does not appear first, or is not exactly 105 bytes, the file is not a valid HIPAA/EDI format.

If you include expressions in your transformation, make sure that the element separators are included for the segments that are unused (no data in segments that are not required, called optional or situational). This is essential if there are any segments that are required after the unused segment(s). The separators must be used to indicate the location of each segment string. For instance, if you do not have data in the optional NM107 (Name Suffix) segment, you must add a placeholder, since there are other segments that are required after the NM107 (Name Suffix) segment.

Optional segments are not required. Situational segments are also optional. However, they are required if another segment is used and that requires the situational segment. Do not use optional or situational segments to reset any flags you have already set. If that flagged segment is not used, the flag does not operate as you originally planned. Use flags on required segments only.

Hitachi Hi-RDB is a program that the integration platform can connect to with an ODBC 3.x driver.

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For more details, see Determining Field Width in Characters or Bytes.

The integration platform connects to Hitachi Hi-RDB tables with ODBC 3.x. For the procedure, and information about the property options, limitations, and source and target schemas, see ODBC 3.x.

Health Level Seven (HL7) is an organization that develops messaging standard specifications that enable disparate health care applications to exchange keys sets of clinical and administrative data. The HL7 standard provides an electronic interchange of clinical, financial and administrative information among independent health care oriented computer systems, such as hospital information systems, clinical laboratory systems, enterprise systems and pharmacy systems.

The HL7 standard provides the layout of messages exchanged between two or more applications. A message is comprised of multiple segments that must be sent in a particular order and which may or may not repeat. Segments are collections of data elements that typically share a common subject. For example, the PID segment contains patient identification data elements such as name, address and social security number. The NK1 next of kin segment contains similar data elements about the patient's next of kin.

HL7 defines which data elements are to be sent and determines the data type and the suggested length of each. It also indicates whether the data element is required or optional and whether it may repeat. Fields must be sent in the order in which they are presented in the Standard. This way, both the sending and receiving systems know what data is expected, in what order and in what format. HL7 also enables one system to query another for relevant information.

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For more details, see Determining Field Width in Characters or Bytes.

Single record type data: Fixed ASCII has only one certainty – every record in the file has exactly the same number of bytes. (If all the single record type data is not exactly the same length, the file is a variable length file and Map Editor cannot read it as Fixed ASCII.

Multirecord Type Files: It is possible to work with variable-length multi-record files (with or without a record separator) using some specific guidelines. For more information, search for the “multiple record type” in the documentation.

Because there are so many variations of fixed ASCII data files, you may need to specify those variations in Target Properties.

You are provided a template file that contains the structure for your HL7 file connections. If your HL7 file structure is different, you may need to modify the template.

To obtain a template file, contact your sales representative. These template files are standard schemas.

To connect to an HL7 source or target file, you must set a schema in the SchemaFile property. You cannot connect directly to the schema. You must select a schema file from your current project that contains the schema you want to use.

It is important to define the structure of your HL7 files before opening them in the integration platform. This is because HL7 files may be loosely structured and most often need to have additional rules applied to them.

Replace File/Table is the only Output Mode available for HL7 target files.

The HL7 connector is designed to work with one message at a time. Files containing two or more messages must be split to process them. Each message begins with a MSH segment and the MSH must exist only once in a message as the first segment. HL7 was designed to be a real time messaging interface where discrete messages are generated and routed immediately to their destination. Some users employ a batch mode process, where multiple messages are written to a single file before transport occurs. This is not the intended use for HL7. Split batch files into discrete messages before processing.

You can set the following source (S) and target (T) properties.

Options are as follows:

These are the data types of the fields in your HL7 target data table. The following data types are available:

If you are writing to a new table, you may use any of these available data types from the list. Click the Type arrow to make a new selection. If you are appending data to an existing table, the data type of each field uses the data type in the selected table by default.

Health Level Seven (HL7) is an organization that develops messaging standard specifications that enable disparate health care applications to exchange key sets of clinical and administrative data. The HL7 standard provides an electronic interchange of clinical, financial, and administrative information among independent health care oriented computer systems, such as hospital information systems, clinical laboratory systems, enterprise systems, and pharmacy systems.

HL7 standard provides a layout of messages exchanged between two or more applications. A message is comprised of multiple segments that must be sent in a particular order which may or may not repeat. Segments are collections of data elements that typically share a common subject. For example, the PID segment contains patient identification data elements such as name, address, and social security number. The NK1 next to kin segment contains similar data elements about the patient's next to kin.

HL7 defines the data elements that must be sent and determines the data type and the suggested length for each data element. Also, it indicates whether the data element is required, optional, or may be repeated. Fields must be sent in the same order as present in the Standard. This way, both sending and receiving systems know the data that is expected, the order, and the format. HL7 also enables one system to query another system for the relevant information.

The HL7 connector sets field width in bytes. The number of characters allowed for a field varies. For more details, see Determining Field Width in Characters or Bytes.

You can obtain a HL7 (eDoc) schema file from HL7 that contains the structure for your HL7 file connections. If your HL7 file structure is different, you may have to modify the template.

To obtain a template file, contact your sales representative. These template files are standard schemas.

To connect to an HL7 source or target file, you must set a schema in the SchemaFile property.

This section provides information that is specific to the connector.

It is important to define the structure of your HL7 files before opening them in the integration platform. This is because HL7 files may be loosely structured and most often must have additional rules applied to them.

The HL7 connector works with one message at a time. Files containing two or more messages must be split to process them. Each message begins with a MSH segment and the MSH must exist only once in a message as the first segment. HL7 is a real time messaging interface, where discrete messages are generated and routed immediately to their destination. Some users employ a batch mode process, where multiple messages are written to a single file before transport occurs. This is not the intended use for HL7. Split batch files into discrete messages before processing.

You can set the following source (S) and target (T) properties.

Options are as follows:

HTML is one of the data publishing file formats used in many web browser applications, such as Internet Explorer. The integration platform can write HTML files in specific structured formats.

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For more details, see Determining Field Width in Characters or Bytes.

HTML is not available as a source connector. However, you can read HTML data sources by using one of three methods:

You can set the following source (S) and target (T) properties.

All data in an HTML file is Text.

IBM DB2 9.7 is a source and target connector that connects directly to an IBM DB2 9.7 database, or connects with an ODBC 3.5 driver. Version 9.7 is supported.

This connector specifies field width in number of bytes, not characters. For details, see Determining Field Width in Characters or Bytes.

This connector does not support Unicode. Use the ODBC 3.5 connector to enable Unicode support in an IBM DB2 9.7 database.

You can set the following source (S) and target (T) properties in IBM DB2 9.7 connections.

In these example statements, we create two indices on the table called "mytable". The first index does not allow duplicates and the index values are stored in ascending order. The second index is a compound index on fields Field2 and Field3.

ConstraintDDL is executed only if replace mode is used for the target. If there are errors, the errors are written to the error and event log file. An error during transformation brings up the transformation error dialog box. To ignore DDL errors, you may continue the transformation.

ConstraintDDL also supports an escaping mechanism to specify DDL in the native SQL of the DBMS. Any statement preceded by an "@" is sent to the DBMS.

The following is a DDL statement for creating a primary key for the table mytable.

Some ODBC drivers do not support the SQL extensions needed to create a primary key with the ODBC variant of the SQL CREATE statement. To create a primary key in these cases, use native SQL.

The following data types are supported:

DB2 can be a source or target connection either directly or through an ODBC driver. You still need to set up an ODBC data source to make the connection. For more information, see the procedure under ODBC 3.5. Also see IBM DB2 9.7.

The integration platform allows for concurrent writes to multiple target tables. Multimode connections allow you to perform table drop and table insert operations directly on your target database.

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For more details, see Determining Field Width in Characters or Bytes.

In multimode targets, modifications to column names, data types, and sizes are not permitted.

You can set the following target properties for IBM DB2 9.7 Multimode connections.

The following data types are supported for data fields:

IBM DB2 10 is a source or target connector that connects directly to an IBM DB2 10 database, or connects with an ODBC 3.5 driver. Version 10 is supported.

This connector specifies field width in number of bytes, not characters. For details, see Determining Field Width in Characters or Bytes.

This connector does not support Unicode. Use the ODBC 3.5 connector to enable Unicode support in an IBM DB2 10 database.

You can set the following source (S) and target (T) properties in IBM DB2 10 connections.

In these example statements, we create two indices on the table called "mytable". The first index does not allow duplicates and the index values are stored in ascending order. The second index is a compound index on fields Field2 and Field3.

ConstraintDDL is executed only if replace mode is used for the target. If there are errors, the errors are written to the error and event log file. An error during transformation brings up the transformation error dialog box. To ignore DDL errors, you may continue the transformation.

ConstraintDDL also supports an escaping mechanism to specify DDL in the native SQL of the DBMS. Any statement preceded by an "@" is sent to the DBMS.

The following is a DDL statement for creating a primary key for the table mytable.

Some ODBC drivers do not support the SQL extensions needed to create a primary key with the ODBC variant of the SQL CREATE statement. To create a primary key in these cases, use native SQL.

The following data types are supported:

DB2 can be a source or target connection either directly or through an ODBC driver. You still need to set up an ODBC data source to make the connection. For more information, see the procedure under ODBC 3.5. Also see IBM DB2 10.

The integration platform allows for concurrent writes to multiple target tables. Multimode connections allow you to perform table drop and table insert operations directly on your target database.

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For more details, see Determining Field Width in Characters or Bytes.

In multimode targets, modifications to column names, data types, and sizes are not permitted.

You can set the following target properties for IBM DB2 10 Multimode connections.

Properties are not present in GUI but authored in document.

The following data types are supported for data fields:

The integation platform can write DB2 Loader files. The files can be imported quickly into DB2 using the DB2 Loader utility.

DB2 Loader is available as a source connector. It appears on the source connector list primarily for reading and verifying Loader files created by the integration platform and is useful when you do not have the application to check the files.

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For more details, see Determining Field Width in Characters or Bytes.

No known limitations.

You can set the following source (S) and target (T) properties.

Specifying source properties is important for you if:

The only data type available is Text.

These are field lengths in your data. If you need to change field lengths, reset them in the schema.

The Target Schema icon is active in the toolbar when DB2 Loader is the target connector.

The following options are applicable for DB2 Loader files.

These are field names that appear in your target data table.

You may enter a name for each field.

The only data type available is Text.

These are field lengths in your data. If you need to change field lengths, reset them in the schema.

IBM DB2 Universal Database is a source or target connector that connects directly to IBM DB2 Universal Database, or connects with an ODBC 3.5 driver. Version 9.5 is supported.

This property is set in number of bytes, not characters. For details, see Determining Field Width in Characters or Bytes.

This connector does not support Unicode. Use the ODBC 3.5 connector to enable Unicode support in an IBM DB2 Universal Database.

You can set the following source (S) and target (T) properties in IBM DB2 Universal Database connections.

The CursorType option specifies the type of cursor to use for retrieving records from the source table. The choices available are Forward Only, Static and Dynamic. The default setting is Forward Only. For more information, see Cursors.

In these example statements, we create two indices on the table called "mytable". The first index does not allow duplicates and the index values are stored in ascending order. The second index is a compound index on fields Field2 and Field3.

ConstraintDDL is executed only if replace mode is used for the target. If there are errors, the errors are written to the error and event log file. An error during transformation brings up the transformation error dialog box. To ignore DDL errors, you may continue the transformation.

ConstraintDDL also supports an escaping mechanism to specify DDL in the native SQL of the DBMS. Any statement preceded by an "@" is sent to the DBMS.

The following is a DDL statement for creating a primary key for the table mytable.

Some ODBC drivers do not support the SQL extensions needed to create a primary key with the ODBC variant of the SQL CREATE statement. To create a primary key in these cases, use native SQL.

The following data types are supported:

The length of fields stored in your data file.

DB2 can be a source or target connection either directly or through an ODBC driver. You still need to set up an ODBC data source to make the connection. For more information, see the procedure under ODBC 3.5. Also see IBM DB2 Universal Database 9.5.

The integration platform allows for concurrent writes to multiple target tables. Multimode connections allow you to perform table drop and table insert operations directly on your target database.

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For more details, see Determining Field Width in Characters or Bytes.

In multimode targets, modifications to column names, data types, and sizes are not permitted.

You can set the following target (T) properties.

In these example statements, we create two indices on the table called "mytable". The first index does not allow duplicates and the index values are stored in ascending order. The second index is a compound index on fields Field2 and Field3.

ConstraintDDL is executed only if replace mode is used for the target. If there are errors, the errors are written to the error and event log file. An error during transformation brings up the transformation error dialog box. To ignore DDL errors, you may continue the transformation.

ConstraintDDL also supports an escaping mechanism to specify DDL in the native SQL of the DBMS. Any statement preceded by an "@" is sent to the DBMS.

The following is a DDL statement for creating a primary key for the table mytable.

Some ODBC drivers do not support the SQL extensions needed to create a primary key with the ODBC variant of the SQL CREATE statement. To create a primary key in these cases, use native SQL.

The following data types are supported for data fields:

These are field lengths in your data. If you need to change field lengths, reset them in the schema.

IBM Informix Dynamic Server 10 is a relational database management system. The integration platform can read and write to IBM Informix Dynamic 10 data tables. This connector uses the ODBC 3.5 standard.

You must have IBM Informix Dynamic Server 10 client installed on your system.

This connector sets field width in bytes. What varies is the number of characters that fit into a given field. For details, see Determining Field Width in Characters or Bytes.

No known limitations.

You can set the following source (S) and target (T) properties for IBM Informix Dynamic Server 10 files.

IBM Informix Dynamic Server 11 is a relational database management system. You must have it installed on your system.

The integration platform can write to IBM Informix Dynamic Server 11 target files. It is not available as a source connector. This connector uses the ODBC 3.5 standard.

This connector specifies field width in characters. The width of a field is the number of characters. For more details, see Determining Field Width in Characters or Bytes.

Target Schema Modification: In multimode targets, modifications to column names, data types, and sizes are not permitted.

Query Statement Support: The target format must support query statements (such as Oracle and SQL Server statements) for transformations to work with this connector.

Auto-Increment Fields: If the target table contains an auto-increment column, you may get an error at run time. If this occurs, delete the field that is causing the problem.

You can set the following target (T) properties for IBM Informix Dynamic Server 11.

Informix SQL databases such as Informix Online can be reached using ODBC 3.x. See ODBC 3.x.

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For more details, see Determining Field Width in Characters or Bytes.

If you attempt to connect to an Informix database in the SQL Query Builder, you receive a Microsoft run time error:

Run-time error [Informix][Informix ODBC Driver][Informix]Environment variable INFORMIXSERVER must be set. [Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed.

Solution: Use the Microsoft OLEDB provider for ODBC drivers for the connection to Informix. Then use this connection in the SQL Query Builder.

This connector provides DSN-less source and target connections to the Informix-Online Dynamic Server relational database management system.

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For more details, see Determining Field Width in Characters or Bytes.

Install the following Informix Client SDK version on same system with DataConnect:

Connection strings from the Informix client configuration file can be referenced in the DriverOptions connector property as documented below under the Procedure topic. The following table lists standard Informix connection keywords.

If you try to make a live connection to a database that you do not have direct access to, Map Editor displays a message that the connection is simulated. The simulated mode may be unable to display all property options. To see all property options, install and configure the database client before connecting in the Map Editor.

No known limitations.

 

This connector provides DSN-less source and target connections to the Informix-Online Dynamic Server relational database management system.

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For more details, see Determining Field Width in Characters or Bytes.

This connector is a multimode connector. The main distinction between single and multimode connectors is in the use of event actions:

Use the event actions intended for multimode connectors to enable access to multiple target tables.

Install the following Informix Client SDK version on same system with DataConnect

Connection strings from the Informix client configuration file can be referenced in the DriverOptions connector property as documented below under the Procedure topic. The following table lists standard Informix connection keywords.

If you try to make a live connection to a database that you do not have direct access to, Map Editor displays a message that the connection is simulated. The simulated mode may be unable to display all property options. To see all property options, install and configure the database client before connecting in the Map Editor.

 

The only available data type is text.

The Data Junction Log File connector gives you the ability to read the log file generated by the Map Editor. A script file created in the Extract Schema Designer is used for the source file structure. The log file information can then be written to a specified target file or table.

This connector is also called Integration Log File.

If you are interested in parsing log files that contain process errors and messages, see Process and Transformation LogFile Parser. With this connector, there are more available schema data types, and the ability to parse process logs as well as logs for transformations.

This connector specifies field width in characters. The width of a field is the number of characters. For more details, see Determining Field Width in Characters or Bytes.

Data Junction Log File is not available as a target connector.

 

JavaScript Object Notation (JSON) is a text-based open standard designed for human readable data interchange. It is derived from the JavaScript scripting language for representing simple data structures and associative arrays, called objects. Despite its relationship with JavaScript, it is language-independent with parsers available for many languages. The official Internet media type for JSON is application/json. The JSON filename extension is .json. The JSON format is often used for serializing and transmitting structured data over a network connection. It is used primarily to transmit data between a server and web application, serving as an alternative to XML. "JSON." Wikipedia, The Free Encyclopedia, http://en.wikipedia.org/wiki/JSON (accessed May 11, 2017).

JSON is built on two structures:

The JSON connector allows you to store and connect to your source and target data using the following methods:

 

The supported data types correspond to the fields in the target data table. If you are appending data to an existing table, then the data type of each field uses the data type in the selected table by default.

JSON connector supports the following data types:

A batch response file contains a group of entries for each connector used in a batch operation. Each action in the operation generates a batch response entry. These entries often provide detailed information, such as errors, that can be used for troubleshooting.

The JSON connector automatically builds the schema when the datasets are created (provided there is valid JSON data in the file) based on the following assumptions:

If the JSON_INPUT field is mapped on the target side, the field is a string field and the connector will treat the mapped string as Custom JSON. There are some considerations you need to take into account when using the JSON_INPUT field:

Lightweight Directory Interchange Format (LDIF) is a data interchange format for Lightweight Directory Access Protocol (LDAP), a directory service database. The LDAP database has ObjectClass entries with standard address book information, such as employee names, e-mail addresses, phone and fax numbers, etc.

LDIF files are standard text files (ASCII) that the integration platform can write.

See ASCII (Fixed) target property options

Lotus 123 is a spreadsheet application. With the Lotus 123 connector, you can read and write Lotus 123 DOS and the following Windows releases:

It is important to remember that the integration platform transforms field and record (column and row) oriented data. Title sections, formulas, macros, and display formatting are not transformed. Column headings in a single row can be transformed as field names.

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For example, if a field width is 10 bytes and the encoding is Shift-JIS, the field can hold a minimum of 5 characters and a maximum of 10. This means that if you try to write eight Japanese characters, data is truncated (the Kanji characters all take 2 bytes).

Some basic limitations exist when transforming data to a spreadsheet.

Lotus 123 has a limitation of 255 characters in a single cell. If you try to transform data to Lotus 123 containing more than 255 characters in any field, that data is truncated at the 255th character.

Lotus 123 has a limitation of 254 fields. If you try to transform data to Lotus 123 that has more than 254 fields, the data is truncated at the 254th field.

Lotus 123 has a limitation of 8192 records. If you try to transform data to Lotus 123 that has more than 8192 records, the data is truncated at the 8192nd record.

These are properties that you may need to specify for your source or target data.

All data is transformed to Text in a spreadsheet, unless the user has selected true for the Auto Type Text Fields option in Target Properties.

These are field lengths in your data. If you need to change field lengths, reset them in the schema.

Caution!

Lotus Notes Structured Text is a special file format that can be imported into a Lotus Notes database. With this connector, the integration platform can write to a Lotus Notes Structured Text target file.

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For example, if a field width is 10 bytes and the encoding is Shift-JIS, the field can hold a minimum of 5 characters and a maximum of 10. This means that if you try to write 8 Japanese characters, data is truncated (the Kanji characters all take 2 bytes).

Lotus Notes Structured Text is a legacy format that was developed before there was an official Lotus Notes API. Use this connector to connect to Lotus Notes data earlier than version 4.

You can set the following source (S) and target (T) properties.

All data in a Lotus Notes Structured Text file is Text.

MAILERS+4 is a database application that uses a dBASE format. If the data file references a memo field file, the memo file must exist for the connection to occur. The primary data file usually has a .dbf extension and the memo file usually has a .dbt extension. MAILERS+4 files are structured; for example, both the data and the file structure are stored inside the primary data file. Using this dBASE connector, the integration platform reads and writes MAILERS+4 data files.

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For example, if a field width is 10 bytes and the encoding is Shift-JIS, the field can hold a minimum of 5 characters and a maximum of 10. This means that if you try to write 8 Japanese characters, data is truncated (the Kanji characters all take 2 bytes).

See dBASE IV Property Options.

Microsoft Dynamics AX is an enterprise resource planning software product. This connector reads and writes data from and to Dynamics AX 4 as a web service.

Upserts are not supported at this time.

You can set the following source (S) and target (T) properties.

The following table lists all Microsoft Dynamics AX 4 entities and the supported and unsupported actions.

See the Microsoft Dynamics AX exception log, a valuable resource to consult when something does not work as expected, under Basic > Periodic > Application Integration Framework > Exceptions.

To view the structured schema of a Microsoft Dynamics AX 4 source table, select the Source Schema icon in the toolbar.

To set up the structured schema of a Microsoft Dynamics AX 4 target table, select the Target Schema icon in the toolbar.

These are the data types of the fields in a table. If you are appending data to an existing table, the data type of each field uses the data type in the selected table by default.

The data types displayed for the fields of tables available in Microsoft Dynamics AX are the defaults specified by the web service interface.

Microsoft Dynamics CRM 4.0 is a customer relationship management (CRM) software package that is available in two deployment styles: client-server and online. A client-server deployment is done on-premise within an organization and made available to users within an intranet or on the Internet. An online or cloud-based deployment using Microsoft Dynamics CRM allows users to subscribe to and access an instance from anywhere on the Internet. Both types of deployments expose the application functionalities through a web services interface.

This connector allows users to retrieve, insert, update, and delete data from both Microsoft Dynamics CRM on-premise and online instances.

This connector supports the following versions:

There are two requirements for the Microsoft Dynamics CRM 4.0 connector:

The DataConnect installer automatically registers Microsoft CRM .dll files for .NET Framework version 3.0 and above.

To verify the version installed on your system, select the Microsoft CRM entry in the Add/Remove Programs section of the Windows Control Panel or look in the .NET Framework directory (for example, C:/Windows/Microsoft.NET/Framework). If you have a .NET Framework version other than 3.0 or above, use the following steps to register the Microsoft CRM 4.0 .dll files.

A message stating that the the operation is displayed.

To establish a connection to Microsoft Dynamics CRM 4.0, you must provide the following information:

You can set the following source (S) and target (T) properties.

The following operations are supported by the Microsoft Dynamics CRM Connector.

The Microsoft Dynamics CRM Connector supports standard and custom entities.

These are the data types of the fields in your target data table. If you are appending data to an existing table, the data type of each field uses the data type in the selected table by default. The following data types are available in Microsoft Dynamics CRM 4.0:

A batch response file contains a group of entries for each connector used in a batch operation. Each action in the operation generates a batch response entry. These entries often give detailed information, such as errors, that can be used for troubleshooting.

This option is used to apply filter conditions limiting the fields and rows fetched from source for the selected table.

Microsoft Dynamics CRM 4.0 does not return the field size for all entities. In the case that the field width is not available, the field width will default to 40. If a value, such a text string appears to be truncated, you must manually adjust the field length.

All sorting and filtering is done client side. All records will be read before any filtering or sorting is done.

The current version of Microsoft Dynamics CRM 4.0 supports Windows Live token-based authentication claims based authentication and NTLM authentication. This connector supports Windows Live token based authentication and NTLM authentication since claims based authentication is limited to .NET based application access.

Microsoft Dynamics GP is an accounting software package that runs on top of a Microsoft SQL Server database. Before its purchase by Microsoft, it was called Great Plains Software. This connector provides a means of retrieving and manipulating data in the database back end of a Microsoft Dynamics GP system. It supports connections to versions 9 and 10.

These connectors rely on Microsoft Dynamics GP eConnect, which uses SQL stored procedures to implement business logic rules, validate data for Great Plains, and ensure good performance.

These connectors set field width in bytes. What varies is the number of characters that fit into a given field. For more details, see Determining Field Width in Characters or Bytes.

The following are requirements before you can use the connector.

If you have problems with data truncation, see the Microsoft Dynamics GP Web Services Reference to determine the length of each key field. Then you can manually change the field sizes using the integration platform. You can download this document from the Learn tab of the Microsoft Dynamics GP Developer Center home page.

Date fields. Date fields sent from Microsoft Dynamics GP contain a time value, even though the time value is not relevant for these fields. For example, assume that the web service response contains <DateCreate>2008-07-30 00:00:00</DateCreated>. If you map this field to an ASCII Delimited file with no date conversions, the following data is returned: 7/30/2008 12:00:00 am.

CRUD Operations. The Microsoft interface to Microsoft Dynamics GP data does not support all fields in a Dynamics GP system, and some fields are available only for read access. The create, delete, get, and update methods of the Microsoft interface provide insight into which CRUD operations this connector can perform.

Error Messages. This connector sometimes returns error messages from the originating server that are not useful when troubleshooting server-side issues. Microsoft Dynamics GP offers information that may be more helpful in these situations.

Query Statements. Query statements are not supported in this connector.

For more information on the above limitations, see the Microsoft Dynamics GP Web Services Reference. You can download this document from the Learn tab of the Microsoft Dynamics GP Developer Center home page.

You can set the following source (S) and target (T) properties.

These are the data types of the fields in a table.

If you are appending data to an existing table, the data type of each field uses the data type in the selected table by default.

The data types displayed for the fields of tables available in Microsoft Dynamics GP are the defaults specified by web service interface.

The following sections discuss various options supported in this connector.

The hierarchical data that is delivered from GP is unrolled into a series of flat records. Source child records are associated with their appropriate parents by ordering of the data.

The hierarchical data contained within a top-level record type, such as Customer, falls into three categories:

Record names follow a certain pattern. For instance, if the record name does not start with an open bracket, it is a top-level parent. Otherwise, it uses the following pattern:

where child1 is a child of parent1, child2 is a child of child1, and so on.

For example, sales orders can contain multiple lines and each line can contain multiple freight taxes. In this case, the connector has the following record types:

Hierarchical data can be flattened into a number of fields contained within its parent. This data is not exposed as a separate record type, and is broken down into a series of fields that are attached to its parent.

For example, the Customer record type contains the following fields:

The GP back-end sends a Customer record type containing a CashGLAccountKey with two child fields:

The depth of flattening correlates directly to the number of underscores present in the field name.

Sections of hierarchical data can sometimes differ when data is delivered. The connector creates record types to deal with this. There is a marker record type with the suffix _C, which is a flag saying that the following record type represents the data that was actually delivered. Each of these record types contain a number of record types, which represent the available choices.

In the GLTransaction record type, each GLTransaction can contain [GLTransaction]Lines records.

Each [GLTransaction]Lines instance contains the record type, [GLTransaction!Lines]DebitAmount_C.

Each [GLTransaction!Lines]DebitAmount_C can be followed by either a [GLTransaction!Lines!DebitAmount_C]MoneyAmount or a [GLTransaction!Lines!DebitAmount_C]Quantity.

Assume that you received the following from GP:

The connector produces the following record series:

Some record types contain multiple instances of a grouping of fields. For instance, a GLTransaction can contain many Lines that are treated as record types and are given prefixes that indicate the parents.

For example, assume GP delivers two sales orders and that each order contains two lines. The connector produces the following records:

The record type is [SalesOrder]Lines as opposed to [SalesOrder]Line because of variable names. Remember that a record of type [SalesOrder]Lines represents one line on a sales order.

The Microsoft Internet Information Server (IIS) produces a log file called the IIS Extended Logfile Format file. With this connector, the integration platform can read IIS Extended Logfile Format source files but cannot write them. Therefore, this log file format is not available as a target.

The log file format includes the information in the Microsoft IIS standard log file format, plus user agent and referrer data for Web, Gopher and FTP servers that run under the Microsoft Internet Information Server.

The following is an example of the format:

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For example, if a field width is 10 bytes and the encoding is Shift-JIS, the field can hold a minimum of 5 characters and a maximum of 10. This means that if you try to write 8 Japanese characters, data is truncated (the Kanji characters all take 2 bytes).

The integration platform can read Microsoft IIS Extended Logfile Format files but cannot write them. Therefore, this log file format is not available as a target.

You can set the following source (S) and target (T) properties.

The Microsoft SQL Azure Database is a cloud-based platform that uses SQL Server technologies. The relational database management service leverages the Microsoft distribution data center, which provides enterprise-level security and scalability.

The Microsoft SQL Azure connector allows you to create, read, update, merge, and delete data in Microsoft SQL Azure database tables.

You can set the following source (S) and target (T) properties.

The following data types (the types of the fields in a table) are supported.

Microsoft SQL Azure Database is a cloud-based relational database management service that is based on SQL Server database technologies. Microsoft provides the physical administration of the database management service through its distribution data center.

You can use the Microsoft SQL Azure Multimode connector to write Microsoft SQL Azure database tables. It is not available as a source connector.

Modification to column names, data types, and sizes are not permitted.

You can set the following source (S) and target (T) properties.

The following data types (the types of the fields in a table) are supported:

Massachusetts General Hospital Utility Multi-Programming System (MUMPS), is a programming language that provides tools to support database management systems. MUMPS was originally only used by hospitals to access medical records. Now MUMPS is also used by other entities (such as banks, stock exchanges, travel agencies) where multiple users need to access the same databases at the same time.

Because MUMPS values are stored as text strings, you can share the same MUMPS database between disparate architectures, such as VAX, DEC Alpha, SUN, IBM PC and HP workstations.

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For more details, see Determining Field Width in Characters or Bytes.

MySQL is an open-source, relational database management system. The integration platform connects to MySQL databases using the ODBC 3.5 connector.

For ODBC 3.5 property and source and target schema information, see ODBC 3.5.

Navision Financials is a product offering by Navision Software Inc., an international corporation that specializes in business integration tools. Navision offers solutions for Financial, Management, Distribution, Manufacturing, Customer Relationship Management (CRM), eCommerce and Business Intelligence (BI).

The integration platform connects to Navision Financials tables with ODBC 3.x. For the procedure, and information about the property options, limitations, and source and target schemas, see ODBC 3.x.

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For more details, see Determining Field Width in Characters or Bytes.

Netezza is a data warehouse appliance that integrates a relational database with server and storage hardware. The integration platform connects to Netezza databases using the ODBC 3.5 connector.

This connector sets field width in bytes. What actually varies is the number of characters that fit into a given field. For more details, see Determining Field Width in Characters or Bytes.

Netezza can use three ODBC drivers: 3.0.5, 3.14, and 4.0, with the following limitations and known issues:

These are properties that you may need to specify for your source or target data.

You can set the following source (S) and target (T) properties.

The following example illustrates use of the ConstraintDDL setting.

These statements create two indices on the table mytable. The first index does not allow duplicates and index values are stored in ascending order. The second is a compound index on fields Field2 and Field3.

The ConstraintDDL is executed only if replace mode is used for the target. If there are errors, they are written to the error and event log file. An error during transformation brings up the transformation error dialog box. If you want to ignore the DDL errors, you may continue the transformation.

ConstraintDDL also supports an escaping mechanism that allows users to specify DDL in the native SQL of the DBMS. Any statement that is preceded by an "@" is sent straight to the DBMS.

This connector supports the major Netezza data types.

These are field lengths in your data. If you need to change field lengths, reset them in the schema.

NetSuite is a web-based Customer Relationship Management (CRM) software platform that enables access to information using a Web Services interface. The NetSuite connector supports most entities exposed by the NetSuite Web Service, an open XML-based online platform that provides Simple Object Access Protocol (SOAP) access. This version of NetSuite connector supports WSDL 2018_1_0.

The NetSuite connector supports the following authentication methods to connect to NetSuite:

For record entities, all CRUD operations (including Upsert) are supported. For Regular List names, only read operation is supported.

The Account information that is specified connects you to the required NetSuite instance (sandbox or production instance).

Before using NetSuite connection, the following must be met:

You can set the following source (S) and target (T) properties.

The following are the data types for the fields in your target database table. If you are appending data to an existing table, then the data type of each field uses the data type in the selected table by default.