OLEDB Command transformation in SSIS

The OLE DB Command transformation is used to run an SQL statement for each row of its input data flow to insert, update or delete records in a database table.

Let’s see the demo

In this demo we will that how we will insert and update a records from the table using the OLEDB Transformation

I have a source where we are getting the product information. Below are the business rule we need to implement.

Rule 1: if we get new product we need to insert.

Rule 2: if we get the existing product we need to update the record.

Below is the destination table

My source data is below

  

Now I am taking the look up transformation. Creating the connection manager and doing the mapping. Setting the no match record setting as Redirect row to no match record.

Taking two Oledb command transformation.

Case of record Match

We need to update the record.

Creating the connection

Now we need to writer the sql command to update the record.

After that we need to map this parameter.

Similarly we need to configure in case of not matched record to insert a new record in the table.

Sql command

And parameter mapping

  

Now our package is ready to run

  

Before running this package records in the table.

Now running this package

See the records in the table.

Get the expected result.