SSIS - Return Row Count From Execute SQL Task [ Updated and Inserted Row Count To SSIS Variables ]

Scenario: 

We have source table (dbo.ClientSource) and destination table (dbo.Client) . We want to insert the new records from Source to Destination and update already existing records by using Execute SQL Task. We want to return number of rows inserted and updated from Execute SQL Task to SSIS variables so we can use them to send email with Insert/Update stats OR insert these stats in some table for Audit purpose.

Solution:

Step 1: 

Lets write our TSQL query to insert new records and update existing records and also save the row count in variables

--Declare the variables to hold the Row Count

DECLARE @UpdateRowCnt INT

DECLARE @InsertRowCnt INT

--Inserting records from Source to Destination which does not exists

insert into dbo.Client(ClientName,Country,Town)

Select clientName, Country, Town from dbo.ClientSource S 

WHERE NOT EXISTS ( Select 1 from dbo.Client CL WHERE CL.ClientName=S.ClientName)

SELECT @InsertRowCnt=@@ROWCOUNT

--Update Already existing records from Source

Update CL

set CL.ClientType=S.CLientType

from dbo.Client CL 

INNER JOIN dbo.ClientSource S 

ON Cl.ClientName=S.ClientName

SELECT @UpdateRowCnt=@@ROWCOUNT

--Return the Variable values 

Select @UpdateRowCnt ,@InsertRowCnt

Step 2:

Drag Execute SQL task to Control Flow Pane and configure as shown below

Step 3: 
Lets map the values returned by query to SSIS variables

Final Output : 
Just to display the values of SSIS variable I used Script Task. Provided the variables to Script task and wrote below script to display.

Provide the variables as shown below and then hit Edit Script

Write this code in Main
            MessageBox.Show("Records Inserted ::"+Dts.Variables["User::VarInsertCnt"].Value.ToString() +"     Records Updated::"+Dts.Variables["User::VarUpdateCnt"].Value.ToString());

Lets execute our SSIS Package and see if we are getting expected results. According to my source and destination rows the counts are correct.