Good progress.

I would however like to see both explain plans for my code (changing the union all to union) and Jonathan's with indexes without included columns.
for the...

even rewriting does not solve everything.

CRM 2016 - DB on compat 130
On my main CRM db in one of our tables (30 Milliion rows +) the following...

Has the OP stated that the correct where clause is
WHERE ( 
BAN = @LOC_BAN 
OR BulkID = @LOC_BulkId 
OR Serial_Number= @LOC_IMEI 
OR ([Offer_Generate_Date] >= @LOC_STARTOFFER AND [Offer_Generate_Date]...

you didn't supply us with the indexes on the table - as it seems you generated the script through SSMS I would advise you to set your scripting options to...

That is normal - when on visual studio all models created on a server have that name format.

should you deploy the model to the server then it will...

Bit curious - there may be underlying problems not mentioned.

re: it is not instant file initialization enabled - very few cases where this should not be enabled -...

just to make it clearer
Publisher
------> Distributor
---------------> subscriber 1
---------------> subscriber 2
---------------> ....
---------------> subscriber N

the above can be any server - they could...

why not just convert the date to a string on the source select - converting to a format that SSIS knows well will then make the conversion to date straightforward.