Hi Michael, thanks for the quick response!
As you suggested, I have added a second Execute SQL Task configured in exactly the same way as the first one described previously except, also as requested:
IsolationLevel = ReadCommited
Transaction Option = Required
The package ran OK (both components start, turn yellow and run at the same time, go green, no errors). Looks like MSDTC is set up ok.
I change the settings on both components back to:
IsolationLevel = Snapshot
Transaction Option = Required
And it fails.
I tried both these article but the problem persists: