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

    Custom Script IN FDMEE to fetch data from Oracle DB

    Roshin

      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= 172.18.37.107:10710/<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.AddNew

        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)

        rsAppend.Update

        rs.movenext

        Loop

        End If

       

       

       

             'RES.PlngActionType = 2

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

       

       

        'Close Record Sets

        rs.close

        rsAppend.close

       

       

      'Assign Return value

      ACT_FISGRPSALES_EXT = True //

       

      **************************

       

      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 .

       

      Thanks,

      Roshin

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

          Hi,

           

          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.