1 Reply Latest reply on Feb 15, 2012 2:15 PM by user15307

    ODBC error when connecting from SSIS to FileMaker Server Advanced

    rolf.marino

      Summary

      ODBC error when connecting from SSIS to FileMaker Server Advanced

      Product

      FileMaker Server

      Version

      Advanced 11.0.3.309

      Operating system version

      Windows Server 2008 R2 Enterprise

      Description of the issue

      Setting up a Microsoft SSIS (SQL Server Integration Services) package with the scope of writing data into a FileMaker file, SSIS reports that it "failed to convert the schema table obtained by querying the connection to a standard format."

      Steps to reproduce the problem

      In server A (Win 2008 Server R2 Enterprise) is running FileMaker Server Advanced 11v3, hosting a file with multiple tables.
      In server B (also Win 2008 server R2 Enterprise) is running SQL server 2008. From SSIS (SQL Server Integration Services) we intend to import data from Excel file and write them into the FileMaker file hosted in server A.
      In server B we set up successfully the DSN with the FileMaker ODBC driver (the test is OK), in SSIS we build the data flow package setting as destination a data flow destination component based on the DSN previously declared.
      In the data flow destination component the tables inside the FileMaker file are all correctly listed (that means that the connection is successfull), then we choose the FileMaker destination table.

      Expected result

      After having chosen the FileMaker destination table, we should be able to map the input columns (of the Excel source file) to the destination columns (i.e. the fields of the FileMaker table).

      Actual result

      After we choose the right table, SSIS reports below error and it is impossible to map the input columns (of the Excel source file) to the destination columns (i.e. the fields of the FileMaker table), because no destination column is available.

      Exact text of any error message(s) that appear

      Failed to get properties of external columns. The table name you entered may not exist, or you do not have SELECT permission on the table object and an alternative attempt to get column properties through connection has failed. Detailed error messages are:
      The provider returned an empty schema table.
      Failed to convert the schema table obtained by querying the connection to a standard format.
      Additional information:
      Pipeline component has returned  HRESULT error code 0xC0208457 from a method call. (Microsoft.SqlServer.DTSPipelineWrap)

      Configuration information

      FileMaker Server Advanced 11.0.3.309 running and with ODBC/JDBC activated (on server A)
      On the FileMaker file the extended privileges are enables for the user specified in the client driver.
      FileMaker listener 11.3.82 running (on server A)
      FileMaker ODBC driver server side 11.3.81 (on server A)
      FileMaker ODBC driver client side 11.03.81 (on server B)

      Workaround

      none

      filemaker_odbc_ssis_error.jpg

        • 1. Re: ODBC error when connecting from SSIS to FileMaker Server Advanced
          user15307

          Rolf Marinio,

          Thank you for your post. I apologize for the late response.

          Although your post is quite detailed, I still need some clarification in order to help. I don't have experience with Microsoft SSIS and its capabilities. I think it would be best to isolate the FileMaker Server/ODBC functions you have working and go from there. After we confirm that works, perhaps then you can re-introduce the services of SSIS.

          "In server A (Win 2008 Server R2 Enterprise) is running FileMaker Server Advanced 11v3, hosting a file with multiple tables.

          In server B (also Win 2008 server R2 Enterprise) is running SQL server 2008.

          In server B we set up successfully the DSN with the FileMaker ODBC driver"

          At this point, are you able to use the "File/Import Records...ODBC Data Source" from your FileMaker Server-hosted file and successfully import from the SQL Server?

          If so, I suggest for the purposes of testing that you separate the two steps: import your Excel data into the SQL database, then perform the import from the SQL Server to FileMaker.

          I realize a month has passed since your post, so if you have discovered a solution, please post it here so that others with a similar issue can benefit.

           

          TSTuatara

          FileMaker, Inc.