Update / Insert ( UPSERT ) MySQL Table From SQL Server Table – SSIS 2005

SSIS 2005 for UPSERT in MySQL Table.
As previous post shows, the main problem is:

There are lots of links which described how to UPSERT( Update / Insert ) between two SQL Server tables. which mostly used Lookup  Transform with OLEDB Command.
But when you work with MySQL there is a limitation, you can not use OLEDB Connections, you can use ODBC or ADO.NET Connectors from  .NET to MySQL Database, as described briefly here:
http://blogs.msdn.com/b/mattm/archive/2008/03/03/connecting-to-mysql-from-ssis.aspx

So, How you can UPSERT with MySQL Tables? You will face problem with Lookup transform because you have no OLEDB Connection to mysql,

and you can not use OLEDB Command on the other side.

CREATE TABLE [dbo].[SourceSqlServerTable](
    [ID] [int] NOT NULL,
    [FirstName] [varchar](255) NULL,
    [LastName] [varchar](255) NULL)

and Structure of MySQL table is:

CREATE TABLE mysqltable (
  ID int(11) DEFAULT NULL,
  FirstName varchar(255) DEFAULT NULL,
  LastName varchar(255) DEFAULT NULL
)

Data in SQL Server Table:

Data in MySQL Table:

Solution:

There are three steps as follows,
First Step:

Add a data flow task to control flow, name it as "Lookup"
Add an DataReader Source which pointing to the MySQL Table, name this source as "MySQL"
For finding how to create connection from SSIS to MySQL look at this article:
http://blogs.msdn.com/b/mattm/archive/2008/03/03/connecting-to-mysql-from-ssis.aspx

You should Sort the output, so go to Input and output Properties tab,
select the DataReader Output and set IsSorted property to true

then you should set what is sorting fields order, so expand the datareader output and then under output columns, select ID,
then set SortKeyPosition property to 1.

Then add an OLEDB Source which pointing to the SQL Server Table, name this source as "SQL Server".

Sort output of this source like previous one,

Add a Merge Join Transformation,
Connect SQL Server source to it as Left input, and connect MySQL source as right input.
the double click on merge join transform, and in merge join transformation editor, select Join type as "Left Outer JOIN",
and select all fields from SQLServer Source, and ID from MySQL Source ( name this one as MySQLID ).

Then add a Conditional Split Trasnformation, and connect data path from merge join transform to this transform.
write this expression :
ISNULL(MySQLID)
and name the output as "No Match Output"
then change default output name to : "Match Output"

Add a RecordSet Destination, name it as "Inserted Rows"
connect No Match Output from conditional split to this destination. set User::InsertedRows as variableName, and map columns

Add another RecordSet Destination , name this one as "Updated Rows"
connect Match Output from conditional Split to this destination. set User::UpdatedRows as variableName, and map columns.

whole schema of this Data flow task:

Second Step:

Then Go back to Control flow.

Create these Variables:
Name        Scope        DataType      Value
———————————————-
ID          Package      Int32         0
FirstName   Package      String
LastName    Package      String

Then in Variable mappings tab, do these mappings:

Then add an execute sql task inside foreach loop container,
And set connection to MySQL database there, I used ODBC Connection in this sample.
Write update command in SQLStatement property,as below:
Insert into MySQLTable( id,firstname,lastname) values (?,?,?)

Then in Parameter mappings tab, do these mappings:

for difference between parameter markders and parameter names in different connections in execute sql task look here:
http://technet.microsoft.com/en-us/library/ms140355.aspx

Add another Foreach Loop Container, and connect precedence constraint from previous foreach loop to this container.
In Foreach Loop Editor, Set Enumerator as ADO Enumerator.
And set Ado object source variable with User::UpdatedRows.

Then in Variable mappings tab, do these mappings:

Then add an execute sql task inside foreach loop container,
And set connection to MySQL database there, I used ODBC Connection in this sample.
Write update command in SQLStatement property,as below:
Update MySQLTable
Set FirstName=?
,LastName=?
where ID=?

Then in Parameter mappings tab, do these mappings:

That’s all, this is the whole schema of package:

Now run the package, and result in MySQL table after Upsert will be:

The End.