How Do I Handle Flat Files with Variable Names? 7-1
7
How Do I Handle Flat Files with Variable
Names?
Scenario
Your company relies on a legacy system that writes data to a flat file on a daily basis
and assigns a unique name to the file based on the date and time of its creation. You
would like to create a mapping that uses the generated flat files as a source and
transforms and loads the data to a relational database. However, mappings require
files to have permanent names and, in this situation, the name of the source file
changes each time the file is created.
Solution
In Warehouse Builder, you can design a process flow that locates the generated file in
a specific directory, renames it to a permanent name you designate, and launches a
dependent mapping. You can now use the permanent flat file name as the source for
your mapping.
Case Study
This case study describes how to create a process flow and a mapping to extract data
from a legacy system that generates flat files with variable names. The process flow
relies on the use of an external process activity. Assume the following information for
the purposes of this case study:
■ Generated Flat File: The legacy system generates a flat file containing sales data
on a daily basis. It saves the file to the c:\staging_files directory and names
the file based on the time and date, such as sales010520041154.dat. Every
generated file is saved to the same directory and begins with the word sales,
followed by the timestamp information.
■
Permanent Flat File Name: You decide to rename the generated file name to
s_data.dat. This is the name you reference as the flat file source in the
mapping.
■
Process Activity: You design a process flow named OWF_EXT to execute batch
commands in DOS that copies the generated file, saves it as s_data.dat, and
deletes the originally generated file.
Your objective is to create logic that ensures the generated flat file is renamed
appropriately before it triggers the execution of a mapping.
To extract data from a genera