Filemaker to SQL Server via SSIS

Question

I'm using SSIS and trying to import data from Filelmaker into SQL Server. In the Solution Explorer, I right click on "SSIS Packages" and select SQL Server Import and Export Wizard". During the process, I use my DSN as the source, SQL Server as the destination, use a valid query to pull data from Filemaker, and set the mappings.

Each time I try to run the package, I receive the following message: The "output column "LastNameFirst" (12)" has a length that is not valide. The length must be between 0 and 4000.

I do not understand this error exactly, but in the documentation for ODBC: http://www.filemaker.com/downloads/pdf/fm9%5Fodbc%5Fjdbc%5Fguide%5Fen.pdf (page 47) it states:

"The maximum column length of text is 1 million characters, unless you specify a smaller Maximum number of characters for the text field in FileMaker. FileMaker returns empty strings as NULL."

I'm thinking that the data type is too large when trying to convert it to varchar. But even after using a query of SUBSTR(LastNameFirst, 1, 2000), I get the same error.

Any suggestions?

Answer 1

HAve you tried nvarchar (max)?

Answer 2

I had this problem, and don't know the cause but these are the steps I used to find the offending row:

-in filemaker, export the data to CSV -open the CSV in excel -double click on the LastNameFirst column to maximize its width -scroll down until you see a column '#########' -the way excel indicates data that is too large to be displayed.

I'm sure theres a better way, and I'd love to hear it!