ODBC error when connecting from SSIS to FileMaker Server Advanced
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.
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).
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.
Pipeline component has returned HRESULT error code 0xC0208457 from a method call. (Microsoft.SqlServer.DTSPipelineWrap)
FileMaker Server Advanced 126.96.36.1999 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)