This discussion is archived
1 2 Previous Next 22 Replies Latest reply: Nov 16, 2012 3:02 AM by user10757003 RSS

FDM Integration Script Error

Smilee Newbie
Currently Being Moderated
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

http://www.flickr.com/photos/84027801@N04/7692782026/in/photostream



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
Info=False;”
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.AddNew
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
rsAppend.Update
rs.movenext
Loop
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

regards
Smilee
  • 1. Re: FDM Integration Script Error
    JeffJon Guru
    Currently Being Moderated
    It is hard to tell what is line 20 of this script.

    What is on line 20?
  • 2. Re: FDM Integration Script Error
    Smilee Newbie
    Currently Being Moderated
    Hi,

    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


    Regards
    Smilee
  • 3. Re: FDM Integration Script Error
    JeffJon Guru
    Currently Being Moderated
    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
    Smilee Newbie
    Currently Being Moderated
    Hi,

    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>


    http://www.flickr.com/photos/84027801@N04/

    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.

    Regards
    Smilee
  • 5. Re: FDM Integration Script Error
    JeffJon Guru
    Currently Being Moderated
    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
    user10757003 Journeyer
    Currently Being Moderated
    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
    Smilee Newbie
    Currently Being Moderated
    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

    http://www.flickr.com/photos/84027801@N04/


    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"

    regards
    Smilee
  • 8. Re: FDM Integration Script Error
    user10757003 Journeyer
    Currently Being Moderated
    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"
         Err.clear
         Set cnSS = Nothing
         FinSelLocs = False
         Exit Function
    End If
  • 9. Re: FDM Integration Script Error
    Smilee Newbie
    Currently Being Moderated
    Hi,

    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.

    [oledb]
    ; 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??

    regards
    Smilee
  • 10. Re: FDM Integration Script Error
    SH Guru
    Currently Being Moderated
    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
    user10757003 Journeyer
    Currently Being Moderated
    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
    Smilee Newbie
    Currently Being Moderated
    Hi,

    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??

    regards
    Smilee
  • 13. Re: FDM Integration Script Error
    user10757003 Journeyer
    Currently Being Moderated
    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
    Smilee Newbie
    Currently Being Moderated
    Hi,

    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....


    regards
    Smilee
1 2 Previous Next

Legend

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