1 2 Previous Next 22 Replies Latest reply: Nov 16, 2012 5:02 AM by user10757003 RSS

    FDM Integration Script Error

      Hi Experts

      I want to import SQL Data to FDM. When I ran the integration script in FDM its showing <font color="red"> “Data Access Error. At Line:20 “</font>

      I have created a .UDL file and testing connection shows “success”.
      What is the exact problem I don’t know.
      1.Where am doing wrong?
      2. Is it required to give this udl file path somewhere else in FDM Application ?

      Below is the screen shot for the FDM Error


      Below is the Integration Script

      Note: I have changed the below <font color="red">red </font> color highlighted fields in the script.

      Function SQLIntegration(strLoc, lngCatKey, dblPerKey, strWorkTableName)
      ‘ FDM Integration Import Script:
      ‘Created By: Admin
      ‘Date Created: 04/19/2004 2:18:39 PM
      ‘Purpose: This import integration script connects to the sample
      ‘ Northwind SQL Server database and imports sample
      ‘ source data from the Orders table into FDM.
      Dim cnSS ‘ADO connection object
      Dim strSQL ‘SQL string
      Dim rs ‘Source system recordset
      Dim rsAppend ‘ FDM recordset
      ‘Initialize ADO objects
      Set cnSS = CreateObject(“ADODB.Connection”)
      Set rs = CreateObject(“ADODB.Recordset”)
      ‘Open FDM work table recordset for appending
      Set rsAppend = DW.DataAccess.farsTableAppend(strWorkTableName)
      ‘Connect to Northwind SQL Server database (our data source)
      Dim strConn
      strConn=“Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
      strConn=strConn & “Initial Catalog=<font color="red">Northwind</font>;Data Source=<font color="red">LocalHost</font>;”
      cnss.open strConn
      ‘Create source query string
      strSQL = “Select * “
      strSQL = strSQL & “FROM Orders “
      ‘Open source recordset
      rs.Open strSQL, cnSS
      ‘Check for data in source system
      If rs.bof AND rs.eof Then
      ‘Give error message
      RES.PlngActionType = 2
      RES.PstrActionValue = “No records to load!”
      ‘Assign Return value of function
      SQLIntegration = False
      Exit Function
      End If
      ‘Loop through source records in Northwind database (Orders table) and
      append to FDM work table
      If Not rs.bof And Not rs.eof Then
      Do While Not rs.eof
      rsAppend.Fields(“PartitionKey”) = RES.PlngLocKey
      rsAppend.Fields(“CatKey”) = lngCatKey
      rsAppend.Fields(“PeriodKey”) = dblPerKey
      rsAppend.Fields(“DataView”) = “YTD”
      rsAppend.Fields(“Amount”) = rs.fields(“Freight”).Value
      rsAppend.Fields(“Account”) = rs.fields(“CustomerID”).Value
      rsAppend.Fields(“Entity”) = rs.fields(“ShipCountry”).Value
      rsAppend.Fields(“Desc1”) = rs.fields(“ShipName”).Value
      End If
      ‘Give success message
      RES.PlngActionType = 2
      RES.PstrActionValue = “SQL Import successful!”
      ‘Assign Return value
      SQLIntegration = True
      End Function

      Please do the needful.

      If you have any FDM integration document step by step, please share me to my email id smileplease9002@gmail.com

        • 1. Re: FDM Integration Script Error
          It is hard to tell what is line 20 of this script.

          What is on line 20?
          • 2. Re: FDM Integration Script Error

            1. When i ran the integration script in FDM Script editor, its showing <Font color="Red">“Data Access Error. At Line:20 “</font>

            Error line 20:
            <Font color="Red">
            (Set rsAppend = DW.DataAccess.farsTableAppend(strWorkTableName))</font>

            2.When i click on import button it is not importing any data in FDM.

            1) And it is not showing any kind of error message.
            2) No log file is created for this script

            • 3. Re: FDM Integration Script Error
              You cannot run an intergration script from the script editor, it can only be executed via the import process.

              Did you create the script as Import(Integration) for the type?
              Is it assigned to an import format and assigned to the location?
              • 4. Re: FDM Integration Script Error

                I have created the import format script and assigned to the location.

                when i click on import button nodata imported and no error message showing.

                <Font color="Red"> Please see the screen shots from below link </Font>


                Sample Data1:
                Actual,2012,JANUARY,VIEW,INDIA,INR,SALES,ICPNONE,C1,C2,C3,C4, 50000

                Sample data2:
                INDIA, EXT SALES, ICPNONE, NONE, NONE, NONE, NONE, 60000
                INDIA, RENT, ICPNONE, NONE, NONE, NONE, NONE, 20000

                I tried with both sample data from SQL but NODATA is imported.

                Can you plz share me your email to smileplease9002@gmail.com so that i can send you the snap shots.(you can see the snapshots from above link also...☺)

                Thanks for your help in advance.

                • 5. Re: FDM Integration Script Error
                  It may be your provider line that is using SQL Integrated Security.

                  Can you update this with the User and password you are using to connect to the source SQL Table and then re-test.

                  It's difficult to troubleshoot with only screen shots, you may need to open a SR.
                  • 6. Re: FDM Integration Script Error
                    If you have access to the FDM database, you could check to see if the temporary table was created / populated. If the script fails, it tends not to get deleted.
                    • 7. Re: FDM Integration Script Error
                      First of all, thanks to everyone for your replies

                      1.It is not creating any kind of tempt files...

                      Please see the FDM database tables with the below link


                      2. I have used Windows integrated Security while creating the FDM Application. And I have used SQLOLEDB for OLE DB Provider.

                      3.Where i need to provide the username/password of SQL login information in the script? as am using SQL Server Authentication login.

                      Servername : HOST
                      Login : HFM
                      Password : XXXXXXX
                      Databasename : DEV
                      TabaleName : DJ

                      Script information Example:
                      cnss.open "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist SecurityInfo=False;Initial Catalog=dev;Data Source=host;"
                      strSQL = "Select * "
                      strSQL = strSQL & "FROM dj"

                      • 8. Re: FDM Integration Script Error
                        Try something like the following, and i would suggest adding in some error handling to identify the exact error being returned if it is a connection problem. If the connection is ok, then put the error handling after the read of the database, to see if there is an issue there and also check if any records are found.

                        'Connect to SQL Server database
                        cnSS.Open "Driver=SQL Server;Server=<servername>;Database=<DatabaseName>;UID=<Username>;pwd=<Password>"
                        cnSS.CommandTimeout = 600

                        'Error Handling
                        If Err.Number <> 0 Then
                             ' An exception occurred
                             RES.PlngActionType = 2
                             RES.PstrActionValue = Err.Description & Chr(10) & "Unable to connect to Database"
                             Set cnSS = Nothing
                             FinSelLocs = False
                             Exit Function
                        End If
                        • 9. Re: FDM Integration Script Error

                          1.I have tested with your script it is showing *1017-Expected 'Then' AtLine:23*

                          Line23 is:*If Err.Number 0 Then*

                          but it is not showing any error message, nor importing data in the Web client when i click on import button.

                          2. have created a UDL file tested and got success. UDL File also showing the same script.

                          ; Everything after this line is an OLE DB initstring
                          Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=dev;Data Source=host

                          Can any one share me a sample doc for loading data from SQL server??

                          • 10. Re: FDM Integration Script Error
                            It should be If Err.Number = 0 Then

                            The FDM Admin guide has a section on setting up integration scripts to source data from a SQL Server DB, use that as a reference
                            • 11. Re: FDM Integration Script Error
                              Sorry, i did not check my cut and paste. It should have been the VB for "not equal" zero
                              • 12. Re: FDM Integration Script Error

                                I have inserted <> 0 to the script. when i ran the import it is not showing any error message nor loading data.

                                it is not showing any white fish symbol also

                                Where am doing wrong? i have used the same sample script from admin guide.

                                Any further suggestions??

                                • 13. Re: FDM Integration Script Error
                                  Can you just confirm that when you added the error handling for the connection, that you used "If error.Number <>0 then" and that produced no error message? If it didn't did you then change the line to "If error.Message = 0 then" as this should then have produced the message box. If neither option produced the message box it would indicate the script hasn't got that far.
                                  • 14. Re: FDM Integration Script Error

                                    I have inserted If Err.Number (not equal to symbol)0 Then. Still it is not showing any kind of error message. even whitefish symbol also. And no data is importing to FDM application.

                                    Note: <Font color="Red">Not equal to symbol<,> is not showing in this thread.</font> when i typed it is showing, when i checked in preview it is not showing. because of this reason, I have added comma(,) to show you the Not equal to symbol in this thread....

                                    1 2 Previous Next