ODI TOOLS - ODIZIP AND ODIFILEDELETE

Scenario:

Everyday we will get three files (i.e., *_sysdate.csv) we need to load those files and then we need to zip those files and place in one specified folder after that we need to remove from source directory.

Procedure:

Step 1:
Upto loading those files, steps are explained in my previous post

Step 2:

• Create a new package
• Create a variable and go to refresh tab provide "SELECT TO_CHAR(SYSDATE,'DD_MM_YYYY') FROM DUAL" and select appropriate logical schema
• Drag and drop variable two times -- Select first variable as declare and second variable as refresh variable.
• Drag and drop procedure
• Drag and drop ODIZip and ODIFileDelete from package toolbox

Step 3:

• Select ODIZip Tool in the package and enter following details
• SourceFileDirectory : <Directory Location>\*#<PROJECT_NAME>.<VARIABLE_NAME>.csv
• ZIP File : <ZIP File Directory Location>\#<PROJECT_NAME>.<VARIABLE_NAME>.ZIP and leave remaining as it

Step 4:

• Select ODIFileDelete tool in the package and enter following details
• Directory: <Directory Location> and FileName : *#<PROJECT_NAME>.<VARIABLE_NAME>.csv and leave remaining as it

Step 5:

     Connect all the components in sequencial order variable_declare--variable_refresh--procedure--ODIZip--ODIFileDelete

Completed Successfully!!!

How to load multiple files(of same structure) to single target table using single interface/Mapping in ODI?

How to load multiple files (of same structure) to single target table using single interface/Mapping in ODI?

Pre-requisities:

File Structure of all the files to be same.

Naming conversion of the file should be decide earlier.

In this example, I am considering sample1,sample2 and sample3 as naming conversion of the file and File Structure as follows for all the files.

ID,PROJECT_NAME (.csv delimited with ',')

Procedure:

Step 1:

Topology Configuration

Create Physical Architecture for Target 

• Expand Technologies -- Right click Oracle Technology -- Click on New Dataserver
• Provide DB details and save it
• Right click the Dataserver and select Select New Physical Schema and provide Schema details

Create Physical Architecture for Source

• Expand Technologies -- Right click File Technology -- Click on New Dataserver
• Provide File server details and save it
• Right click the Dataserver and select Select New Physical Schema and provide Folder location

Step 2:

Create Logical Architecture for Target

• Expand Technologies -- Right click Oracle Technology -- select New Logical Schema
• Provide Logical Schema Name (whatever you want)

Create Logical Architecture for Source

• Expand Technologies -- Right click FileTechnology -- select New Logical Schema
• Provide Logical Schema Name (whatever you want)

Step 3:

Create Context

• Click on New Context
• Provide Context Name (whatever you want)
• Go to Schemas tab and Select Physical Schema for appropriate Logical Schema(

Step 4:

Create Target Model 

• Click New Model
• Provide Name , Select Technology as Oracle and Select Logical Schema which you created earlier.
• Go to Reverse Engineering Tab -- Select Context which we created earlier and provide table name for "Mask" input text box
• Go to selective Reverse Engineering tab and select appropriate table and click on Reverse Engineering
• Your datastore is created successfully

Create Source Model

• Click New Model
• Provide Name , Select Technology as File and Select Logical Schema which you created earlier.
• Right on your Model and select New Datastore
• Provide name and select the sample1.csv file
• Go to Files tab -- File Format -- Delimited and Header -- 1
• Under Field Separator -- Others -- provide ','
• Go to Attributes and click on reverse Engineering
• Go to Definition -- Resource Name : Sample#<PROJECT_NAME>.<VARIABLE_NAME>.csv and save it

Step 5:

Create Project

• Create new mapping
• Drag and drop both source and target datastores. Link the source output connector to target input connector. Do mappings as per your requirement
• Go to Physical -- Select staging area and select KM as LKM File to SQL and Select Target datatstore and select KM as IKM SQL control Append

IMPORTANT (MAIN LOGIC)

Step 6:

Create Package

• Create Variable in your project
• Create Package and drag and drop your variable and mapping which you created earlier
• Connect 'OK' from variable to Mapping
• Generate Scenario to that package

Step 7:

Create Procedure

• Create Proceudre, In target side select technology as ODI tools and for Source side select Oracle as technology and select logical schema as well.
• Target side : OdiStartScen "-SCEN_NAME=PKG_MULTI_FILES_LOAD" "-SCEN_VERSION=001" -<PROJECT_NAME>.<VARIABLE_NAME>=#FN
• Source Side : SELECT REGEXP_SUBSTR('1,2,3','[^,]+',1,LEVEL) FN FROM DUALCONNECT BY LEVEL <= LENGTH(REGEXP_REPLACE('1,2,3','[^,]+'))+1
• Select appropriate logical Schema for Source Technology and save it

Completed Succesfully!!!

Input Files:

Sample1 

ID,PROJECT_NAME

1,QUOTING&CONTRACT

2,EMPR

Sample2

ID,PROJECT_NAME

3,CONVERSIONS

4,DWH

Sample3

ID,PROJECT_NAME

5,SUPPORT

6,EAM_DM

Output:

Sample

How to pass runtime value to a package in ODI?

For Example:
Organization : 'X'
Child Organization : 'A'
Second Child Organization : 'B'
Third Child Organization : 'C'
Requirement I need to pass runtime child Organization name to my package. That means whenever I am executing package I need to mention what is child Organization we are going to load now.

Solution:
Step 1:
Create a variable V_CHILD_ORGANIZATION

Step 2:
Create a mapping whose child_organization (i.e., target column name) mapping should be #V_CHILD_ORGANIZATION

Step 3:
Create a package and drag and drop that variable (V_CHILD_ORGANIZATION) and drag and drop Mapping

Step 4:
Select that variable and make it as declare variable and select 'ok' control flow and link the variable and mapping.

Step 5:
Generate Scenario for that package and select the variable which there in that package while generating scenario

Successfully Created!!!

Execution:
Execute that scenario with corresponding context and agent then it prompt for Variable value.
If you want to change the value then you need to disable the latest value check box and you can provide whatever value you want (i.e., 'A' or 'B' or 'C')