CODEWITHSUNDEEP
sql-servert-sqlssis
kepak221
kepak221

Reputation: 9

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

I am fairly new to SSIS and I am doing small data migration. I have column in my destination database col1 which has Unique Key constraint and the data is in "xxxx01" -form.

What I need to do is to shorten the source data from col2 to four first characters (xxxx) and add running number to the end if the first part is the same with other row. So for example destination data would look like fest01, fest02, fest03, test01,kest01.

In my source table there might be little under 80 rows that has the same value in source.col2 so running number has to go at least high as that.

Upvotes: 1

Views: 39

Answers (1)

Igor Borisenko
Igor Borisenko

Reputation: 3866

Add into your package OLEDB Source with SQL command like this:

/* sample data */    
declare @t table (v varchar(50))
insert @t values('sadasvasv'),('sdfvadvssdv')
/* the SQL command */    
SELECT LEFT(v,4)+CAST(ROW_NUMBER() OVER(PARTITION BY LEFT(v,4) ORDER BY v) as varchar(50)) AS col1
FROM @t

Upvotes: 1

Related Questions