Reverse Engineer works for an existing physical table.
What we can do in this scenario is, we already have a reversed engineered datastore in the Model. Lets say the process id for this is 1, and the table name is 1_LOAD_TABLE. Open this datastore, and modify the resource name to have a variable instead of the actual table name. The steps for doing it are below.
- Create a new variable #P_PROCESS_ID in the project and add it to the ODI package before the step which uses the Source table in the interface (or mapping) or procedure. This can be a set variable where you can set the value to 1, or if you have a dynamic package then you can write a query to pick the required process id (refresh variable), or the data mart process which creates and loads the table and launches the subsequent ODI load scenario can pass this paramater (using declare variable)
- In the package, create and add another variable #P_TABLE_NAME, where the code should concatenate the #P_PROCESS_ID and '_LOAD_TABLE'.
- In the Datastore for the table, rename resource name to #P_TABLE_NAME'
Now when the package (or scenario) runs, the variable will be replaced with the actual value and will be used.
Always remember to add the variable steps in each of the ODI package where it is used. Don't reply on the last value, it doesn't always work correctly.
Tell me if you need snapshots.
This is exactly what I think I am looking for! Going to test it asap! I'll keep you updated!
Sorry for the delay in my reply. I modified the requirement so I only need a variable that defines the table name. I did the following:
- Changed the resource name in the datastore to #DYN_TBL
- Created a variable called #DYN_TBL
- Created a package - declare the variable, set the variable, called the interface
- Using the #DYN_TBL as the source table in the interface
The package is failed during the Integration step with ODI-1228 The multi-part identifier "ABDC_123.Col1' could not be bound. Where ABDC_123 = the variable value and Col1 = column. Not sure what I am missing. Could you please send screenshots and maybe I can figure where I went wrong?
Appreciate the assistance! This is being critical!