Update Table With Data Calculated From The Same Table

Hello,

I'm attempting to explore the functionality of SSIS and all the available data flow transformations, and I'm trying to learn how to use the tool correctly.
How would someone update a table with data which is calculated from that same table.
This is a made up example. Lets say I have a table:


CREATE TABLE [dbo].[tst_source](

[tst_key] [int] IDENTITY(1,1) NOT NULL,

[object] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[color] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[color_count] [int] NULL

)


I would like to populate the field color_count with the count of items listed with that color.

Data:
tst_key object color color_count
1, chair blue NULL
2, table black NULL
3 lamp black NULL
----
So the last column for 1st rec value would be 1, and for 2nd and 3rd, would be two.
tst_key object color color_count
1, chair blue 1
2, table black 2
3 lamp black 2

-- With T-SQL I would use the following code:

update tst_source

set color_count = res.cnt

from tst_source

Inner join (select count(tst_key) cnt,color

from tst_source

group by color) res

ON res.color = tst_source.color

But I would like to use the Data Transformations. Can someone explain in great detail which Data Transformation tasks I would need to use?

Thank you.