sql server - Changing column from source to column with unique key constraint - SSIS -

i new ssis , doing small data migration. have column in destination database col1 has unique key constraint , data in "xxxx01" -form.

what need shorten source data col2 4 first characters (xxxx) , add running number end if first part same other row. example destination data fest01, fest02, fest03, test01,kest01.

in source table there might little under 80 rows has same value in source.col2 running number has go @ least high that.

add package oledb source sql command this:

/* sample data */     declare @t table (v varchar(50)) insert @t values('sadasvasv'),('sdfvadvssdv') /* sql command */     select left(v,4)+cast(row_number() over(partition left(v,4) order v) varchar(50)) col1 @t