SSIS 2008 mySQL source to SQL 2008

I need to copy data from a remote mySQL database into MSSQL Server 2008 database using SSIS 2008 package. I have some Timestamp fields in the mySQL database giving me problems. When l excluded the Timestamp fields l managed to copy the data nicely. On the OLE DB Destination Input - Input Columns the field which is Timestamp on mySQL database is showing as having data type DT_Bytes.

I am getting the error An OLE DB record is available.
Source: "Microsoft SQL Server Native Client 10.0" Hresult: 0x80040E21 Description: "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done." [OLE DB Destination [784]] Error: Cannot create an OLE DB accessor. Verify that the column metadata is valid.

My select statement to the mySQL source has got a select case to handle 0 dates it is like; SELECT case modified_date when day(modified_date) = 0 then '1990-01-01 00:00:00' else modified _date end as modified from mySQLTableName

The problem is definitely with your data types. Open up your Destination component for editing. Go the mapping tab. Hover over each column mapping, first the source column, then the destination. The tooltip will show you what the data types of each column are. One of them doesn't match.

It's most likely your timestamps field, but it might not be, so I won't speculate.

In any case, to make them match, add a Data Transformation component in between your source and destination, and recast the offending source column to the data type you want for inserting into SQL Server. (If the recasting also causes an error, it'll be a lot more helpful one than this one!)