Oracle

How to create an Oracle Data Integrator (ODI) Package

So I’m hoping you all read my previous post on “Creating an Oracle Data Integrator (ODI) Interface” and hopefully it was simple to follow.  Today, I want to show you how to take your interface and bundle it into a ODI package with other ODI components in which will all you to run more than 1 interface at time.  Now the ODI Package is the largest unit of execution in Oracle Data Integrator. A Package is made up of a sequence of steps organized into an execution diagram[1].

Each step can either succeed or fail its execution. Depending on the execution result (success or failure), a step can branch to another step.

Now within ODI a package consists of the following different types of steps.

Screen Shot 2014-08-26 at 12.15.19 AM

 

In todays blog I will be showing you how to create a simple ODI package containing some of the following steps.  So lets get started!

Before you can create your ODI package, make sure you are logged into ODI and locate within Designer your project.

Screen Shot 2014-08-26 at 12.16.00 AM

Once you located your project highlight “Packages” and right click your mouse.  You will have 2 options to choose from, select “New Package.”

Screen Shot 2014-08-26 at 12.16.33 AM

Once you have done the following you will now be able to create your ODI package.  Before doing so, you will need to enter a name for your ODI package.

Screen Shot 2014-08-26 at 12.16.56 AM

Once you have added the name for your ODI package, click on the “Diagram” tab on the left corner.

Screen Shot 2014-08-26 at 12.17.18 AM

Once you have clicked the Diagram tab you will now be able to add the ODI steps that will create your package.

Screen Shot 2014-08-26 at 12.17.41 AM

Now that you’re ready to create your package I want to go over ODI package toolbar.

Screen Shot 2014-08-26 at 12.18.03 AM

Within the toolbar you have your “Free Choice” which is your pointer I like to call it.  This allows you to drag and drop your ODI steps.

Screen Shot 2014-08-26 at 12.18.24 AM

The two green lines allow you to view hidden links between steps after a successful step.

Screen Shot 2014-08-26 at 12.18.47 AM

The two red lines allow you to view hidden links between steps after a failed step.

Screen Shot 2014-08-26 at 12.19.13 AM

The OK green line will allow the ODI package to move to the next step once the pervious step has ran successfully.

Screen Shot 2014-08-26 at 12.19.39 AM

The KO red line is the link in which will allow the ODI package to move to the next step once the pervious step has ran unsuccessfully or failed.

Screen Shot 2014-08-26 at 12.20.03 AM

The next option allows you to duplicate and steps you make have within your package.

Screen Shot 2014-08-26 at 12.20.25 AM

The trashcan is used to delete any steps.

Screen Shot 2014-08-26 at 12.20.49 AM

The next step after the trashcan is the reorganized option in which allows you to organize the steps within your package.

Oracle - Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud
Guide to Oracle Cloud: 5 Steps to Ensure a Successful Move to the Cloud

Explore key considerations, integrating the cloud with legacy applications and challenges of current cloud implementations.

Get the Guide

Screen Shot 2014-08-26 at 12.21.10 AM

The pencil option allows you to edit linked objects.

Screen Shot 2014-08-26 at 12.21.30 AM

The next option allows you to execute a single step.

Screen Shot 2014-08-26 at 12.21.51 AM

The thunderbolt within the yellow box will allow you to execute your package.

Screen Shot 2014-08-26 at 12.22.12 AM

The next 3 options will allow you to print your package, make page configurations and will show you any warnings within your package that will need to be addressed.

Screen Shot 2014-08-26 at 12.22.47 AM

Before creating your ODI package, one thing I want to mention is the “Toolbox” section on the top left hand corner.  ODI offers a lot of functions in which you can use for your package.  A few of my favorites that I like to mention are;

 

ODIFileAppend – Append multiple flat files into 1.

ODIFtpGet – Obtain flat files on your server for loading.

ODISendMail – Send an email once your package has complete or failed.

Screen Shot 2014-08-26 at 12.23.11 AM

Here in this example we will be using the following.

Now that you have your blank canvas you can now start creating your ODI package.  Now first rule of thumb I always say is add your variables first.  In our example, I have created a simple SYSDATE variable in which we will be using in our package.

Screen Shot 2014-08-26 at 12.23.36 AM

To add the variable to your ODI package just simply drag and drop the variable to your ODI package.  In addition, I will also drag over the interface I created in the previous blog posting.

