Related Data Files Guide

It is common in the U2 environment to segment transactional files by a year or some other important piece of information. For example, the current year's General Ledger file might be named GL while the prior year's General Ledger files are named GL-2008 and GL-2007 (this list of files will be used throughout the remainder of this document to illustrate concepts or results). Another common method for this approach is to use "comma files" instead of separate stand-alone files. If using comma files, the archive files might be called GL,GL2008 and GL,GL2007.

The Related Data Files feature in Kourier Integrator for U2 allows you to combine information from related data files into one Microsoft SQL Server table for ease of reporting using just one export specification, saving you time setting up and maintaining you data exports. This guide outlines the steps required to accomplish this goal.

Assumptions Made in the Guide

1. Except for the name of the file, the structure of the data in each file is identical.
2. If dictionaries are used in the export, each file has access to the same dictionary names.
3. To implement this feature, the export must include a field designated as the Source ID.

Setting up a Related Files Export 

1. Enter a list of related files by clicking the icon next to the Export Data Files field. Only one file name should be entered per line.

2. In the Fields section of the export specification, add a field designated as the Source ID. This field must have the following information specified:

• A Format specification (e.g., 4L)
• A Key Code of PK/CK.
• The Source ID checkbox must be selected.
• A formula similar to the one shown below that produces the value of the Source ID. In the example below, the related files list consists of GL, GL-2008 and GL-2007.

  
   001:  YEAR = FIELD(FILENAME$,'-',2)
   002:  IF YEAR = '' THEN YEAR = OCONV(DATE(),'DY')
   003:  FIELD$ = YEAR
  

Line 001 extracts the year from the file that is currently being processed. In Line 002, if the year could not be found (as is the case if the current year's file named GL), then the year will be determined by the current system date. Line 003 returns the year using the standard FIELD$ variable.

Setting up a Related Files Service 

1. In the Target Append Address of the service that drives the related files export(s), use the {%source_id} substitution literal to access the value of the Source ID generated in the export. For example:

          {%source_id}_{%sequence(MR%7)}_{%sql_table}.{%export_mode}
 

What Happens When the Export/Service Runs

When an export specification that has been defined with related files is executed from the service,  the Kourier export engine will automatically process each related file specified in the export by replacing the Select Data File and Export Data File and then executing the export specification.

Using the example above with the related files list consisting of GL, GL-2008 and GL-2007, the resulting datasets produced by the full refresh service via the export specification will be named as shown below:

   2009_0001234_GL.full
   2008_0001235_GL.full
   2007_0001235_GL.full

The resulting datasets are then loaded into SQL using either SSIS or DTS packages. Refer to the Generate SSIS Package help for more information.

User Guide - Table of Contents