Introduction

Like many large organizations, the company I work for has a heterogeneous database environment. We have at least four RDBMSs in use (five, if you count MS Access) to support the various lines of business. That list includes major, multiple instances of SQL Server, Oracle, DB2 and MySQL, and some of those came as part of certain vendor packages. This article concerns our adventures integrating data from MySQL into SQL Server for reporting purposes.

One of the instances of MySQL that we need to pull data from to populate a SQL Server data warehouse is the database used by SANscreen. This is a product developed by Onaro and now part of the NetApp product suite. It provides information on storage allocations and usage in our SAN. Developing an ETL process for this data load has been challenging, even frustrating at times, but very satisfying as the barriers we encountered along the way began to fall.

Getting connected

The first thing we did was install the MySQL ODBC drivers into our development instance of SQL Server and verify that we could at least connect to the SANScreen database. That worked and we could see the SANScreen tables under the Linked Servers node of System Objects in SSMS. On to the queries!

We learned right away that we could not use SQL Server's four-part naming to query the MySQL tables since the MySQL ODBC driver does not suport the required interfaces. So, we built views using OPENQUERY that work quite well. A typical view looks like this:

CREATE VIEW MySQLTable
AS
SELECT col1, col2, col3
FROM OPENQUERY(SANSCREEN, '
    SELECT col1, col2, col3
    FROM MySQLSchema.MySQLTable;
');

No problem!  We used the views to build ETL packages in Integration Services and the world was a happier place. (Note: we found we had to wrap the outer column list in CAST(column AS type) since the ODBC driver does not always provide correct type mapping from MySQL to SQL Server.) After a few runs however, it became obvious that were pulling largely the same data every night for some of the biggest tables.

With 20/20 hindsight we realized that this was not unexpected, since those tables are essentially data warehouse fact tables. Only a few hundred or thousand rows were new for any given run. The rest (millions of rows, in some cases) were from older transactions. An incremental load approach was needed.

An Incremental Approach

Writing incremental loads in Integration Services means that you need some column you can use to define the start of the new data. That is commonly a column containing a transaction date or something that can be mapped to such a date, though of course there are other possibilities. You want to be able to restrict your query to rows that are new with respect to that column. Then, you want to save the new, maximum value of that column to use for the next incremental load. We found columns that mapped to dates and decided to use those. Adding those dates to our query created a new challenge.

We didn't want to retrieve the whole table from MySQL and then filter out the new rows. That would defeat one of our key goals in the incremental load approach: only pull data from MySQL that we need. So, we needed to pass that date to the call to OPENQUERY. Unfortunately, OPENQUERY does not support a variable as its second parameter. Our nice, neat views would not do the job. We needed new stored procedures. The procedures have the general form:

CREATE PROCEDURE usp_MySQLTable
    @StartDate datetime = null
AS 
DECLARE @query varchar(max) = '
    SELECT col1, col2, col3
    FROM MySQLSchema.MySQLTable
    WHERE datecol >= ''{StartDate}'';
';
SET @query = REPLACE(@query, '{StartDate}', @StartDate);
EXEC sp_executesql @sql;

(Note: I like to declare a variable containing the whole query including place-holders for variables, and then replace those variables before executing the query. This way, I can see the whole query at a glance. I find this easier to follow than alternatives that assemble the query from parts. YMMV.)

Voila!  A stored procedure that calls MySQL with a variable start date!  This worked like a charm in all our unit tests. So, now we went on to build SSIS packages that use the new procs. More pain ensued...

Trouble ahead

Since we were no longer working with views, there has to be some way for Integration Services to figure out the metatdata for the source adapter. Since we are using OLE DB Sources in our packages, we changed the input from Table or View to SQL Command. The commands look like:

EXEC usp_MySQLTable ?

and we put a variable holding a default starting date in the Parameters section. (As an aside: For some reason, when you do this, the Integration Services designer cannot preview the results. It throws an error, complaining that parameters were not supplied, even though they clearly are.) Once we had changed the view to the call to the stored procedure, I clicked on Columns to make sure that everything was still OK. We waited...and waited...and waited some more...until the designer threw an error saying that the query has timed out!  What on earth was going on?  To find out, we fired up SQL Server Profiler to observer.  we found a sequence like this (reformatted for readability):

DECLARE @p1 int
SET @p1 = 0
EXEC sp_prepare @p1 OUTPUT
     , N'@P1 datetime'
     , N'exec  [dbo].[usp_MySQLTable] @StartDate = @P1;'
     , 1
SELECT @p1
SET NO_BROWSETABLE ON
SET FMTONLY ON
EXEC sp_execute 1
     , '01-01-1992 01:01:01:000'

Of course, SSIS needs to know the metadata, hence the SET FMTONLY ON. But where did that weird date come from?  We had not specified January 1, 1992 as the start date!  (Note that this remains a mystery.)  The consequence of the bogus date is that MySQL is asked to return the entire table, all four million rows of it. Since this is inside an OPENQUERY, SQL Server cannot know what the metadata is until something is returned. MySQL chose to process a large portion of the table before returning anything to SQL Server, hence the timeout.

The amazing FMTONLY trick

What to do?  Well, we thought if we could somehow limit the results returned by MySQL, in spite of the bogus date of 1 January 1992, we might be able to avoid the timeout and make the package design less frustrating as well. Reading through the MySQL docs, we saw that there is a LIMIT clause available for MySQL queries.

That's it! we thought. Now, we needed a way to include that in the query, but conditionally, since we only wanted to do that when FMTONLY was ON. So how could we know that?  BOL was no help, but some Internet searches turned up this gem: https://devio.wordpress.com/2013/01/15/the-amazing-fmtonly-trick/. In a nutshell, when FMTONLY is ON, SQL Server tries all conditional branches in the stored procedure, looking for metadata that it can use. That means that this sequence:

SET FMTONLY ON 
DECLARE @fmtonly bit = 0; 
IF 1 = 0 SET @fmtonly = 1;

will actually set the variable @fmtonly to 1, since FMTONLY is indeed ON. What we wanted to do is to add a LIMIT clause. So, to begin with, we modified the query template in the stored procedure to:

DECLARE @query varchar(max) = '
   SELECT col1, col2, col3
   FROM MySQLSchema.MySQLTable
   WHERE datecol >= ''{StartDate}''
   /* LIMIT */;
';

Note that adding a comment to the query passed to MySQL does not have any ill effects. However, it gives us a place to insert a proper limit clause. So, before actually executing the dynamic SQL, we added this one line:

IF 1=0 SET @query = replace(@query, '/* LIMIT */', 'LIMIT 1');

The effect?  It worked!  Now, when SSIS does its FMTONLY run, only one row is returned from MySQL, almost instantly. No more lengthy waits and time outs!

Summary

We faced a real challenge ETL-ing data from MySQL in an incremental fashion.  Discovering and exploiting the way SQL Server processes code when SET FMTONLY ON is in effect allows us to write Integration Services packages without lengthy waits or timeouts from the MySQL server.

By the way, I am still looking for official documentation that describes the full effects of SET FMTONLY ON, especially the effect of processing all conditional branches in T-SQL.