Screen Shot 2014-08-26 at 12.23.59 AM

Now that I have added two steps you will need to identify which step you would like to execute first.  Now to do this select the step in which you would like to execute first, then right mouse click.  A list of options will appear.  Select “First Step.”

Screen Shot 2014-08-26 at 12.24.27 AM

Now that you have your first step identified lets then link our steps together.   Now there are two ways to do this.

 

  1. Click on the Ok option in your tool bar and link your steps.

Screen Shot 2014-08-26 at 12.25.05 AM

  1. Second option will be for you to click on the “Advance” tab and select “Execute Step” and then select the next step you would like to be executed after a success of the prior step.

Screen Shot 2014-08-26 at 12.25.40 AM

Your ODI package should look like the following so far.

Screen Shot 2014-08-26 at 12.26.32 AM

Next we will be adding ODIBeep to our package.  This can be located in the ODI Toolbox.  The ODIBeep plays a default beep or sound file on the machine hosting the agent. The following file formats are supported by default[2].  We will be using this beep to notify us of our successful links and unsuccessful links.

Screen Shot 2014-08-26 at 12.26.58 AM

In our ODIBeep command I will be adding the following command.

Screen Shot 2014-08-26 at 12.27.20 AM

 

Next, I will then link all the 2 steps, SYSDATE_VAR and TEST_INTERFACE to the ODIBeep.  But I will only be linking based on a failure.  Now to do this you can do this in two ways.

 

  1. Click on the ko option in your tool bar and link your steps.

Screen Shot 2014-08-26 at 12.29.02 AM

  1. Second option will be for you to click on the “Advance” tab and select “Execute Step” under the processing after failure and then select the ODIBeep.

Screen Shot 2014-08-26 at 12.29.18 AM

Your package should look like the following so far.

Screen Shot 2014-08-26 at 12.29.37 AM

The last step I will add, in which I recommended be added to all packages is the ODISendMail step.  I will add two of these so that I am notified of a successful completion of my package or a failure within my package load.

Screen Shot 2014-08-26 at 12.30.10 AM

Now that you dragged two ODISendMail steps to your package before we configure these steps add a successful link from TEST_INTERFACE to one of your ODISendMail steps and add a unsuccessful link from ODIBeep to one of your ODISendMail steps.  Your package should look like the following.

Screen Shot 2014-08-26 at 12.30.36 AM

Next you will need to configure your ODISendMail steps.  To do this highlight one of these steps and click on the General tab.  Here you will need to add all the necessary configurations.  One thing you will notice is that I also added my variable in the subject line as this will call out the SYSDATE variable in our package.

Screen Shot 2014-08-26 at 12.31.02 AM

Once you have configured your ODISendMail steps make sure you SAVE your package.  Once done you can now execute your ODI package.  You can do this in two ways.

  1. Within the ODI toolbar you can click on the thunderbolt within the yellow box will allow you to execute your package.

Screen Shot 2014-08-26 at 12.31.46 AM

  1. Or you can click on the Green play button.

Screen Shot 2014-08-26 at 12.31.58 AM

Next you will need to follow the same final loads steps as outlined in my pervious post, Creating an Oracle Data Integrator (ODI) Interface.  This will complete this instructional post.  For more information on ODI packages visit “Working with Packages – Oracle Documentation.”  Please stay tuned as I will be putting together a new post on how to create ODI Load Plans and Scenarios.

 

[1]http://docs.oracle.com/cd/E17904_01/integrate.1111/e12643/packages.htm#CJAHIDFB

[2] http://gerardnico.com/doc/odi/webhelp/en/ref_tools/snpsbeep.htm

 

Thoughts on “How to create an Oracle Data Integrator (ODI) Package”

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Chris Evans

Chris Evans has extensive management, technical, consulting and customer service skills. He provides hands-on expertise in project leadership and management, assessments, methodologies, data modeling, database design, meta data, systems analysis, and development. He has worked with multiple platforms, and his experience spans a wide range of operational and data warehouse environments. In addition to his BI background, Chris Evans has a experience in marketing strategy for major consumer brands.

More from this Author

Subscribe to the Weekly Blog Digest:

Sign Up
Categories
Follow Us
TwitterLinkedinFacebookYoutubeInstagram