Conversion error in ADO.Net/ODBC data export from MSSQLServer to MySQL

[thierry mignard]

Hi,
I am exporting data from a SQL Server 2008 R2 database to a MySQL 5.x database.
I am using an dtsx package that deletes all the records for each table and then copy each table data.

All works fine except when a table contains a numeric field with a null in first row, then the process acts as if the null was char type and when there is a numeric value in the next rows, the system stops and the following error occurs :

Erreur : 0xC020844B à Copie tbl_personne, ADO NET Destination [1093]: Une exception s'est produite lors de l'insertion des données ; le message retourné par le fournisseur est : Impossible d'effectuer un cast d'un objet de type 'System.Decimal' en type 'System.Char[]'.

Did someone met this kind of error ? Does anyone has a solution ?

!!! If the value in first row of the numeric field is numeric (ie 6), then no problem, null and numerics are well writen.

This wordked fine with DTS in SQLServer 2000.

Process description
Delete on the destination table to empty it before copying the all the records in the table :

SQL Request Task which performs "delete from bc2e_tampon_tmi.tbl_personne" called from the Control Flow.

Copy :

Data flow task which calls two ADO NET, one for the SQLServer source thru and one the MySQL destination thru ADO.Net and ODBC.

I also tried with an OLDB Source, but got the same error.

I have tried data conversion between source and destination (with this kind of formula : isnull(MyField) ? null(dt_numeric,10,0) : MyFiled) but always the same error.

Thanks in advance for your help



Edited 3 time(s). Last edit at 06/15/2011 04:35AM by thierry mignard.