Working with Flat Files-Things to Do-BODS

One of the most common scenarios working with SAP Data Services is flat file generating. A lot of questions were asked around how to dynamically generate the series of flat files based on data records. This is mostly depending on the consumed applications and how they require on file formatting. Especially some consumed applications require meaningful filenames for data loading. So, flat files in this blog will be generated in dynamic manner with data element on filename.
The goal of this blog post is to quickly share how I build flow to serve above kind of scenario. This blog is considered as a quick tip for those who would want to work with SAP Data Services File Format.

Requirement

Let’s define the sample requirement on generating flat files contain the data from database table. Each output file will represent the data records from one person, each file will be named with citizen ID of respective person pulling from database table.

Before we get started, here is what you will need:

• SAP Data Services Designer running with access of staging database.
• Sample data on table with “CitizenID” column.
• We are going to generate sequence number for each of the distinct citizen ID. This is needed when working with While Loop.

Step 1: Big Picture

Create new batch job with in your project, we are going to use this job to generate the files. This job contains Data Flow, Script, and While Loop objects.

Step 2: Source Table

Let us first see the table structure of source table, Citizen_Expense, with the sample data on it. All data is random. There is no primary key or any constraints on this table. Please take this as a sample structure. In real life, database tables are normalized and they do not look like this. You may need to do some joins or transforms to come up with this kind of result. Bear with me if my sample data seems not to be relevant, it is just for demonstration.

Step 3: Generate Sequence Number

In the first Data Flow, you will make use of template table to create temporary database table for distinct set of CitizenID together with sequence numbers. The sequence numbers will be used for While Loop transform that match to each particular CitizenID.

My source table is “Citizen_Expense”. And it has been queried for distinct value of CitizenID.

Then you will generate sequence number using gen_row_num() function.

Expected data on template table contains two columns :- CitizenID and SeqNumber.

Step 4: Define Variables

Let us define the job-level variables as below. You are going to need these variables for While Loop. (Click on your job name in “Project Area” then hit the menu “Tools” > “Variables”, just in case your Designer’s layout does not show the “Variables and Parameters” area by default.)

Step 5: Prepare Initial Script for While Loop Transform

Then put simple two commands to initialize the variables in the script. The “cast” function is used to explicitly convert an expression SQL result from one data type (character in this case) to another data type (integer as we need).

Step 6: While Loop Design

Inside the While Loop transform, let us add the design to generate flat files on each CitizenID with the logic of adding count number repeatedly until reach the target record number. So add Script object and another Data Flow and link them together.

The script will increase sequence number by 1 on every loop. Then it query for CitizenID value from table to find the working CitizenID on that particular loop. In my case, I intended to put “ID” as the prefix for Filename, just to show you how to customize a bit of filename generation.

If you validate the statement at this point, you may face the warning messages from the script because of the conversion from integer to character in SQL statement, ignore it. Job will be running fine.

Next we will define input parameters at dataflow level to be used when generate flat file for each CitizenID.

Then assign variable values to these parameters at While Loop level.

From this point, let us go inside the Data Flow and put the source table with Query object, link them together.

Go ahead map the output column as wish. The important thing is Where condition in this query comparing the CitizenID on each loop.

Here is another trick to easily create flat file format based on data structure from query instead of doing it manually. Right click on Query output and click on “Create File Format …”.

Then we put this Flat Files as a target. And define File name property to the dataflow parameter assigned earlier.

Step 7: Check the Result

Finally all those flat files are generated by executing job. Depending on what you define in target file’s properties, file location, column delimiters, etc. Do customization your formats based on consumed application’s requirements.

Final Thoughts

So this is just a quick tip for using SAP Data Services generate flat files dynamically. I do really hope this is useful and gives you basic idea of working with file formats. Then you could apply this to real-life scenarios which I believe they are much more complex.