Working with Data Services Flatfiles

Requirement 1: Let us look into the first requirement: Generate respective flatfiles containing employee information for each department. So for each and every department we will generate flatfiles in a dynamic manner from our employee database table.

• First of all we will create a Job in Data Services. The main objective is to generate flat files dynamically using WHILE LOOP transform in Data Services. This is how the final implementation Job will look like:

• Now we will design a Dataflow to populate a temporary database template table to populate the distinct set of available Departments present in the EMP table. This table will contain the list of values of the departments together with a sequence number. We will use this sequence number to loop through the available LOV's using the While Loop transform.

• Select the EMP database table as Source. Next place a Query transform to select the DISTINCT department numbers present in the EMP table.

• Next we will generate sequence number for each of the distinct departments using the in-built Data Services function gen_row_num()

• Lastly we place a template table as the target table to load the distinct set of departments in the employee table.

• Next we define the following Variables and initialize the variable values to be used in WHILE loop.

• Next we set the variable $INIT_CNT to 0. This variable will be used as a counter variable of the While Loop. The variable $LOOP_CNT is set to the maximum values of the Departments in the Employee table. This will be the number of times the while loop will execute which in turn will generate the respective employee flat files for each departments.

• Now we take a look inside the WHILE Loop and see the final implementation logic to generate dynamic flat files for each department. The logic of the while loop is simple, that is it will iterate as many times as the distinct set of departments.

• The below script sets the Department Number and the corresponding file that needs to be generated dynamically by the next following Dataflow. Here the variable initialization counter is incremented by 1. Next the variable $DEPTNO is set to the first depratment value as present in the LOV department table we loaded earlier. Also the corresponding filename to be generated is set to the variable $FILENAME. So with each iteration of the WHILE loop it will set the department for which it has to extract the data from the employee table and generate the corresponding department file.

• Next we go to the final dataflow which will extract the data from the EMP table and generate flatfiles for each department. Before that in order to pass the variable values we set earlier to the dataflow, we define the following Input Parameters at the Dataflow level and also assign the variable values to these parameters at Workflow level.

• Now the final dataflow looks like below. We have the EMP table as source followed by a Query transform and lastly the flat file target.

• In the Query transform we filter the employee records for the department number as set in the Dataflow Parameter. Also additionally we generate a sequence or serial number of the employees in each of the flat files.

• Lastly we place the file-format of the generated employee file for each department. Note we need to set the File name property of the file-format to the dataflow Parameter we assigned earlier.

We are done with our first requirement.

Requirement 2: Now we try the reverse scenario i.e. load the database employee table with the information in the form of flatfiles received from source systems. These flatfiles have employee information based on each department; Also the number of files or departments is not know in Advance. Note, that the part of the flatfile name bears the dept information that also need to be mapped to deptno column of the employee table.

Here we go:

• We will design a dataflow to read all the input flatfiles having employee information extract the department number information from the filename and populate to the target relational table. Below is how the dataflow looks like:

• First of all we place the fileformat as our source. Note the filename is using wild character search. i.e. DEPT*.txt; which means it will read all files in the given root directory having filename starting with DEPT with extension .txt. Next the Include file name column property of the Source Information of the file format is set to Yes with column name as FILENAME. This will help to read the filename information and pass in the dataflow transform.

• Next we use the Query Transform to format the Dataset. We will extract the department number from the filename itself, as shown below:

• Lastly we place the Target Template table.

Hence our second requirement is also done.