MySQL Bugs: #31854: mysql-connector-net-5.1.3 SQL Server 2005 SSIS BUG

Bug #31854	mysql-connector-net-5.1.3 SQL Server 2005 SSIS BUG
Submitted:	25 Oct 2007 14:51	Modified:	7 Jan 2008 10:33
Reporter:	Kenneth Kolk	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.1.3	OS:	Windows (Incorrect Datatype)
Assigned to:		CPU Architecture:	Any

Description:
The Connector is reading the MYSQL Time value as DT_I8 and will not import the data at all, even on a default. It should be reading it as a datetime or smalldatetime so it can be parsed at a time value instead of DT_I8. DT_I8 cannot be converted see below errors...

Error 0xc0202044: Data Flow Task: The OLE DB provider used by the OLE DB adapter cannot convert between types "DT_I8" and "DT_DBTIMESTAMP" for "startTime".
(SQL Server Import and Export Wizard)

Error 0xc0202044: Data Flow Task: The OLE DB provider used by the OLE DB adapter cannot convert between types "DT_I8" and "DT_DBTIMESTAMP" for "endTime".
(SQL Server Import and Export Wizard)

Error 0xc0202044: Data Flow Task: The OLE DB provider used by the OLE DB adapter cannot convert between types "DT_I8" and "DT_DBTIMESTAMP" for "arrivedTime".
(SQL Server Import and Export Wizard)

Error 0xc0202044: Data Flow Task: The OLE DB provider used by the OLE DB adapter cannot convert between types "DT_I8" and "DT_DBTIMESTAMP" for "timeIn".
(SQL Server Import and Export Wizard)

Error 0xc0202044: Data Flow Task: The OLE DB provider used by the OLE DB adapter cannot convert between types "DT_I8" and "DT_DBTIMESTAMP" for "timeOut".
(SQL Server Import and Export Wizard)

Error 0xc0202044: Data Flow Task: The OLE DB provider used by the OLE DB adapter cannot convert between types "DT_I8" and "DT_DBTIMESTAMP" for "depTime".
(SQL Server Import and Export Wizard)

Error 0xc0202044: Data Flow Task: The OLE DB provider used by the OLE DB adapter cannot convert between types "DT_I8" and "DT_DBTIMESTAMP" for "waitTime".
(SQL Server Import and Export Wizard)

There is no way to correct the reading of this as a DT_I8 in the application.

How to repeat:
Using SSIS packages with SQL Server 2005, connect to the MYSQL database utilizing the mysql-connector-net-5.1.3 define attempt to run the SSIS package and receive the above error. As you can see this is happening on multiple fields not just a single field value.

Suggested fix:
The Connector should read the TIME Field in MYSQL as a datetime or even smalldatetime, it can then be parsed by SSIS and pulled into time values where needed. It can also be calculated using SQL Server default date time functions.

Any status update on this?  I still have not been able to resolve the problem.

Hi Kenneth and thanks for your report. Unfortunately I have to many of them to satisfy everybody. If you want quicker response and custom builds you should contact sales@mysql.com and acquire a contract.

Now, let me ask you a few things as you seem to be using quite a lot of 3rd party SW here... What is the purpose of NET/OLEDB bridge when there's native NET connector (c/NET)? Is that the way your SSIS (whatever that might be) package works? Following on that, did you tried MyODBC instead?

Actually I am only using SSIS.  "SQL Server Integration Services", is a .NET Microsoft Application built in 2005.  It comes with Microsoft SQL Server 2005 Enterprise Edition its a part of the SQL Server Business Intelligence Studio Suite.  It used to be DTS in 2000.  

Anyway, the ODBC connector does not present itself to SQL Server 2005, only the .Net Connector presents to SQL 2005 utilizing .Net 2.0.  

The SSIS package (Old DTS) basically ports the data through .NET connector.

Hi Kenneth and thanks for your explanation, DTS is something I understand :-)

Now, for one, I do not have VS2005 Enterprise nor do I think any of us does so this may represent a bit of problem... It is impossible to have every piece of SW released. As for problems with "SQL Server Business Intelligence Studio Suite" it is known to us (see Bug#30230 although most of it is private). Let me try to picture the problem; the current version of BI module has not been made for use with anything but Microsoft SQL Server Databases and here's one example why. BI Reporting Services can use only Microsoft-style queries such as "SELECT * FROM [mydb].[mytable]" instead of "SELECT * FROM `mydb`.`mytable`" or something else. They simply do not respect RDBMS-specific attributes such as SQL_IDENTIFIER_QUOTE_CHAR or SQL_CATALOG_NAME_SEPARATOR. Well, this is not a new behavior from MS and we probably should wait for another SP... We failed to use it with Interbase, Oracle and our drivers.
Although we don't see it as our bug we will still try to workaround in c/NET 5.1.

Now, what about my other questions? It appears from our research that one has to use OLEDB provider so there's no chance of using just c/NET right? Can you check on that for me please? Also, a DM trace of ODBC calls might be helpful. Just go to Control Panel/Administrative Tools/Data Sources, find "Tracing" tab and start tracing ODBC calls right before activating BI.

Well, I managed a work around that seems to be handling the issue.  I basically created a linked server with the MYSQL Database and defining the DNS connections directly within the server profiles.  I did this using the ODBC you suggested which creates a System DNS and not a SQL Server presentation.  I then created stored procedures to read the tables utilizing TSQL.  This did work with one exception, the Time stamp still would not represent to SQL Server.  It did however fix the Time field issue but the Timestamp issue

Kenneth, please provide as much info as possible. For a start, attach DM trace log (as described) and MDAC info (What is your MDAC version?). Having a bridge like this is always a bad idea and we'll probably have a lot more troubles until this is resolved.

Kenneth, I spent some time with SSIS and to tell you the truth I have many problems with it... Can you attach SQL dump of small database with your test data and a VS BI project I can load and test with?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".