5 Replies Latest reply on Feb 28, 2014 7:38 PM by JeffJon

    FDM import from oracle RDB using Import Script gives -  Import failed.Invalid data or Empty content.

    Saurav S-Oracle

          Hi All,

       

      I am creating an FDM interface to fetch data from Oracle RDB and push it to an essbase database. I am under the impression that if you have to pull data from RDB you need to create an integration import script . So I  have created an Import Integration script based on the example given in the FDM admin Document.

      The Script is as below:

      Function PSIP_TEST(strLoc, lngCatKey, dblPerKey, strWorkTableName)

      '------------------------------------------------------------------

      'Oracle Hyperion FDM Integration Import Script:

      '

      'Created By: admin

      'Date Created: 2/18/2014 6:00:33 PM

      '

      'Purpose:      

      '              

      '------------------------------------------------------------------

      Dim cnSS 'ADODB.Connection

      Dim strSQL 'SQL string

      Dim rs 'Recordset

      Dim rsAppend 'tTB table append rs object

       

       

      'Initialize objects

      Set cnSS = CreateObject("ADODB.Connection")

      Set rs = CreateObject("ADODB.Recordset")

      Set rsAppend = DW.DataAccess.farsTableAppend(strWorkTableName)

       

       

      'Connect to Oracle database

      cnss.open "Provider=OraOLEDB.Oracle.1;Password=PSIP_TST;Persist Security Info=True;User ID=PSIP_TST;Data Source=PSIP"

       

       

       

      'Create query string

      strSQL = "SELECT YEAR,COST_CENTRE,PROGRAMME_CODE,ACCOUNTS,PROJECT_CODE,AMOUNT FROM VW_PSIP_EBUDGET_ESTIMATES"

      'Get data

      rs.Open strSQL, cnSS

      'Check for data

      If rs.bof And rs.eof Then

      RES.PlngActionType = 2

      RES.PstrActionValue = "No Records to load!"

      Exit Function

      End If

      'Loop through records and append to tTB 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") = RES.PlngCatKey

      rsAppend.Fields("PeriodKey") = RES.PdtePerKey

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

      rsAppend.Fields("CalcAcctType") = 9

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

      rsAppend.Fields("Entity") = rs.fields("COST_CENTRE").Value

      rsAppend.Fields("UD1") = rs.fields("COST_CENTRE").Value

      rsAppend.Fields("UD2") = rs.fields("PROGRAMME_CODE").Value

      rsAppend.Fields("UD3") = rs.fields("PROJECT_CODE").Value

      rsAppend.Fields("Amount") = rs.fields("Amount").Value

      rsAppend.Update

      rs.movenext

      Loop

      End If

      'Records loaded

      RES.PlngActionType = 6

      RES.PstrActionValue = "Import successful!"

      'Assign Return value

      SQLIntegration = True

       

      End Function

       

      When I run the Script in Script editor I get the following error :

      Error: An error occurred running the script:

      -2147467259 - Data access error.

      At Line: 19

       

      If I see in the Error log it is as follows:

      ERROR:

      Code............................................. -2147467259

      Description...................................... ORA-00903: invalid table name

       

       

      Procedure........................................ clsDataAccess.farsTableAppend

      Component........................................ upsWDataWindowDM

      Version.......................................... 1112

      Thread........................................... 41900

       

       

       

      Line nu. 19 is this :  Set rsAppend = DW.DataAccess.farsTableAppend(strWorkTableName) and therefore I thought that may be when I run the script in the Script editor maybe the value of the variable "strWorkTableName" does not get populated that is why it gives error .

       

      So I ran the Import stage in the Workflow and Got the following error :

      Error: Import failed.Invalid data or Empty content.

       

      and there is no entry in the error log.

       

      I am lost , please need your help .

       

      One more thing I would like to ask is kindly please validate if my connection string is correct which is :

      cnss.open "Provider=OraOLEDB.Oracle.1;Password=PSIP_TST;Persist Security Info=True;User ID=PSIP_TST;Data Source=PSIP"

       

      I read that FDM is a 32 bit product and therefore will require a 32 bit oledb connection provider.

      FDM here is installed on a 64 bit machine and the provider here used in the connection string is 64 bit.


      Please help , my work here has come to a stand still . Kindly help