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

There are lots of links which described how to UPSERT( Update / Insert ) between two SQL Server tables. which mostly used Lookup

This is a solution for Upsert ( Update/Insert) with MySQL table:
(Note that in this solution Cache connection manager used which is only available in SSIS 2008)

Assume Structure of SQL Server table is :

Data in SQL Server Table:

Data in MySQL Table:

Add a data flow task to control flow, name it as "Fill Cache Component"
Add an ADO.NET Source which pointing to the MySQL Table.
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

Then add a Cache Transform and connect data path ( green arrow ) from ado.net source to this cache transform.

Double click on connection manager,
Click on New to create new cache connection.
In cache connection manager editor, check the "Use file cache" checkbox.
And browse a path to save cache file there,

In the columns tab, set PK of mysql table as index ( set index position as 1 ),then OK.

Map columns.

Go back to control flow.
Create new Variable of OBJECT data type in package scope and name it as UpdatedRows.
Add another data flow task, name this one as "Lookup".
Connect precedence constraint ( green arrow ) from first data flow task to second data flow task.
Then double click on second data flow task.
Add an OLEDB Source ( setup a new oledb connection to sql server database ) and point it to SQL Server table.

Then add a Lookup transform, connect green arrow from oledb source to lookup.
Then double click on lookup, and in lookup transformation editor,
Set Connection Type as "Cache Connection manager".
And set "specify how to handle rows with no match entries" with "redirect no match rows to no match output".

Then in connection tab, select cache connection manager from the drop down list.

And in columns tab, map joining fields

Add an ado.net Destination pointing to the MySQL database, and map columns.

This will do INSERT part of UPSERT.
Add a RecordSet Destination, and double click on it, in Component properties tab, set VariableName with User::UpdatedRows.

In the second step you INSERT new rows in MySQL table, and fills UpdatedRows to an object datatype variable.
This is whole schema of this second Data flow task:

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::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,
Write update command in SQLStatement property,as below:

Update MySQLTable
Set FirstName=@FirstName
,LastName=@LastName
where ID=@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:

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.