SSIS is hanging during Update with 3 millions of rows

Question

I'm implementing a new method for a warehouse. The new method consist on perform incremental loading between source and destination tables (Insert,Update or Delete).

All the table are working really well, except for 1 table which the Source has more than 3 millions of rows, as you will see in the image below it just start running but never finish. Probable I'm not doing the update in the correct way or there is another way to do it.

Here are some pictures of my SSIS package:

Highlighted object is where it hangs. This is the stored procedure I call to update the table:

ALTER PROCEDURE [dbo].[UpdateDim_A] 
      @ID INT,
      @FileDataID INT
     ,@CategoryID SMALLINT
     ,@FirstName VARCHAR(50)
     ,@LastName VARCHAR(50)
     ,@Company VARCHAR(100)
     ,@Email VARCHAR(250) AS BEGIN
SET NOCOUNT ON;


BEGIN TRAN 
 UPDATE DIM_A 
    SET                  
        [FileDataID] = @FileDataID,
        [CategoryID] = @CategoryID,
        [FirstName]  = @FirstName,
        [LastName]   = @LastName,
        [Company]    = @Company,
        [Email]      = @Email

    WHERE PartyID=@ID

    COMMIT TRAN;  END

Note: I already tried Dropping the constraint and indexes and changing the recovery mode of the database to simple.

Any help will be appreciate.

---

After Apply the solution provided by @Prabhat G, this is how my package looks like, running in 39 seconds (avg)!!!

Inside Dim_A DataFlow

Answer 1

Follow these 2 performance enhancers and you'll avoid your bottleneck.

1. Remove sort transformation. In your source, while fetching the data use order by sql. Reason being, sort takes up all the records in memory before sorting. You don't want that, be it incremental or full load.

2. In the last step of update, introduce another Staging Table instead of update records oledb command, which will be replica of Dim table. Once all the matching records are inserted in this new staging table, exit the Data flow task and create EXECUTE SQL TASK which will simply UPDATE Dim table based on joining ID/conditions.

Reason for this is, oledb command hits row by row. Always prefer update using Execute SQL Task as its a batch process.

---

Edit: As per comments, to update only changed rows in Execute SQL Task, add the conditions in where clause:

eg:

UPDATE x
SET
   x.attribute_A = y.attribute_A
  ,x.attribute_B = y.attribute_B
FROM
DimA x
 inner join stg_DimA y
ON x.Id = y.Id
WHERE
(x.Attribute_A <> y.Attribute_A
OR x.Attribute_B <> y.Attribute_B)