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

In previous post I presented a way for UPSERT in MySQL with SSIS 2008, in this post I present another solution which is compatible with

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.

This is a solution for Upsert ( Update/Insert) with MySQL table:
(This Solution will work under SSIS 2005)


Assume Structure of SQL Server table is :

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

Add a Foreach Loop Container, and connect precedence constraint from "lookup" data flow to this container.
In Foreach Loop Editor, Set Enumerator as ADO Enumerator.
And set Ado object source variable with User::InsertedRows.


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.

Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for 12 continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand, Power BI Summit, and Data Insight Summit.
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.

Leave a Reply