Today I will try to demonstrate the usage of ODI Packages, scheduling runs for those packages and using variables to dynamically access different data sources every day.
In the end, different statistics and automatic emails can be generated to achieve basically everything which can be achieved through an SQL Query in a database :).
Scenario:
- Read a .TXT file which name changes everyday with current date. (test20190811.txt)
- Populate an oracle table everyday except for Saturday and Sunday
- Generate a .csv file from that table with name and date mask (test20190811.csv)
- Attach that file to an email and send that email everyday to a recipient.
- Show row number of the populated table in email.
Firstly we need to create a variable which obtains the value of current date.
That can be done through a simple query in Oracle:
SELECT to_char(sysdate,'YYYYMMDD') from dual;
The variable will be named VAR1, and the Schema provided can be any schema since I am not selecting a particular table but I am selecting from dual which is a built in oracle table. (Otherwise if you need to select from a specific table, the schema where that table is created has to be provided).
Make sure to test the value of the variable using the refresh logo on top right corner, after clicking it, navigate to history tab on the left top corner and check the value if it corresponds to today's date and desired format (in my case YYYYMMDD).
Now that we have created the variable which will do the work, we have to create a new interface which will read the file and populate an non-existing oracle table which will be created during the first run of the interface.
As mentioned, I will be reading a source file .txt named test20190811 (date is the file mask which will change everyday according to current date). Create a new data store which will be used as a source in the interface, make sure that at Resource Name, give the static part of the name, and then for file mask address the variable created, variables in ODI are addressed using "#", in this case #VAR1.
Make sure to write this part manually and don't use the search icon on the right:
After that, define file format in Files tab and make sure to add Columns manually using + sign in Columns tab. (It is not possible to reverse engineer the file because RKM will not obtain the variable value as of 11g.)
After defining manually all of the columns, create a new interface and add the created data store as source, right click on it and Add to target. All of existing columns from source will be mapped into target.
(Note that the table can be created in database manually and reverse engineered as a new data store and dragged into target tab).
In my case LKM will be assigned LKM File to SQL, IKM SQL Control Append.
Run the interface and check the operator:
Now that we have the interface which loads (File to table) we have to generate a new .csv file containing data from this table and send it as an email attachment and provide statistics within email for every load.
In one of my previous posts, I have shown how to generate a file from an Oracle table, I will generate a .xlsx using ODI package elements this time which is quite interesting.
Let's assemble our elements in a package.
Create a new package, I will name it EMAIL_AUTOMATION.
The first element that I will drag is VAR1 variable, we have to refresh that variable everyday (rememberthat variables have to be refreshed, otherwise they will keep the old value), due to this fact we need to load a new file which updates is name mask everyday so a refreshing is a must:
Drag VAR1 into the diagram, it will be set as a Refresh Variable, which is exactly what we need.
After having refreshed the variable, interface has to be run, we assume that the file will be generated in the path where Interface reads the source file (If it doesn't, we can use ODI File Copy from Toolbox provided and specify the copy path before running the interface but I won't be covering that topic). Drag the interface into the diagram. Connect both points using the Ok arrow which indicates that if one step executes correctly continue with the other step:
After this step file generation is required, I will do that not using an interface but using ODI SQL Unload Element from toolbox,
-Target File (full path of the file which will be generated (I want to name it file#VAR1.csv))
-JDBC Driver: oracle.jdbc.OracleDriver
-JDBC URL: jdbc:oracle:thin@<>:<>;<> (provide database info)
-User: Provide User
-Password: Provide Password
-File Format: Delimited
-SQL QUERY: (select * from <MYSCHEMA>.TARGETTEST), this is the query which "decides" what to output in your file.
Right click on ODI Sql Unload step and select execute step to test it.
Go to the path specified to generate the file and open it:
Having done this correctly you can see the data from the table.
Let's create a new variable which counts the rows in the table, using this query:
SELECT COUNT(*) FROM <MYSCHEMA>.TARGETTEST
Drag this variable into package and make sure to refresh it before the interface, otherwise the row count will not be updated:
As you can see another variable refresh has been added and also an ODISendMail step from Internet folder inside the toolbox on the left, now we need to fill the ODISendMail parameters as below:
- Mail Server (ip address of the mail server)
- From: Any valid email
- To: Any valid email
- Attachment: Full path of the file generated + variable which obtains the dynamically changing mask
- Message Body which consists the date of load, table row count as below:
Save the package and run it (check operator with all of the steps finished):
Wait for the email:
Now lets add a schedule to this package, to do so we must generate a package scenario and also have an existing ODI Agent running.
Generate a scenario under package (Scenario is a frozen code)
Expand the new scenario generated, right click on Scheduling and select new scheduling:
Many constraints can be added, I want it to run every day of the week 7:28 AM.
No comments:
Post a Comment