This content has been marked as final. Show 6 replies
Just get ODI to spit out a file with the data in it and copy it to wherever you need it in the FDM Inbox where it can then be picked up and loaded manually. If you want a fully automated process simply put it into the OpenBatch folder and name the file according to the Batch loader naming convention and the kick off the batch process from ODI with a call to the upsShell executable
Thanks for your response.
Heres a couple of our reqs,
1. We want to stay away from a flat file, hence the question on temporary staging vs a sql db.
2. We want users to kick off the ODI process from FDM when they Import data. I guess I am just trying to find a sample code for the ODBC connection in the Import script with ODI and then ofcourse call the Package via FDM.
Let me know your thought..
Why do you want to stay away from a flat file? They provide a snapshot of the data at a given point in time (good for audit) which will not change and can be manipulated easily by FDM's out-of-the-box functionality without the need for any custom scripting for DB access. You cannot drill back to temp db tables. I recommend that you use a flat file, less effort to set up and much easier to maintain/support and as I previously mentioned you can link directly into batch automation.
You can kick off an ODI scenario by scripting a shell script call to the ODI startscen.bat script. This can script can be setup and associated with an FDM Task Flow for one click execution.
There is an example of an FDM import integration script in the admin guide, would this help you? Try chapter 19, pg 206 Integration Import Script Example
This way you could integrate to an ODI staging table I would think and do so from an FDM batch if needed.
Hope this helps
Thank you all for your valueble inputs. We are still deciding between Flat File vs. SQL (for several reasons I shall explain later).
Meanwhile, now we do know how to automate ODI-FDM process. The question is does it have to be a Custom Script called via a Task-Flow or could we call ODI from an Import Script?
And we dont know how well our users will receive having to go to Task-Flow to execute this ODI-FDM/Import process. (We want to limit the automation to only the Import Process.)
What we found was that we "CANNOT" include the ODI executable (or any outside executable process) in an Import script. Is this true?
Please let me know.
Can you launch the ODI executable from the command line?
If you can then the below is an example of how you can shell out and launch a windows batch (.bat) file from within an FDM script.
Set cmd = Nothing
We used this within a custom script so you would need to test it if using it elsewhere. Also you need to be aware that the FDM script will not wait for a return code from the .bat file it will just launch it and carry on to the next commands.
Can I ask why you need to run the ODI process each time before the FDM import, could you automate the ODI process to run periodically (out of hours?) so that all FDM has to do is query the relational tables populated by the ODI process?
It also sounds as though you are intending FDM users to manually trigger each process step through FDM, have you considered FDM batch processing and are you aware that you can launch FDM scripts from the command line as well using the upsShell.exe utility? Potentially you could launch the FDM import process from ODI for example.
Lots of options available...
Hope this helps