Seems to work with the supplied data, but does not work with all occurrences of multiple commas, eg

insert into #table select '01008,,,,,,,,,,,,,,03072,0078,R,,'

I like Ramesh's solution. Your first solution replaces all commas ... this is not the requirement.

If your source data does not contain dups, but your target data does, you should be asking why, in my opinion - and that was my assumption on reading your...

Your DBA should be able to help you find out what process has locked the table ... then you just need to decide what to do about it. Killing the...

Not sure whether this would be faster ...

Maybe Zip all the files and then move the Zip file. Depending on the nature of the file names and the renaming, you...

I'll help you with the hard bit. Assume you have a table 'Colour' as follows:

ID ParentID ...

Your question seems to boil down to "why is SSIS creating duplicates in my data transfer?"

How can you expect anyone to answer this when you provide so little detail about...

Is it possible to come at this problem from another angle? Just have a blank version of the spreadsheet living somewhere accessible, then do a delete (of old spreadsheet) and...

Have you created the package variables you need? They need to be created/declared before they can be used.

Right-click the control flow and select Variables and follow it through from there....

... and I thought I had tested that! Well, it was early in the morning :doze:

Nice approach Ramesh - great use of logic.

Phil

Haha - I know what you mean. I wasn't sure until I tried it either 🙂

declare @a varchar(50), @b-2 varchar(50)

Set @a = '01008,03072,0045,R,,,,,,,,,-17'

Set @b-2 = replace(@a, ',,', '')

select @a, @b-2

Great, thanks for the feedback & agree with your comments - you learn more by trying, even if it doesn't work out ... 🙂

I'm afraid I still do not understand the requirement. Can you post sample data showing

a) the current data, and

b) how you would like the data to look after running the...

You just need all occurrences of the field to be 1? Can't believe it's that simple, but here goes:

update security_instrument_financing_statement_activity_reporting

set sequence_number = 1

If I've misunderstood something, please clarify ...