This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Nov 16, 2012 3:02 AM by user10757003 Go to original post RSS
  • 15. Re: FDM Integration Script Error
    user10757003 Journeyer
    Currently Being Moderated
    I did reply to your last post but the message seems to have disapeared. Anyway, i suspect the script is not being executed as setting the error.message to zero and not equal zero is not producing any message. (It can only be one or the other).
    As a result, I looked back at the screenshots you posted and noticed that you appear to be executing the import process from the workflow menu option. If this is the case, then it is not correct, and the reason why you get the message displayed on the import screen. To execute the script you need to select the second tab on the import screen (as you would if you were selecting an import file) and select the import button at the top left of the screen.
  • 16. Re: FDM Integration Script Error
    Smilee Newbie
    Currently Being Moderated
    Hi,

    As per you instructions i have selected 2 tab on import screen(Upload File) and executed the import button which is near to import type it is showing below error message.

    Error: An error occurred importing the file.
    Detail: Item cannot be found in the collection corresponding to the requested name or ordinal.
    At line: 38

    Line number 38 is refers to this function rsAppend.Fields("Amount") = rs.fields("dblAmt").Value


    When i click on import button on 4 Fish tab it is not showing any error message.

    regards
    Smilee
  • 17. Re: FDM Integration Script Error
    user10757003 Journeyer
    Currently Being Moderated
    the error would indicate that "dblAmt" does not exist in your source exist in your source table if doing a "select *", or in your SQL statement if you have defined the individual fields?

    Selecting any other import option (selecting the fish, or the menu pulldown option, does not execute the script. the only option is as stated using the import button on the second tab.
  • 18. Re: FDM Integration Script Error
    Smilee Newbie
    Currently Being Moderated
    Hi,


    I tried to execute with import button from all the possible ways still no data is imported.
    I tried with 2nd tab of import screen (upload file)->with import button which is beside import types(Replace,Append,) still it is showing the same error.


    Where am doing wrong?

    <font color="red">
    Still it is showing the same error.

    Error: An error occurred importing the file.
    Detail: Item cannot be found in the collection corresponding to the requested name or ordinal.
    At line: 38

    Line 38 is = rsAppend.Fields("Amount") = rs.fields("dblAmt").Value
    </font>


    I have created below tables in DEV database in HOST Server.

    1.SELECT * FROM DJ;
    <font color="Blue">
    ACTUAL      2012      JANUARY      VIEW      INDIA      INR      SALES      ICPNONE      C1      C2      C3      C4      50000

    2.SELECT * FROM SDR;

    INDIA      EXT SALES ICPNONE      NONE      NONE      NONE      NONE      60000
    INDIA      RENT      ICPNONE      NONE      NONE      NONE      NONE      20000

    </font>


    Integration script:

    1     Function Integration1(strLoc, lngCatKey, dblPerKey, strWorkTableName)
    2     '------------------------------------------------------------------
    3     'Oracle Hyperion FDM IMPORT Integration Script:
    4     'Created By:
    5     'Date Created:
    6     'Purpose:
    7     '------------------------------------------------------------------
    8     Dim objSS 'ADODB.Connection
    9     Dim strSQL 'SQL String
    10     Dim rs 'Recordset
    11     Dim rsAppend 'tTB table append rs Object
    12     'Initialize objects
    13     Set cnSS = CreateObject("ADODB.Connection")
    14     Set rs = CreateObject("ADODB.Recordset")
    15     Set rsAppend = DW.DataAccess.farsTable(strWorkTableName)
    16     'Connect To SQL Server database
    17     cnss.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dev;Data Source=host;"
    18     strSQL = "Select * "
    19     strSQL = strSQL & "FROM sdr"
    20     'Get data
    21     rs.Open strSQL, cnSS
    22     'Check For data
    23     If rs.bof And rs.eof Then
    24     RES.PlngActionType = 2
    25     RES.PstrActionValue = "No Records To load!"
    26     FirstImportVB = False ' Assign return value of function
    27     Exit Function
    28     End If
    29     'Loop through records And append To tTB table In location’s DB
    30     If Not rs.bof And Not rs.eof Then
    31     Do While Not rs.eof
    32     rsAppend.AddNew
    33     rsAppend.Fields("PartitionKey") = RES.PlngLocKey
    34     rsAppend.Fields("catKey") = lngCatKey
    35     rsAppend.Fields("PeriodKey") =dblPerKey
    36     rsAppend.Fields("DataView") = "YTD"
    37     rsAppend.Fields("CalcAcctType") = 9
    38 rsAppend.Fields("Amount") = rs.fields("dblAmt").Value
    39     rsAppend.Fields("Desc1") = rs.fields("txtAcctDes").Value
    40     rsAppend.Fields("Account") = rs.fields("txtAcct").Value
    41     rsAppend.Fields("Entity") = rs.fields("txtCenter").Value
    42     rsAppend.Update
    43     rs.movenext
    44     Loop
    45     End If
    46     'Records loaded
    47     RES.PlngActionType = 2
    48     RES.PstrActionValue = "SQL Import successful!"
    49     'Assign Return value
    50     Integration1 = True
    51     End Function


    regards
    Smilee
  • 19. Re: FDM Integration Script Error
    user10757003 Journeyer
    Currently Being Moderated
    Because you are using the Select *, i cannot see what the field names are in the SDR table. Have you double checked the field name (dblAmt) for the amount field in the SDR table is correct?

    Edited by: user10757003 on 14-Nov-2012 14:27
  • 20. Re: FDM Integration Script Error
    SH Guru
    Currently Being Moderated
    You have 8 fields in the table you are selecting from with the last field being the value. Reference the value field using the numeric psoition in the fields collection as this will remove any errors caused by using incorrect text names for the fields item i.e. try replacing rs.fields("dblAmt").Value with rs.fields(7).Value. If this still gives an error on the same line return the following value rs.fields.count to an information dialog to validate how many fields are being returned in your recordset. Also check there are no NULL values in your source table for the amount field if there are update them to be zeros or put some conditional code to catch NULLs in the script
  • 21. Re: FDM Integration Script Error
    Smilee Newbie
    Currently Being Moderated
    <font color="Blue">
    Hi,

    Thanks to All who helped me to solve my issue
    </font>
    <font color="Blue">

    I have replaced the rs.fields("dblAmt").Value with rs.fields(7) then it is showing error for next lines then i modified the next lines with the numbers. <font color="Greencolor">Now data imported Successfully !!!!</font>

    And I have added the UD1 - UD4 members too.....

    Here is the data table and Integration Script:


    1.SELECT * FROM SDR;

    INDIA,          EXTSALES, ICPNONE,        NONE,           NONE,          NONE,           NONE,           60000
    INDIA,          RENT,           ICPNONE,        NONE,           NONE,           NONE,           NONE,           20000

    1     Function Integration1(strLoc, lngCatKey, dblPerKey, strWorkTableName)     
    2     '------------------------------------------------------------------     
    3     'Oracle Hyperion FDM IMPORT Integration Script:     
    4     'Created By:      admin
    5     'Date Created:      2012-10-31-03:54:18
    6     'Purpose:     
    7     '------------------------------------------------------------------     
    8     Dim objSS 'ADODB.Connection     
    9     Dim strSQL 'SQL String     
    10     Dim rs 'Recordset     
    11     Dim rsAppend 'tTB table append rs Object     
    12     'Initialize objects     
    13     Set cnSS = CreateObject("ADODB.Connection")     
    14     Set rs = CreateObject("ADODB.Recordset")     
    15     Set rsAppend = DW.DataAccess.farsTable(strWorkTableName)     
    16     'Connect To SQL Server database     
    17     cnss.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dev;Data Source=host;"     
    18     strSQL = "Select * "     
    19     strSQL = strSQL & "FROM sdr"     
    20     'Get data     
    21     rs.Open strSQL, cnSS     
    22     'Check For data     
    23     If rs.bof And rs.eof Then     
    24     RES.PlngActionType = 2     
    25     RES.PstrActionValue = "No Records To load!"     
    26     FirstImportVB = False ' Assign return value of function      
    27     Exit Function     
    28     End If     
    29     'Loop through records And append To tTB table In location’s DB     
    30     If Not rs.bof And Not rs.eof Then     
    31     Do While Not rs.eof     
    32     rsAppend.AddNew     
    33     rsAppend.Fields("PartitionKey") = RES.PlngLocKey     
    34     rsAppend.Fields("catKey") = lngCatKey      
    35     rsAppend.Fields("PeriodKey") =dblPerKey     
    36     rsAppend.Fields("DataView") = "YTD"     
    37     rsAppend.Fields("CalcAcctType") = 9     
    38     rsAppend.Fields("Amount") = rs.fields(7).Value     
    39     rsAppend.Fields("Desc1") = rs.fields(1).Value     
    40     rsAppend.Fields("Account") = rs.fields(1).Value     
    41     rsAppend.Fields("Entity") = rs.fields(0).Value     
    42     rsAppend.Fields("ICP") = rs.fields(2).Value     
    43     rsAppend.Fields("UD1") = rs.fields(3).Value     
    44     rsAppend.Fields("UD2") = rs.fields(4).Value     
    45     rsAppend.Fields("UD3") = rs.fields(5).Value     
    46     rsAppend.Fields("UD4") = rs.fields(6).Value     
    47     rsAppend.Update     
    48     rs.movenext     
    49     Loop     
    50     End If     
    51     'Records loaded     
    52     RES.PlngActionType = 2     
    53     RES.PstrActionValue = "SQL Import successful!"     
    54     'Assign Return value     
    55     Integration1 = True     
    56     End Function




    Regards
    Smilee
    </font>
  • 22. Re: FDM Integration Script Error
    user10757003 Journeyer
    Currently Being Moderated
    Well done. took us a while but we got there in the end.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points