Multiple Flat File Load in single Mapping
In This Scenario I’m trying
to explain how to load multiple flat file in single mapping, these files are having same structure.
Here I’m taking indirect
method from session properties and on top of it loading file name in target, so that we can validate if file
is already loaded.
If file is already present in target then will load that data into junk target table.
Step 1: we have to create
source flat file definition and import relational target definition.
Flat file is delimited file and delimiter is “,” here we need some additional
setting in source definition for this we need to check “Add Currently Processed Flat
File Name Port “ option in
source definition properties.
So that we can keep track of
loaded file into target.
Step 2: Create Expression
transformation to cleanse data into mapping b’coz source data is flat file. In
flat file data type always string data type and our target is relational for
this data need to converted in desired data types.
Below are some of the sample
expression in expression transformation
EMPNO_o: TO_INTEGER(LTRIM(RTRIM(EMPNO)))
ENAME_o: LTRIM(RTRIM(ENAME))
JOB_o: LTRIM(RTRIM(JOB))
MGR_o: TO_INTEGER(LTRIM(RTRIM(MGR)))
HIREDATE_o: TO_DATE(HIREDATE, 'MM/DD/YYYY')
SAL_o: TO_INTEGER(LTRIM(RTRIM(SAL)))
COMM_o:
TO_INTEGER(LTRIM(RTRIM(COMM)))
DEPTNO_o: TO_INTEGER(LTRIM(RTRIM(DEPTNO)))
CurrentProcessFile: SUBSTR(CurrentlyProcessedFileName,INSTR(CurrentlyProcessedFileName,'\',1,2)+1)
Step 3: We have to lookup
target table “TGT_MULTILOAD” to validate if file is loaded already or not.
Here we will be comparing CurrentProcessFile from source with file
loaded into target i.e. “FileName”
column from target table. Based on this lookup will return FileName from
target.
Step4: Here we will create
Router T/R which will help to Route our data into Target table “TGT_MULTILOAD” and Junk Target TGT_JUNKFILE_LOAD as per our requirement.
In Router we will specify
condition in two group
Group1: For “TGT_MULTILOAD” condition will be
NewFile: ISNULL(
FILE_NAME)
Group2: for “TGT_JUNKFILE_LOAD”
condition will be
ExistingFile: CurrentProcessFile = FILE_NAME
Here mapping will look like below
Step4: Now we have to Specify
Source and target connections in session, In case of Source is flat file we have to
specify informatica server source directory path & source file name under mapping Tab in session.
SourceFileType: Indirect
Source file
directory: $PMInfa_FlatFile_Path/$PMInfaSource_FlatFile
Source file
name: $PMInfa_ListFile_Name
Here we have
to create command task and write UNIX script to write all files present in
source directory into list file.
Before
starting session it will keep all files into list file, we will specify source
path and file names in parameter files
########################
ls –tr $PMInfa_FlatFile_Path/$PMInfaSource_FlatFile
> $PMInfa_FlatFile_Path/$PMInfa_ListFile_Name
if [ ! –s $PMInfa_FlatFile_Path/$PMInfa_ListFile_Name
then
echo date
+%m-%d-%y !%H :%M:%S “No file present in directory” >> $LogPath/$LogFile
echo “No
updated File available in directory” | mailx
-s “no file to process” $Email
fi
##########################
##########################
Parameter
files contain all variable values
$PMInfa_FlatFile_Path:
/InfaServer/dev/etl/ewbs/sourcefile
$PMInfaSource_FlatFile:
abc_*.*
$PMInfa_ListFile_Name:
FlatFile.lst
$Email:
L4support@abc.com
$LogPath: /InfaServer/dev/etl/ewbs/log
$LogFile:
logupdates.log
Now will
specify Target connections and other required setting in session properties.
Now let’s
run session and verify target
list file contain two files and target table "TGT_JUNKFILE_LOAD" will be empty
1. test.txt
2. test1.txt
TGT_MULTILOAD
Update some flat file(test2.txt, test3.txt) in source directory and verify target tables
In Next run list file contains
1. test.txt
2. test1.txt
3. test2.txt
4. test3.txt
TGT_MULTILOAD
TGT_JUNKFILE_LOAD
With this i'm done with Multiple flat file load in single mapping, please let me know you question and concern.
No comments:
Post a Comment