DWBI CONCEPTS

How to handle multiple input file in SAP Data Services

Details

Category: ETL BODS

Last Updated on Wednesday, 10 October 2012 04:49

Written by Saurav Mitra

In this tutorial, we will learn how we can use a single data flow to process multiple input files of same structure using SAP Data Services. Like before, we have included one hands-on video with the tutorial to help you see the technique in action

Multiple File Handling Requirement

In a typical ETL domian, we often need to process more than one file with exact same structure. As an example, consider loading accounts information from multiple bank branches or sales information coming from multiple store outlets. The structure of these files are essentially same and that is why we can leverage a single data flow to load these files instead of creating separate data flows for separate files.

Most of the ETL tools allow you to do this in some way or other (for example, Informatica has an indirect file loading option). We will see how to do this in SAP data services here.

Method of handling multiple files

Suppose you have several files in your source data directory and you want to create a job that will process all these files one by one. In order to do this, we will set some properties in the "Format" tab of File Format Editor. Open the File Format Editor and set the following values,

• Put a star (" * ") in the File Name property. This will allow data services to read all the files in the directory.
• If you want to keep track of the file names from which data is coming (recommended) set the property Include File Name to yes. This will add a new column DI_FILENAME in the output schema containing the name of the file.
• Optionally, based on your requirement you may set Include Path property to yes or No.

That's it. That would process all the files in the source file directory one by one. Please see the below video to understand better.

Hands-on Video showing multiple file processing in BODS

About the Author

Saurav Mitra is a business intelligence professional.
Connect with the author via Google+