Use a Conditional Split and send the unwanted row(s) to an output which goes nowhere ...

A possible alternative is to delete and recreate the sheet you are working with, using Execute SQL tasks against an Excel Connection Manager connection ...

DROP TABLE 'sheetname'

followed by

CREATE TABLE 'sheetname'...

Aggregation can remove dups too. But it all works faster using the database engine, as already noted.

This is really not my area of expertise, but I think that you'll have no problems with the availability of regedit and that gacutil would be installed as part of...

Not really. Still seems like a versioning issue to me. Did you have to register that DLL on your local PC, or was it just 'there' as a result of...

What does the Script Task do?

But version 10, as referred to in your error message, is Excel 2002 (see here).

At what point is the error message appearing? Is it when you try to run the...

Version 10 is Excel 2002, I think. Is that what you have on your PC? You need to get these versions in order ...

So ......

Does the server have Excel installed?

Sabyasm told you already - what is wrong with that method?

Well, within reason, but if you're going round pretty much the same loop 20 times, I would be doing a fair amount of investigation to avoid having 20 dataflows.

A foreach...

From your screen shot it is difficult to distinguish between long lines (which have wrapped) and true line breaks. Can you widen it to make this clearer? Or just cut...

The result of a union can be thought of as a single table with x rows and y columns with various datatypes - you can't mix and match within that...

The size of the source field (in SSIS) needs to be increased to accommodate the maximum string length from Excel.

If you then want to truncate this before it gets loaded...