A 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.
 |
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 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 additional information:
- 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.
- View test results in native MultiValue Format: click the
checkbox 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 the data you are exporting looks like which may
help you understand why export format, conversions, or formulas are
not working.
Click the OK button to view the export results
or click the Cancel button to return to the Export Specification
page. |
 |
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 an 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 SQL Export Specification, you may click the
Generate Package button from the tool bar. Note,
your current 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. |
 |
Click the Help balloon to open the help for this page. |
| 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. |
| Export Format |
Enter the export format. If the export verb is KEXPORT, the possible
choices are shown below. Other export verbs (normally user-written
BASIC programs) may or may not support these formats.
| Format |
Description |
| TAB |
Data values are delimited by the tab character.
Use this format to generate files that can be automatically
loaded in the Excel or other PC applications. This format
must be used when using the Generate SSIS packages feature. |
| PIPE |
Data values are delimited by the pipe character.
This format is primarily used when debugging the output. Note,
you will not be able to generate SSIS packages using the PIPE
format. |
|
| 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. |
| Select Verb |
Enter a valid verb to perform the selection, such as
SELECT or SSELECT.
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. |
| Select 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.
For example, if you want to select all of the
CUSTOMER records with PHONE 619], enter WITH PHONE 619]
in the Statement field. This field may also contain
substitution tags. |
| Export Data File |
Enter the name of the data file to use to export data records. |
| Export 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 the 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
processes 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-REPEAT-VALUES |
If this option is specified, Kourier will repeat the last non-null value
or sub-value of a field. Syntax is: EXPORT-REPEAT-VALUES |
|
EXPORT-STAMP |
Used by the KEXPORT command to specify the attribute which should receive
a time/date stamp when a record is exported. Syntax is:
EXPORT-STAMP "amc". |
|
EXPORT-SUBNAME |
Used by the KEXPORT command to specify the
name of the export subroutine associated to the export. Syntax is:
EXPORT-SUBNAME "subroutinename". |
|
XML-FORM-TAG |
When creating an XML export, this option indicates the form tag of the
XML document. Syntax is: XML-FORM-TAG "elementname". |
|
XML-ROOT-TAG |
When creating an XML export, this option indicates the root element tag
of the XML document. Syntax is: XML-ROOT-TAG "elementname". Default value is: Kourier file=export_data_file |
|
XML-TRANS-TAG |
When creating an XML export, this option indicates the transaction tag
of the XML document. Syntax is: XML-TRANS-TAG "elementname". Default value is: Trans id=@ID |
|