10 Replies Latest reply on Mar 23, 2013 3:26 PM by 998158

    Issue with FDM Import Integration script

    998158
      Hi,

      I need to pull records from Oracle DB and load in FDM. have created Import Integration script for this. But,I reciev 'Data access error' when I execute the script.

      The line of error is Set rsAppend = DW.DataAccess.farsTable(strWorkTableName)+ .When I review log, I see 'strWorkTableName' is Invalid table name.

      ERROR:
      Code............................................. -2147467259
      Description...................................... ORA-00903: invalid table name

      Procedure........................................ clsDataAccess.farsTable
      Component........................................ upsWDataWindowDM


      Below is the script:*

      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.farsTable(strWorkTableName)


      'Connect to Oracle database
      cnss.open "Provider=OraOLEDB.Oracle.1;Data Source= FDMDB;Database= FDMDB;User ID= FDM;Password= xxxx"

      'Create query string
      strSQL = "Select ACCOUNT,ENTITY,AMOUNT FROM BALANCES"
      '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("Account").Value
      rsAppend.Fields("Entity") = rs.fields("Entity").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

      ===========================

      Also, Is the below string correct to connect to Oracle DB:

      cnss.open "Provider=OraOLEDB.Oracle.1;Data Source= FDMDB;Database= FDMDB;User ID= FDM;Password= xxxx"+

      Thanks in advance

      Edited by: 995155 on Mar 20, 2013 12:45 PM