Posts Tagged ‘Integration Services’

Using MySQL BLOB Data via ODBC in SSIS, SQL Server & Business Objects

Whilst trying to build a centralised cross-platform alerting system I spotted a peculiar issue when trying to move the output of a SHOW FULL PROCESSLIST command on MySQL via ODBC.  It seems that the output of the SHOW FULL PROCESSLIST command returns both integers and binary (BLOB) data types even though to they eye (that is, in the MySQL Query Browser) most of the columns appear to be short text fields.

Despite the fact that the data looks like text whenever I tried to return the data into an application, I tried SSIS, SQL Server Linked Servers and Business Objects, each time the data would come back unusable or an error would be returned.  Business Objects gave me the key by declaring “This is a BLOB.” as you can see in the following screenshots…

SQL Server Integration Services

SQL Server Linked Server

Business Objects Desktop Intelligence

As with my recent post about loading data into MySQL with SSIS the saviour turns out to be an ODBC configuration setting, this time in the Metadata tab of the MySQL ODBC driver.  All you have to do is check the “Always handle binary function results as character data” and instantly your problems will be solved…

Out of a crazy fit of completeness I also took screenshots of the final results and it’d be a shame to waste them so here they are…

SQL Server Integration Services

SQL Server Linked Server

Business Objects Desktop Intelligence

Loading Data into MySQL with SSIS via ODBC (sql_mode = ANSI)

I was performing a test earlier, a proof-of-concept to show that we could use Integration Services to move data from one platform (PostgreSQL) to another (MySQL) without SQL Server itself being involved at all. 

Unfortunately I hit a slight snag on the first simple attempt, I could read the data without a hitch but despite multiple attempts at tweaking SSIS settings and reconfiguring connections I could not manage to get data into the MySQL target table.  Amongst other messages in the Output window in BIDS I could see the following error…

[MySQL][ODBC 5.1 Driver][mysqld-5.0.45-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘”tblDestination” (“col1″, “col2″, “col3″, “col4″, “col5″, “c’ at line 1

After some Googling I determined that the issue was down to an incompatibility between the SQL being generated by SSIS and the default MySQL engine, specifically that MySQL was not setup to accept ANSI compliant SQL.  Most of the solutions out there seemed to require that the entire server was configured in the ANSI compliant mode which was a problem for me as it’s a shared server and I can’t go reconfiguring it at will.  Thankfully MySQL provides the ability to set the mode on a per connection level with the following command…

SET SESSION sql_mode= 'ansi';

The trick is – how to get SSIS to run this before inserting the data without messing with the package itself?  The easiest way is to have the ODBC driver send the command at the point SSIS opens the connection, this can be configured in the settings of the ODBC source…

This is a relatively common feature of ODBC drivers and allows you to setup initial configuration options for this exact reason, now I know that I will not impact any other users of the server and my SSIS package works just fine.

How to Enable 32-bit Runtime Mode in SSIS

If you’re just beginning to play with SSIS on a 64-bit box you’re pretty likely to hit a roadblock if your data sources have 32-bit ODBC drivers.  The error will become obvious immediately when you come to execute your package in BIDS (Business Intelligence Development Studio)…

The ODBC Driver Manager error reported in the Output window will be: ”The specified DSN contains an architecture mismatch between the Driver and Application”.  Thankfully this is a pretty easy one to sort out, all you have to do is…

1. Right-click on the Project name in the Solution Explorer and select Properties…

   

2. Choose “Debugging” in the left-hand pane…

   

3. Change the Run64BitRuntime value to False (by double-clicking on it). 

   

   For reference the help-text states that if the 64-bit SSIS runtime is not installed this setting is ignored so if you’re working on a 32-bit box in all liklihood you wouldn’t experience this issue. 

   

4. Click OK and re-run your package. 

My Favourite SQL Server Features

SQL Server MVP Ken Simmons is offering the chance to win one of three MSDN subscriptions on his blog, I thought I’d join in by writing a post describing my favourite features of SQL Server.

The ‘big ticket’ item for me is SQL Server Integration Services. For those of you stumbling across this post from non-SQL backgrounds SSIS is Microsoft’s ETL tool and has been bundled with the database engine since SQL Server 2005. Essentially SSIS allows you to extract data from (or deliver data to) a range of ODBC, OLE DB, ADO.NET or flat file data sources including Oracle, DB2, MySQL, Excel, CSV files and of course – Microsoft SQL Server. Along the way you can make use of a range of tasks and components that allow you to transform the data and perform operations such as looping, FTP, Email, Lookup Values, Data Mining, Aggregation, Process Dimensions and many many more .  There are alternatives out there but many are prohibitively expensive for the small-medium enterprise (e.g. Informatica, BODI) – that SSIS comes bundled with SQL Server still astonishes me to this day.

As for my favourite ‘little’ features I’d have include:

Linked Servers, a few people give it a bad rap since they can be misused but I’ve made great use of Linked Servers to simply data assurance processes and join data in queries running across both MySQL and Sybase data sources in a single statement.

Copy and Paste Coloured SQL, I’m not sure if it’s just because I’m used to Management Studio but I often struggle to read SQL if it’s not coloured-in. Colouring SQL improves readability and since SQL Server 2005 I’ve enjoyed the ability to copy and paste directly from Management Studio into emails or word documents and retain the coloured keywords, it makes documentation much more interesting.

Common Table Expressions, it took me a while to get into the swing of using CTEs and then for a while I probably over-used them before settling down. They’re often a great way to make statements with nested subqueries much more readable and much more elegant in design but whenever I go back to SQL Server 2000 or MySQL I really miss CTEs.

SQL Server Express, it might be a bit of a cheat to include an edition as a feature but the fact that the core SQL Server database engine is available for free is brilliant and allows a lot more people to try out SQL Server and see the difference between a slick GUI like Management Studio Express vs the MySQL toolset.

Intellisense, I couldn’t resist choosing this as one of the more modern features I love about SQL Server. Once you’ve gotten used to it Intellisense makes trotting out T-SQL statements a breeze and you know what? It just feels cool, albeit in a really geeky sense of the term.