1 Reply Latest reply on Jul 24, 2014 10:51 PM by Francisco Amores

    Custom Script IN FDMEE to fetch data from Oracle DB


      Hi All Please find a custom script which i try to execute to fetch data from Oracle DB to FDMEE .



      Set cnSS = CreateObject("ADODB.Connection")

      Set rs = CreateObject("ADODB.Recordset")

      Set rsAppend = DW.DataAccess.farsTable(strWorkTableName)

      Prd = FormatDateTime(CDate(dblPerKey),1)

      Prd = UCase(CStr(Mid(Prd, InStr(Prd, ",")+2, 3) & "-" & Right(Prd, 2)))



      'Connect to Oracle database

        cnss.open "Provider=OraOLEDB.Oracle.1;Password="*****";Persist Security Info=True;User ID=apps ;Data Source=<SID Name >"



        'Create query string



        strSQL = "Select * FROM "

        Select Case lngCatKey

        Case 12: strSql = strSql & "GL_BALANCES"

        End Select



        CnSS.commandTimeout = 240

        rs.Open strSQL, cnSS


               'Check for data

        If rs.bof And rs.eof Then



        Exit Function

        End If



        //Mapping between soruce and Target

          'Loop through records and append to FDM Worktable table in location's DB

        If Not rs.bof And Not rs.eof Then

        Do While Not rs.eof


        rsAppend.Fields("PartitionKey") = RES.PlngLocKey

        rsAppend.Fields("CatKey") = lngCatKey

        rsAppend.Fields("PeriodKey") = dblPerKey

        rsAppend.Fields("DataView") = "YTD"

        rsAppend.Fields("Account") = UCase (rs.fields("Account").Value)

        rsAppend.Fields("Entity") = UCase (rs.fields("Legal Entity").Value)




        End If




             'RES.PlngActionType = 2

            ' RES.PstrActionValue = "No Records to load!" & "SQL:" & StrSQL



        'Close Record Sets





      'Assign Return value





      But when i execute this am getting below message in log


      Input Error: There is no file extension in "\\Zaflaipmmvd05\mtndata\FDMEE\data\scripts\custom\Extract".



      Please help in this . this is my first time in FDMEE . My Objective is to write a custom Script to Fetch data using SQL from Oracle DB and then do mapping and load to planning .


      Please advise .




        • 1. Re: Custom Script IN FDMEE to fetch data from Oracle DB
          Francisco Amores



          couple of things.


          - You VBS custom/event scripts should have vbs extension

          - If you are willing to use VB for your scripts, take a look to sample scripts in the admin guide to see how you define the header in the script in order to get the correct parameters and be able to initialize the API.

          - If you are planning to have the oracle db as a source for your data integration you may look to another approach than using a custom script.

          You would have to use Open Interface Adapter. You can write event script BefImport in order to move data from your oracle db to open interface table.

          you have a sample script to load data into open interface table in the admin guide.


          I hope that clarifies.