Posted on April 29, 2015 by Jose Chinchilla aka SQLJoe
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:
SQL Server Integer and its variations data type documentation can be found at:
Filed under: Uncategorized | Leave a comment »
Posted on October 27, 2013 by Jose Chinchilla aka SQLJoe
This year I was honored to sponsor and speak at IT Pro Camp Tampa 2013. Like always, it is a great pleasure to give back to the community and get to know people from different technical backgrounds. The event was very well-organized and the venue was great.
This year the entire Agile Bay team spoke at IT Pro Camp delivering 4 sessions in total for Track 2: Business Intelligence & Big Data (SQL Server, Hadoop, etc.). You can download the PowerPoint presentations by clicking on the session links below:
- Wes Springob | Introduction To Reporting Services
- Jose Chinchilla & Jon Bloom | Introduction to Big Data
- Jonathan Bloom | Introduction to T-SQL
- Jose Chinchilla | Introduction to Microsoft BI
Wes Springob deliverin Introduction to SSRS
IT Pro Camp Tampa 2013 Attendees
Filed under: Big Data, Business Intelligence, Community, Hadoop, IT Pro Camp, Tampa BI User Group | Tagged: #sqlserver, 2005, 2008, business intelligence, community, data warehouse, IT Pro Camp, Server, SQL, ssas, SSIS, tampa | Leave a comment »
Posted on August 7, 2013 by Jose Chinchilla aka SQLJoe
I recently downloaded the Hortonworks Sandbox v1.3 Hyper-V VM and had a hard time connecting to the included web interface that comes with it. I downloaded and followed the instructions to configure the VM in my Hyper-V server. As of this writing, the instructions point you to take note of the IP address that shows up in the console screen. The default IP address is 192.168.56.101 as shown in the next figure.
As part of the instructions you are directed to create a dedicated Virtual Switch in Hyper-V that is then given shared internet connectivity with your physical network adapter (wireless or Ethernet). The issue is that the vSandox virtual switch gets assigned an IP address from a random subnet in the 192.168.xxx.xxx range that is not in the same subnet as the Hortonworks Sandbox default IP address (192.168.56.101).
In my case, my virtual switch vSandbox got an IP address of 192.168.137.100. The solution is to change the vSandbox’s IP address to an IP address within the same subnet range as Hortonworks Sandbox default IP address, for example: 192.168.56.100 with the default Subnet Mask 255.255.255.0 and no Default Gateway or DNS as shown in the next figure.
Once you apply the new IP address for vSandbox virtual switch, you should be able to reach the Hortonworks Sandbox web interface by typing http://192.168.56.101 on your browser as show in the next figure.
Filed under: Big Data, Errors, Hadoop | Tagged: big data, hadoop, horton works | 4 Comments »
Posted on March 17, 2013 by Jose Chinchilla aka SQLJoe
This past Saturday March 16th, I had the opportunity to speak once again the .Net Orlando Code Camp. I presented a session under the SQL Server track titled “Change Data Capture, a developer’s best unknown friend”. I had better than expected turn-out with about 20 attendees and overall very good participation.
I was glad to hear from more than one attendee that they were planning to use Change Data Capture in their environment and best of all to get rid of triggers!
I had the opportunity to catch-up with some of my friends and fellow SQL-ers from PASS. Also, met some new folks from the App/Dev community. These type of events are always great to expand your network, share your knowledge and learn something new!
Thanks to the organizers, sponsors, volunteers and attendees to make this event a success and for having me once more.
You can download my PowerPoint presentation and demo files at: http://sdrv.ms/15UUX64
Filed under: Change Data Capture, Code Camp, Data Warehouse, ETL, How to, SQL PASS | Tagged: #sqlserver, 2008, CDC, Change Data Capture, code camp, community, ETL, extract, incremental loads, load, SQL, sqlpass, sqlsaturday | Leave a comment »