Hi, I have been experiencing problems whilst trying to export data from a Database in Filemaker 6 to an MS SQL Table via an SSIS package. I have been able to do this successfully with Filemaker 10, so fail to understand what the hindrance is. I know it would be logical to do the transfer with Filemaker 10 then since successful, but unfortunately time constraints make this unviable. I would be much obliged to be pointed in the right direction as it is urgent. In my SSIS package, under the Data Flow component, my DataReader Source (the Filemaker 6 ODBC Data Provider Connection Manager has been tested and verified), reads the Filemaker data, and maps its columns accordingly as long as data types are set to Number (File sharing is turned on in Filemaker as required). If I change these to Text, the DataReader Source fails to map the columns (even when the appropriate Validation settings are selected), and comes up with the following error message: "Error at Data Flow Task[DTS.Pipeline]: The "output column "<column name>"(<numeric value>) has a length that is not valid. The length must be between 0 and 4000. Error at Data Flow Task [DataReader Source[1473]]: System.Runtime.InteropServices.COMException(0xC020 4016): Exception from HRESULT: 0xC0204016..." If I go back to Filemaker and change the data types back to Number, set a Destination (such as a Flat File) from the DataReader Source and then run / execute the package, the flow crashes at the DataReader Source and produces the following error message: "Error: 0xC02090F5 at Data Flow Task, DataReader Source [3377]: The component "DataReader Source" (3377) was unable to process the data. Error: 0xC0047038 at Data Flow Task, DTS.Pipeline: The PrimeOutput method on component "DataReader Source" (3377) returned error code 0xC02090F5....". Can someone please enlighten me on what I might be doing wrong? I would like my data types in Filemaker to be set accordingly, i.e. Number or Text when appropriate, and not restricted to one data type. I also know I can then transform this Filemaker data type to a MS SQL equivalent with the "Data Conversion", but at the moment I am stuck at the DataReader Source trying to process my import. Many thanks. Uju Onwuachu

I think this link from the Microsoft SQL Server help forum will help you: After Installation of .NET Framework 3.5 SP1 (or .Net framework 2.0 SP2) , SSIS packages using ODBC 3rd party drivers may fail Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

I think this link from the Microsoft SQL Server help forum will help you: After Installation of .NET Framework 3.5 SP1 (or .Net framework 2.0 SP2) , SSIS packages using ODBC 3rd party drivers may fail Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Thanks MMilligan. I have read the details in the link you referred me to, and it does not seem to be related to the problem I am experiencing. It also suggests I uninstall .Net framework 2.0 SP2 from my PC and I am worried about the rammifications of doing that. What are your thoughts please? Uju.

Thanks MMilligan. I have read the details in the link you referred me to, and it does not seem to be related to the problem I am experiencing. It also suggests I uninstall .Net framework 2.0 SP2 from my PC and I am worried about the rammifications of doing that. What are your thoughts please? Uju.

Look at workaround #1 first and analyze the contents of the error logs it produces. If you're uncomfortable with workaround #2 or you don't think it will resolve your problem then save it as a last resort when you've tried all other avenues. Since this isn't a package that was ever working and the article seems to be about packages that were working and suddenly quit that link may not have anything to do with your problem. Redirecting the error rows to a flat file is a good suggestion, though so you can analyze the results. That is what I was hoping you would take away from the article. Sorry, I should have been more specific. These things can be pretty tricky. If you must get this working quickly you may want to schedule a virtual mentoring session with the company I work for, Pragmatic Works. Otherwise, I'll try to do what I can from this medium. Also, your explanation of the problem is a little unclear. When you state: "and maps its columns accordingly as long as data types are set to Number " Are you saying that it maps all the column types to number by default? Are they numbers? Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Look at workaround #1 first and analyze the contents of the error logs it produces. If you're uncomfortable with workaround #2 or you don't think it will resolve your problem then save it as a last resort when you've tried all other avenues. Since this isn't a package that was ever working and the article seems to be about packages that were working and suddenly quit that link may not have anything to do with your problem. Redirecting the error rows to a flat file is a good suggestion, though so you can analyze the results. That is what I was hoping you would take away from the article. Sorry, I should have been more specific. These things can be pretty tricky. If you must get this working quickly you may want to schedule a virtual mentoring session with the company I work for, Pragmatic Works. Otherwise, I'll try to do what I can from this medium. Also, your explanation of the problem is a little unclear. When you state: "and maps its columns accordingly as long as data types are set to Number " Are you saying that it maps all the column types to number by default? Are they numbers? Convert DTS to SSIS | Document SSIS | 30+ SSIS Tasks | Real-time SSIS Monitoring | Quick Starts | BI Blitz

