U2 to SQL Export Specification

Purpose

Define U2 to SQL Export Specifications, which are used only to extract data from UniData or UniVerse (U2) to Microsoft SQL Server.

A U2 to SQL Export Specification has two parts: selection and export. The selection portion of an export allows you to specify any selection criteria to apply to the data before the data export file is created. The export portion lists the fields to be included in the data export file, the column headings to use, and any data conversion required.

If you are not exporting data from U2 to SQL Server, use the or one of the other export specification types to define your exports from/to other file formats.

Here's the typical workflow when creating a U2 to SQL Export Specification:

Create an export and add one or more line items.

Use the Test command to verify the export is working correctly.

Generate the DDL for the export, use this to create the target SQL table.

Create the SSIS package with the Generate Package command.

An export is typically automated and processed as part of a Kourier Service using the command KMK.EXPORT.

Toolbar Commands

Toolbar Commands Help

Click the New icon in the toolbar to save the current export specification and then clear the page making it ready for a new item.

Click the Save icon in the toolbar to save the current export specification and return to the Export Specifications listing.

Click the Save and Stay icon in the toolbar to save the current export specification and continue working.

Click the Save As icon in the toolbar to save the current export specification to a new name. You will be prompted to enter a new name. Click OK to save the export to a new name and then continue editing the export. Click Cancel to return to the existing export without saving.

Click the Delete icon in the toolbar to delete the current export specification. You will be prompted with a "Are you sure you want to delete?" dialog. Clicking Yes will permanently delete the export and then return to the Export Specifications listing. Click Cancel to return to the existing export without deleting.

Click the Test icon in the toolbar to test an export. Testing an export will perform a sample data extract using all of the information entered on the export specification and then outputs the results to the screen where it can be viewed and validated. Typically a test is performed to ensure that the correct data fields are being extracted and all data conversions, formats, and formulas are working correctly.

After clicking the Test icon, you will see a dialog requesting the following additional information:

Export file to test: Select one of the export data files to use in the test. The first export data file entered is selected by default. Note, this prompt will only be available if multiple data export files have been entered on the export specification.
Sample Number of records: Defaults to 100. This number determines how many records are processed and exported to the screen.
Record IDs to export: If you want to test individual records, enter one or more item ids. Entering a value here overrides the sample number of records.
Export Format: Select the export format for viewing the test data.

Select MultiValue to view the exported data in a detailed data view which shows the data for each field in its native MultiValue format (including value and sub-value delimiters) and without processing it through formats, conversions, etc. This is very useful if you need to see exactly what exported data looks like, which may help you understand why the export format, conversions or formulas are not working.

Click the OK button to view the export results. Click the Clear button to clear the fields so you can enter new test parameters. Click the Cancel button to return to the Export Specification page.

For this feature to work correctly you will need to: 1) Add the Kourier website as a trusted site so pop-up dialogs appear and 2) enable the automatic prompting for downloads option in your browser's internet options (e.g. for Internet Explorer this setting is found in Security | Custom Level dialog).

To see the runtime statistics for an individual export, click the Statistics icon from the toolbar. This will take you to the Export Statistics listing which displays all export statistics that have been captured for the export.

To create the DDL from a U2 to SQL export specification, you may click the DDL button from the tool bar. Once the DDL has been created a dialog appears allowing you to view the DDL. This feature is useful when you need to only create the DDL for an single export instead of all of the exports associated with a master file.

To create the SSIS Package from a U2 to SQL Export Specification, you may click the Generate Package button from the tool bar. Note, your current U2 to SQL Export Specification will be saved before you can create the SSIS package. This will open a new Generate SSIS Package page where you will be asked to enter a number of parameters before the SSIS package can be generated.

Allows you to flag an SSIS package as complete.

Click the Help balloon to open the help for this page.

File/Key Field

KT_Exports/Export Name

Prerequisites

If an export contains user-defined literals, they should be entered using Substitution Literals.

Fields

Field Name

Description - Usage - Notes

Name

Enter a unique name for the export. By convention, exports are named using an Integration ID such as KS for KommerceServer, followed by an underscore and a string that describes its purpose (e.g. KS_CONTACTS).

Title

Enter a short descriptive name for this export. This name is displayed in on-line lookups.

Enable Net Change

