Really looks nice and makes sense. Thanks!

Will be testing it today.  Does it make difference if I do  'Select 1 instead of Select * in each EXISTS(......  subquery?

or Select Top...

Should I update statistics first on all tables participating in the joins? including the #temp table?

All indexes are tuned to their best (for the current statement) so I am specifically looking for rewriting this IF statement.

May be 2 or 3 IFs are better? like populating...

Yes, I have just installed the 2nd instance, it is possible, thanks.

Thanks!  So if there is more than 1 record matching in SOURCE then MERGE picks a random one to insert?

Do you use ROW_NUMBER inside of CTE in the Source?

MSSQL 2014

Grant, thank you so much for the overview! I really appreciate it.

Question: establishing 2nd (cloud) location.  Which paid service of this kind would you recommend?

or, if not cloud, then what...

when i press ADD button to add error output it says cannot create it.
Is there a way to let package continue writing to sql table , SKIPPING (or ouputting...

I ended up doing it this way, because same scrtipt needs to be run on both 2016 and 2000 SQL Servers:

--create first table
CREATE TABLE...

i did what you said, and 1252 to 65001 everywhere, and ALwaysUseDefaultCodePage=true in destination.
But i really don't know why it worked :).  i thought i tried the same  before.

shouold i try to re-create the CSV file or text file make it text file and copy and paste or recreate the data  in new csv file and try....

I changed all types via COnversion task from DT_STR to WDT_STR unicode.  as on the attached pic.
then i got oledb destination error:  cannot convert between non-unicode and unicode data..