Moving Multi-Valued (MV) Data to SQL Server

The information contained in this guide outlines the steps required to move a MV file to Microsoft SQL server using Kore's SQL Accelerator and Kourier products. We suggest the following directory structure to organize the various files used in this process.

Directory	Usage
...\ODS\Inbox	Contains the datasets to be imported by the SQL DTS/SSIS packages. This directory must be visible to both your MV server and the SQL server.
...\ODS\Logs	Contains the logs files generated by each DTS/SSIS package.
...\ODS\Config	Contains the configuration files used by the Kore's SQL Accelerator.
...\ODS\Schema	Contains schema information generated by the quick-start tools.

Assumptions made in the Guide

The column names used in the Kourier export must match the column names in the target SQL table.
The name of the Kourier export will match the name of the target SQL table.
The name of the export file will follow the naming convention of: 2n_7n_sql_table.export_mode
A DSN has been created that points to the ...\ODS\Inbox.
An Integration ID has been created with a SITE_ID property.

Exporting a MV File to SQL Server

In most MV systems, the dictionaries for a file do not accurately reflect the structure of the data. Use the KT.FILEUSAGE tool to analyze the data in a file and compare it to the data defining dictionaries (D-type) for the file. .

Review and edit the results of the KT.FILEUSAGE command in Microsoft Excel using this procedure.

Use the KT.SCHEMA2EXPORT command to build the Kourier exports for a file. An export will be created for all single-valued fields (the parent export), all multi-valued fields and all associated multi-valued fields (the child exports). Each export will be mapped directly into a single SQL table. A Kourier Service will also be created to run the exports created by this command.

Review each export created by the KT.SCHEMA2EXPORT command. The overall goal of each export is to take advantage of the power of SQL without carrying along MV database conventions that work against you in the SQL environment.

Delete any unwanted fields from each export.
Review the column headings.
Make sure the selection criteria for each export contains valid dictionary references.
If more than one MV account will be sending data to the same data warehouse, add a line item for a site identifier at the beginning of each export.
If a field (especially the item-id) for a MV file is a compound field, add a line item for each part of the field. Use the conversion, format and associations fields to set data types, data lengths and primary keys (use the special association named PK).
Eliminate any child export that solely contains cross-reference information. Use the SQL join capability to get at this information.
For multi-valued comment or text fields, delete the generated export and use the @TEXT conversion code to convert the multi-valued text to a single string and add that field to the parent export.
For multi-valued fields that contain a fixed number of multi-values (i.e. 3 address lines) considering adding these fields to the parent export instead of treating them as a child export. Use the conversion, format fields to set data types, data lengths.

Review the service created by the KT.SCHEMA2EXPORT command.

Remove any export deleted in the prior step.
Correct the EXPORT-ID value to reference the correct SQL table name.
Choose a target DSN.

While in the service, use the DDL button to create a SQL script that can be pasted into the SQL Query tool to create the SQL table(s) based upon the fields referenced in each export.

While in the service, use the RUN button to run the service. This will execute each export and move the result of each export to the ...\ODS\Inbox.

Use the Accelerator to create the DTS/SSIS packages by selecting the BATCH button and selecting the files sent to the ...\ODS\Inbox in step 7. Make sure to change any file path name containing a drive letter to a UNC path name. This will allow the DTS/SSIS job to be run from any SQL server instance provided that server has access to the UNC path.

Run the DTS /SSIS job to load the exported data. Resolve any errors that may occur. The most common errors are:

Duplicate primary keys caused by the fact that MV databases are case-sensitive and SQL tables (by default) is case-insensitive.
Data truncation caused by the data in MV database exceeding the defined length of a column in the SQL table.
Wrong data types.

Repeat the above steps on each MV file you plan on moving to SQL Server.

Create the DTS/SSIS Master Package

Once you have created and tested all the individual DTS/SSID packages create one master DTS/SSID package that runs all the individual packages. This allows you to schedule all jobs as a single unit. For the DTS master package, make sure to limit the maximum number of tasks executed in parallel to 1. This setting can be found on the General tab in the package properties.