When Net Change is enabled any add, change, or delete of a record in the file referenced in the Export Data File field will be tracked. Kourier tracks these changes by creating an RDBMS index named KCL_INDEX. A subfile is created under the KT_CHGLOG file using the same name as the file referenced in the Export Data File field with the string _LOG appended to that file name.

Only files that are defined locally to the current account may have net change enabled.

You may also enable Net Change with Checksum, which provides an additional level of filtering when records are exported. This feature is useful if your application writes records to the file referenced but those changes may not be of interest to the target application.

Last Update

Display only field, shows the last date/time that the export specification was saved.

Last Package Generation

Display only field, shows the last date/time that the associated SSIS package was successfully generated for this export specification.

Last Run

Display only field, shows the last date/time that this export was run regardless of if any data was output.

Note

Enter a free form comment or note for this Export Specification. If a note is entered, a small icon will display on the Export Specification listing. Hovering your mouse over the icon on that listing will display about 40 characters of the note for quick reference.

Select Verb

Enter a valid verb to perform the selection, such as select or sselect.

For UniData systems, ECLTYPE “P” and ECLTYPE “U” syntax is supported. For ECLTYPE “P”, use the upper case version of the select verb. For ECLTYPE “U”, use the lower case version of the select verb.

The export processor first processes the selection portion of the screen, and then passes the selected items to the export section. The selection portion of the screen can be used to limit the number of records exported.

Select Data File

Enter the name of the data file to use to select data records for exporting.

Using File

Enter the name of the file that contains the dictionary items referenced in the select statement.

To use the dictionary file with the same name as the data file, leave this field empty. For example, if you enter CUSTOMER as the data file and do not enter a using file, the CUSTOMER dictionary file is used.

To specify an alternate dictionary file, enter DICT and the file name.

To specify a data file that contains dictionary items you want to use, enter the file name.

Select Statement

Enter the selection criteria statement using standard U2 query syntax. This field may also contain substitution tags.

For example, if you want to select all of the CUSTOMER records with PHONE beginning with 619, enter WITH PHONE = "619]" (if your system runs in Pick mode) or WITH PHONE LIKE "619..." (if your system runs in native or Ideal mode) in the Statement field.

If your select statement uses a dynamic substitution tag that will be specified in the Command field in a Service by including the HEADERS keyword, you must specify a default value for the substitution tag so that the Test capability works correctly. For example, if your HEADERS expression was HEADERS "StartDate=01-01-15" then your substitution expression in the export should be {%StartDate(S;*;'01-01-14'}.

Export Data Files

Enter the name of the data file to use to export data records. To enter a list of related export files click the drop-down icon.

Note: If more than one file is entered, the field will be disabled (grayed out) and only the primary file name will be displayed, followed by the total number of files in brackets. For example, if the primary file is GL, and there are two additional related GL files, the field will be disabled and display as "GL [3]". Use the drop-down icon to edit the list of files. Refer to the Related Data Files Guide for more information on using this feature.

Using File

Enter the name of the file that contains the dictionary items referenced in the export statement.

To use the dictionary file with the same name as the export data file, leave this field empty. For example, if you enter CUSTOMER as the data file and do not enter a using file, the CUSTOMER dictionary file is used.

To specify an alternate dictionary file, enter DICT and the file name.

To specify a data file that contains dictionary items you want to use, enter the file name.

SQL Table

Enter the name of the SQL Server table to be used as the destination of the exported data. This name will be used in any DDL that is generated and when generating SSIS packages for this export.

Export Verb Requires Select List

If enabled, then an active select list (or an empty selection criteria) must be present before the export statement will be executed. If disabled, the export statement should be processed even if an active select list is not present.

Export Options

Enter any valid export options.

Option	Usage
COL.HDR.SUPP	Use this option to suppress columnar headings. Syntax is: COL.HDR.SUPP
EXPORT-SUBNAME	Used by the KEXPORT command to specify the name of the export subroutine associated to the export. Syntax is: EXPORT-SUBNAME "subroutinename".
INCLUDE-DELETES	Use this option when running a point-in-time style export in net change mode to include physically deleted records into the .net file.

Event Handler

Refer to the Export Specification Event Handlers topic for detailed information on Event Handlers.

Line Item Fields

Click the Add button in the line item section command bar to add a new line item field. See help for U2 to SQL Export Specification Line Item