Moving MultiValued (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 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 SSIS package. |
| ...\ODS\Config |
Contains the configuration files used by the Kore's SQL Accelerator. |
|
...\ODS\Packages |
Contains the SSIS packages created by 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.
- While in the export, use the DDL button to generate a SQL script
to create the SQL table based upon the fields in the export. Paste
that script into the SQL Query tool and execute it.
- While in the export, use the Generate Package button to access
the SSIS configuration screen. Review the parameters in this screen
and click the Generate button to create the SSIS package.
- Review the service created by the KT.SCHEMA2EXPORT
command.
- Remove any export deleted in a prior step.
- Choose a target DSN.
- While in the service, run the service in full refresh mode to
extract the data from U2 and move it to the
...\ODS\Inbox.
- Run the 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 SSIS Master Package
Once you have created and tested all the individual SSIS packages create one
master SSID package that runs all the individual packages. This
allows you to schedule all jobs as a single unit. For a 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.