When working with a MySQL database as a source you may experience some unexpected results in your data extracts. Most of the issues are related with handling of certain data types such as “TinyInt”.

A particular issue I recently came upon was the fact that TinyInt(1) is treated by default in SSIS as a “Boolean”. You can see this setting in the ADO.Net provider configuration under the All section as shown in the image below.

Columns with data type of Boolean are expected to have values of either 1 (True) or 0 (False) or Null. But, TinyInt(1) data type allows values other than 1 or 0. At this particular client, columns defined as TinyInt(1) had values ranging from 1 to 9. Because of the default setting in the ADO.Net driver, only values of 1 and 0 were coming through as all values that were not 1 were turned into 0.

As a recommendation when working with MySQL, check for data types like these that could be interpreted differently depending on your driver. A good practice is too refer to an existing data dictionary or profile the data source before you start coding away. Also, check the documentation online, in particular for MySQL you can find it at http://dev.mysql.com/doc

As a side note, also check the values allowed for each data type. TinyInt allows different range of values depending if it is signed and unsigned. For example, in MySQL signed TinyInt allows values ranging from -128 to 127 while unsigned allows values from 0 to 255. SQL Server only allows values of 0 to 255 for TinyInt. In other words, SQL Server only supports unsigned TinyInt. You will need to use SmallInt at a minimum in SQL Server to support signed Tinyint data types.

There is an additional type of integer in MySQL called “MediumInt” that allows values greater then “SmallInt” but smaller than “Int”. In this case you will need to use Int data type in SQL Server at a minimum.

MySQL Integer (Exact Value) and its variations data type documentation can be found at:
https://dev.mysql.com/doc/refman/5.0/en/integer-types.html

SQL Server Integer and its variations data type documentation can be found at:
https://msdn.microsoft.com/en-us/library/ms187745.aspx