mysql error while executing SSIS package

I am tansfering data from MSSQL to MySQL using SSIS.I have done mapping of all the tables.But while executing the package I am getting errors :

[tabl_name [1056]] Error: An exception has occurred during data insertion, the message returned from the provider is: ERROR [07006] [MySQL][ODBC 3.51 Driver][mysqld-5.1.33-community-log]Restricted data type 开发者_如何学Goattribute violation(SQL_C_NUMERIC)

[product [402]] Error: An exception has occurred during data insertion, the message returned from the provider is: Unable to cast object of type 'System.Int32' to type 'System.Char[]'.

I also compared the data type of all the columns and I found it same. After that I did some research and found there is problem with data type decimal(10,2).But I dint find the solution.

Thanks in advance.

One simple way to get out of this kind of problem is to temporary convert that problematic column from decimal to varchar, after complete the migration, convert it back to decimal.

The above can apply to MSSQL since the error is complained at MSSQL.

Either use a data conversion in the data flow task or cast your invalid types in the source query.

DECLARE @bit as bit
set @bit = 1
SELECT CAST(@bit as int)

I got the same error while converting from Access to ODBC (on 64-bit MS JET won't go anymore). My FldSeqnr field is decimal(15,0) definition:

        Dim gtSelectSQL As String = "SELECT FldText FROM Data WHERE CollCode = ? AND CollSeq = ? AND FldCode = ? AND FldSeqnr = ?"
        Dim DRgtSelect As odbcDataReader = Nothing
        Dim DbCgtSelect As odbcCommand
        Dim gtSelP1 As New odbcParameter("@CollCode", odbc.odbcType.VarChar, 4)
        Dim gtSelP2 As New odbcParameter("@CollSeq", Odbc.OdbcType.Int, 4)
        Dim gtSelP3 As New odbcParameter("@FldCode", odbc.odbcType.VarChar, 4)
        Dim gtSelP4 As New odbcParameter("@FldSeqnrs", odbc.odbcType.Decimal, 16)
        DbCgtSelect = New odbcCommand(gtSelectSQL, DbConn)
        Dim NrErr As Integer = 0
   Retrysql:
   Try     
        dSelP1.Value = CollCode
        dSelP2.Value = CollSeq
        dSelP3.Value = p.FldCode
        dSelP4.Value = strt
        dSelP5.Value = endn
        DRdSelect = DbCdSelect.ExecuteReader()
        Do While (DRdSelect.Read())
             ...
        End Do
    Catch ex As Exception
        If ex.Message.Substring(0, 13) = "ERROR [07006]" Then
           If NrErr = 0 Then
               NrErr += 1
               GoTo retrySql
           End If
        End If
        MsgBox(ex.ToString())

I know it's awfull, but it works: the error appears ONLY on the first ExecuteReader. All others go well.

I got the same big headache this week. I tried many ways. Thanks God, finnally, one of them worked. Hope it could help you a little bit.

For some columns with the data type of Int, datetime, decimal....,here, I identified as ColumnA, and I used it as datetime type.

1. in Data Flow Source, use SQL Command to retrieve data. Sth like select isnull(ColumnA,'1800-01-01') as ColumnA, C1, C2, ... Cn from Table

Make sure to use Isnull function for all columns with the datatype mentioned before.

1. Excute the SSIS pkg. It should work.

2. Go back to Control Flow, under the data flow task, add SQL Task control to replace the data back. I mean, update the ColumnA from '1800-01-01' to null again.

That works for me. In my situation, I cannot use ignore failure option. Because if I do, I will lose thousands rows of data.