I recently inherited a SSIS package that moves a daily snapshot of a log table on a MySQL database over to a MSSQL database. The existing tasks truncate the destination tables and perform a fresh insert every night which works for now since the tables are relatively small. A log table, on the other hand, needs to be moved in increments because of it’s size.  I needed to be able to get the identifier for the last inserted row from my MSSQL table, set it as a variable in the SSIS package, and then pass it as a parameter in my query against the MySQL database. The following steps are shown in Visual Studio 2008.

The first step is to verify you have the correct MySQL drivers on your development machine. I used 5.0.5. You can download the necessary drivers for free from mysql.com.

Now create a solution and your SSIS package. Add a MSSQL connection, then a MySQL connection. To create a MySQL connection, right click in the Connection Managers pane and choose New ADO.NET Connection and then click New. In the Provider dropdown you should have an option for MySQL Data Provider in the .Net Providers tree. If not, you’ll need to add a MySQL Connector to Visual Studio. Go to the mysql.com download page, find the section for Connector/Net and download the database driver. After installation you’ll need to close and re-open Visual Studio for the connector to show.

I named my connection MySQL. In the image below you can see the MySQL Data Provider connection and the fields I filled in: Database, Server, Password, and User ID.

Now we will add two variables: one for the last LogID in our destination log table and another to assist with dynamically building the SQL command that we will send to the source MySQL database. The scope is the entire package and not one of the steps. I also added an Execute SQL task and a Data Flow task in my Control Flow that you can see to the right; they’re named so that their purpose should be fairly obvious See the image below:

Double click the execute SQL task “Get last LogID” in the Control Flow window so we can begin filling out the task. In the Execute SQL Task Editor window that pops up you’ll need to go to General and match the settings in the image below. As you can see my SQLStatement is a simple select that gets the most recent id from my MSSQL log table.

Once you’ve set General skip down to Result Set and assign the output from your SQL statement to the MaxLogID variable you created earlier. You’ll need to click Add and choose your MaxLogID in the Variable Name dropdown. The image below shows how my Result Set window looks.

Next we’ll set up our data flow task “Copy MySQL data to MSSQL database”. Double click it in the Control Flow window and add ADO NET Source from Data Flow Sources and OLD DB Destination from Data Flow Destinations. We’ll assume instructions for associating the MSSQL connection to the OLE DB Destination step aren’t needed. Setting up the ADO NET Source is trickier.

First we set the ADO.NET connection manager to point to our MySQL connection. Data access mode should be “SQL command”. Last, you’ll need to put your MySQL query into the SQL command text. I aliased the table as l and every column must be surrounded by grave accents (to the left of the 1 key on a QWERTY keyboard).  I ran into a DATETIME conversion issue where the date in MySQL was stored as UNIXTIME and I wanted to use DATETIME so I’ve shown that conversion in my query as well. Now, for your WHERE clause, you’ll need to choose a valid entry. I chose 0, which you’ll see is surrounded by standard single quotes. Note: this is not what will execute at runtime.  See below for my settings.

Finally the the most difficult task: building the query with the MaxLogID parameter. Return to the Control Flow tab and open your SQLCommand variable. Your value should be your MySQL query all the way up to but not including your variable. It must be contained in a single line with no carriage returns.

Next, single click the “Copy MySQL data to MSSQL database” task and look in your Properties window. Scroll to and click Expressions.

Click the ellipsis with three periods in it and a Property Expressions Editor should pop up. Choose [ADO NET Source].[SqlCommand] for Property. Now we will build our expression. Click the far right ellipsis to bring up the Expression Builder. Expand the Variables tree and drag-and-drop User::SQLCommand into the Expression window.

This is how we will append the rest of the query to what we set the SQLCommand variable to earlier. Next, add a single quote to the right of your expression (addition symbol, space, double quote, space, single quote,double quote, space, addition symbol).  Since we are building a string to execute we’ll need to cast our MaxLogID variable as a string. Expand Type Casts and choose (DT_STR, <<length>>, <<code_page>>). Drag and drop it to the Expression window then fill in an appropriate length you expect your variable to be. 1252 is standard for <<code_page>>.  Now add your MaxLogID variable by dragging and dropping it from the Variables tree. Last, append another single quote to finish out the query. Below is how my finished expression appears.

That’s it! One other issue I ran into was that my package was timing out when validating into the MySQL server, so I set DelayValidation to True.