Issues with ODBC Destination MySQL (AWS)

[russell...@monster.co.uk]

HI,

I am hoping someone can help, I am evaluating the cozyroc software to see if we can copy data from on premise SQL Server SQL 2008 R2 to AWS MySQL RDS. At the moment and I have the following configured in a very basic package:

1. Source System is SQL Server 2008 R2

2. Source connection used in the data flow task is a .Net Provider\SqlClient Data Provider configured to connect to the SQL server.

2. Source table definition is:

CREATE TABLE [dbo].[Messages](

[MessageID] [int] NOT NULL,

[ChannelID] [int] NOT NULL,

 CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED 

(

[MessageID] ASC,

[ChannelID] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

3. Destination system a MySQL instance version 2.6 running on Amazon Web Services RDS

4. Destination connection is the cozyroc ODBC destination, configured to connect to the AWS RDS MySQL instance

5. Destination table definition is:

CREATE TABLE `messages` (

  `MessageID` int(11) NOT NULL,

  `ChannelID` int(11) NOT NULL,

  PRIMARY KEY (`MessageID`,`ChannelID`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

When I try and copy the data when running the package I get this error:

Warning: 0x8002F304 at Data Flow Task, ODBC Destination [20]: A problem occurred with the following messages: "Unable to detect bulk load support.".

Error: 0xC002F304 at Data Flow Task, ODBC Destination [20]: An error occurred with the following error message: "

[Microsoft][ODBC Driver Manager] Invalid string or buffer length [SQLSTATE: HY090]".

Error: 0xC02090F9 at Data Flow Task, ODBC Destination [20]: The ODBC Destination was unable to process the data.

Any ideas where I am going wrong?

[Ivan Peev]

Hi Russell,

I suspect you are using MS ODBC driver. That is incorrect. You should be using MySQL ODBC driver.

[russell...@monster.co.uk]

My ODBC DSN is configured using the MySQL ODBC 5.3 Unicode Driver for communicating with the MySQL instance so I don't think its that.

I actually managed to make some progress on this and now it does work to an extent.  The simple data flow fails after processing 410K rows of 3M.  It then fails with this pretty much the exact same error:

Warning: 0x8002F304 at Data Flow Task, ODBC Destination [23]: A problem occurred with the following messages: "Unable to detect bulk load support.".

Error: 0xC002F304 at Data Flow Task, ODBC Destination [23]: An error occurred with the following error message: "

[Microsoft][ODBC Driver Manager] Invalid string or buffer length [SQLSTATE: HY090]".

Error: 0xC02090F9 at Data Flow Task, ODBC Destination [23]: The ODBC Destination was unable to process the data.

[Ivan Peev]

Russell,

How did you make it work to 410k records? Do you execute in 32bit or 64bit mode?

[russell...@monster.co.uk]

I managed to get this working in the end.  I tried several options changing between 32bit and 64 bit run times, changing between 32bit and 64 bit MySQL ODBC drivers for the DSN, changing the MSSQL source between OLE and ADO.   There was mixed results, in some cases it would process more or less rows but never the complete set.

In the end this worked for me:

1, Set the package to run in 64 bit mode

2. Use OLE DB Connection to the source MSSQL database.

2. Configure the ODBC DSN for MySQL to use the MySQL 32bit ANSI driver for the destination.

3. Set the package property DefaultBufferMaxRows to 5000, leave the DefaltBufferSize set to 10MB

4. Set the ODBC Destination to 5000 rows per batch

The usage of the ANSI driver seems strange to me as the data is all numeric, but in the end this is now working.