How to Update the Records in a Table using OLE DB Command Transformation in SSIS Package

SSIS Package to Update the Records in a Destination Table by Mapping with Source Table using OLE DB Command Transformation
Scenario :
Suppose we have the Source Table data which is in Sales Database is as follows..
Select * From [SalesDB].[dbo].[Tbl_Customers]

Now I want to Update the Customer details like Cust_Name and Cust_Segment from the above table into below Destination Table, which lies in the MyAnalytics Database as follows..

Select * From [MyAnalyticsDB].[dbo].[Tbl_Customers]

Now lets design the Package to accomplish the above Scenario

-----------------------------------------------------------------------------------------------------

Phase-1 : Create the  required OLDB Connections 

-----------------------------------------------------------------------------------------------------

Create the Connections to the Source ( SalesDB ) and the Destination (MyAnalyticsDB)

-----------------------------------------------------------------------------------------------------

Phase-2 : Add the Data Flow Task to Control Flow and Design the Flow
-----------------------------------------------------------------------------------------------------

A) Add the Data Flow Task

B) Add the OLE DB Source Transformation in Data Flow and Set Properties

Here I have connected to the Source Database "SalesDB" where my Source Table having Customer Details .

Next Review and Select the Columns from the OLE DB Source Editor > Columns Tab

Next say OK.

-----------------------------------------------------------------------------------------------------

Phase-3 : Add the OLE DB Command then Set the Properties
-----------------------------------------------------------------------------------------------------

A) Add the OLE DB Command Transformation and Connect to OLEDB Source Transformation

B) Next set Connection to the OLE DB Command Transformation from Advance Editor>Connection Managers

Here I have connected to the MyAnalyticsDB where I have destination Customers Table in which I want to update the Customers Details.

C) Next in the OLE DB Command Transformation>Advance Editor>Component Properties , update the SQL Command

Here I have used the below Update Statement with Parameters (? ).

Update [MyAnalyticsDB].[dbo].[Tbl_Customers]
Set Cust_Name=?, Cust_Segment=?
Where Cust_Id=?

The Value for each Parameter will be passed by Source Table, by Mapping it as in the Next step.

D) Next in the OLE DB Command Transformation>Advance Editor>Column Mapping, map the Source Columns with Destination Parameters.

Map the Correct Column with Corresponding Parameter.

Ignore the Input Output Properties Tab, nothing will update in this Tab.

Next say OK. Now the Package is Ready.

E) Finally Run the Package.

------------------------------------------------------------------------------------------------------

Output :

Now the destination Customers Table in the MyAnalyticsDB will updated with required Customer Details from source Customers Table of SalesDB

Select * From [MyAnalyticsDB].[dbo].[Tbl_Customers]

Important Notes :

OLE DB Transformation will performs the any Update/Delete operation on Row by Row basis.

For Large Datasets , this Transformation takes long time to Process. So in that case , its is advisable to load the Data to a Staging Table in the Data flow, next Connect this Data Flow to the Execute SQL Task in the Control Flow then perform the your Operations using SQL statements.

------------------------------------------------------------------------------------------------------

Example -II :

------------------------------------------------------------------------------------------------------

Executing a SP with Parameters in OLE DB Transformation :

Suppose we have an SP with 3 Parameters as follows...

SP Definition :

Create Procedure spUpdate_CustomerDtls
 (
  @CustId Integer,
  @CustName Varchar(50),
  @CustSeg Varchar(50)
 )
AS
BEGIN
 Update [Tbl_Customers]
 Set  [Cust_Name]=@CustName , [Cust_Segment]=@CustSeg
 WHERE  [Cust_Id]=@CustId
END
GO

We can execute this SP using the above OLE DB Command Transformation , by Passing the SP with 3 Parameters In the SqlCommand as follows..

Exec spUpdate_CustomerDtls ?,?,?

Next we need to Map the Source Columns to these @Parameters as explained in the above Example. Also I shown here for your understand..

The Remaining Steps we need to follow as explained in the above first Example-I.

-------------------------------------------------------------------------------------------------------- 

Thanks, TAMATAM ; Business Intelligence & Analytics Professional 

--------------------------------------------------------------------------------------------------------