In my previous blog I demonstrated how ODI Scenarios can be exported with one click using ODI’s Toolbox features. In this blog, I will show how to do the same for ODI Scenario imports. Just like the manual exports from ODI Studio, you always have the option to manually import each Scenario one at a time to your ODI Execution Repository. But if you have a lot of them to import, it can get tedious and time consuming. Oracle ODI Studio’s Toolbox provides us with the ability to create a Package that will import all our Scenarios with one click.
Since the environment I was importing to was an ODI Execution Repository environment, I could not create any ODI artifacts there. ODI Designer is not available for an ODI Execution Repository. So, within the “Maintenance” project (This project is a reference from my previous blog post), I created a Package called “Import_All_S” for importing all Scenarios. ODI Studio’s Toolbox provides an OdiImportScen feature, but unlike the OdiExportAllScen feature which exports all your Scenarios OdiImportScen only lets you import one Scenario at a time. Because of this limitation and the fact that I had to import 30+ Scenarios, my Package “Import_All_S” runs in a loop importing one Scenario at a time until all Scenarios are imported. On each loop, I used the help of a database table to get the name of the Scenario I want to import, updated an ODI variable with that value, and then used this variable as the Scenario import file name for the OdiImportScen feature.
1) First thing I did was to create a database table that holds the names of all the Scenarios I planned to import. I created a table “Properties” that holds various properties for me, one of which is the Scenario names. In my example below, the Scenario names are aaa, bbb, and ccc. Here a screenshot of how it looks like:
2) Then I created 2 ODI Variables on the “Maintenance” project. lineNumber (type Numeric) and importFile (type Text).
3) I created the “Import_All_S” Package. This is how the finished Package looks like:
Let’s walk thru the steps to create this Package.
3a) The first step, “lineNumberS”, I set the variable lineNumber to 0.
3b) The second step “lineNumberR”, I refresh the lineNumber variable such that it returns the property_id from the reference table “Properties” where the property_type is “IMPORT_FILE_NAME” and the property_id is greater than the last value of this variable.
If the last value of lineNumber was 0, looking at your table (see database table image above) the minimum value greater than 0 for property_type “IMPORT_FILE_NAME” is 1, and variable lineNumber would now refresh the value to 1.
If the last value of lineNumber was 1, looking at your table (see database table image above) the minimum value greater than 1 for property_type “IMPORT_FILE_NAME” is 2, and variable lineNumber would now refresh the value to 2.
If the last value of lineNumber was 2, looking at your table (see database table image above) the minimum value greater than 2 for property_type “IMPORT_FILE_NAME” is 3, and variable lineNumber would now refresh the value to 3.
If the last value of lineNumber was 3, looking at your table (see database table image above) the minimum value greater than 3 for property_type “IMPORT_FILE_NAME” is NULL and variable lineNumber would now refresh the value to 0 (based on the NVL() function).
3c) The third step “lineNumberE”, I evaluate the lineNumber variable to check if the value is greater than 0. If it is equal or less than 0, this Package stops here. If it is greater than 0, we move onto the next step.
3d) The fourth step “importFileR”, I refresh the importFile variable such that I get the property_value from the reference table “Properties” where property_id is equal to the lineNumber refreshed on Step 3b.
If the last value of lineNumber was 0, the process would never get here and would have ended at step 3c.
If the last value of lineNumber was 1, looking at your table (see database table image above) the property_value would be “aaa.xml” and variable importFile would now refresh the value to “aaa.xml”.
If the last value of lineNumber was 2, looking at your table (see database table image above) the property_value would be “bbb.xml” and variable importFile would now refresh the value to “bbb.xml”.
If the last value of lineNumber was 3, looking at your table (see database table image above) the property_value would be “ccc.xml” and variable importFile would now refresh the value to “ccc.xml”.
3e) The fifth step “OdiImportScenI”, I import the Scenario based on the value of importFile variable from Step 3d. After the successful completion of Step 3e, it loops back at Step 3b to refresh the lineNumber variable and go thru the steps again until the lineNumber value reaches 0.
4) As this was a onetime step, I manually created the Scenario for the Package “Import_All_S” and imported this Scenario to the new ODI Execution Repository environment.
5) As and when ODI Scenarios needed to be migrated, I executed “Export_All” Package from ODI Studio (see previous blog post), moved the exported Scenarios to the new environment’s server, confirmed any changes that were needed to the “Properties” table and then executed the “Import_All_S” Scenario on the new ODI Execution Repository environment.
You can use this Package as a starting point for your ODI Scenario imports. You can add more funtionality to your Package by adding a status column to your “Properties” table to control which Scenarios to import and which not or you can add a column to control which version of the Scenario to import or you can add an action-item once the loop ends (after step 3c) to let you know when it finished successfully etc etc.
Creating the “Maintenance” project to manage my Scenario migrations helped me in two primary ways:
1) Eliminated the human error of forgetting to regenerate and/or export a specific Scenario.
2) Saved a considerable amount of time in the development life-cycle.
Please feel free to leave me a comment if you have any questions.