Hi MMilligan, I have looked at the workaround #1, and I am unable to replicate it at my end (selecting the columns [only with character data type], changing the ErrorRowDisposition and TruncationRowDisposition properties to RD_RedirectRow and redirecting the DataReader Error Output to a text file) because the DataReader Source only reads the columns with the Number data type. Columns or fields that have a Text (character) data type are not being read by the DataReader Source, but generate the error message: "Error at Data Flow Task[DTS.Pipeline]: The "output column "<column name>"(<numeric value>) has a length that is not valid. The length must be between 0 and 4000. Error at Data Flow Task [DataReader Source[1473]]: System.Runtime.InteropServices.COMException(0xC020 4016): Exception from HRESULT: 0xC0204016...". The package works successfully with Filemaker 10, but as I mentioned above, I am unable to do the data transfer with this Filemaker version because of time constraints. The package's DataReader Source is unable to process the data from Filemaker 6 it seems. Also, When I say, "and maps its columns accordingly as long as data types are set to Number ", I mean if the Data Source is Filemaker 6, the DataReader Source is unable to read the data unless the data type is set to Number in Filemaker 6. It is after the data has been read that mapping occurs under the Column Mapping tab. I look forward to your response. Many thanks.

Hi MMilligan, I have looked at the workaround #1, and I am unable to replicate it at my end (selecting the columns [only with character data type], changing the ErrorRowDisposition and TruncationRowDisposition properties to RD_RedirectRow and redirecting the DataReader Error Output to a text file) because the DataReader Source only reads the columns with the Number data type. Columns or fields that have a Text (character) data type are not being read by the DataReader Source, but generate the error message: "Error at Data Flow Task[DTS.Pipeline]: The "output column "<column name>"(<numeric value>) has a length that is not valid. The length must be between 0 and 4000. Error at Data Flow Task [DataReader Source[1473]]: System.Runtime.InteropServices.COMException(0xC020 4016): Exception from HRESULT: 0xC0204016...". The package works successfully with Filemaker 10, but as I mentioned above, I am unable to do the data transfer with this Filemaker version because of time constraints. The package's DataReader Source is unable to process the data from Filemaker 6 it seems. Also, When I say, "and maps its columns accordingly as long as data types are set to Number ", I mean if the Data Source is Filemaker 6, the DataReader Source is unable to read the data unless the data type is set to Number in Filemaker 6. It is after the data has been read that mapping occurs under the Column Mapping tab. I look forward to your response. Many thanks.

If you're severely time-constrained, you might consider exporting the data from FileMaker 6 into a flat-file format that you can change the SSIS data sources only in order to read from there instead of the FileMaker tables. BTW, do all your tables have primary keys? MS' ODBC has been known to produce strange errors that are fixed by adding a primary key to the source table. hth Mike

If you're severely time-constrained, you might consider exporting the data from FileMaker 6 into a flat-file format that you can change the SSIS data sources only in order to read from there instead of the FileMaker tables. BTW, do all your tables have primary keys? MS' ODBC has been known to produce strange errors that are fixed by adding a primary key to the source table. hth Mike

If you're severely time-constrained, you might consider exporting the data from FileMaker 6 into a flat-file format that you can change the SSIS data sources only in order to read from there instead of the FileMaker tables. BTW, do all your tables have primary keys? MS' ODBC has been known to produce strange errors that are fixed by adding a primary key to the source table. hth Mike