1 2 Previous Next 22 Replies Latest reply: Nov 16, 2012 5:02 AM by user10757003 Go to original post RSS
      • 15. Re: FDM Integration Script Error
        user10757003
        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
          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
            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
              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
                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
                  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
                    <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
                      Well done. took us a while but we got there in the end.
                      1 2 Previous Next