If that's a Foxpro DBF, you might need to install a driver (free, I think) before this will work.

Are you using a Foreach/File container?

Easy for me to...

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