6 Replies Latest reply on Aug 30, 2012 9:38 PM by Stuart Game

    ODI Data Import into FDM (No ERPi)

      Hi All,

      We are trying to import data into FDM using ODI from Lawson source, and have a few questions;

      1. We "donot" intend to use ERPi as we arent licensed. I know we can still use ODI to import data into FDM by editing the Import script. Does anyone have a sample code to connect ODI and FDM?

      2. In ODI, should we use a specific Target (such as SQL) to store data or could we manage with just a Temporary Staging Area? I think the FDM Import script would require either flat file or a SQL db...

      Please let me know. Appreciate your responses.
        • 1. Re: ODI Data Import into FDM (No ERPi)
          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
          • 2. Re: ODI Data Import into FDM (No ERPi)

            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..
            • 3. Re: ODI Data Import into FDM (No ERPi)
              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.
              • 4. Re: ODI Data Import into FDM (No ERPi)
                Stuart Game

                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
                • 5. Re: ODI Data Import into FDM (No ERPi)

                  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.

                  Thanks again!
                  • 6. Re: ODI Data Import into FDM (No ERPi)
                    Stuart Game

                    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.

                    Dim cmd
                    Set cmd=CreateObject("wscript.shell")
                    cmd.run "C:\Temp\Test.bat"
                    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