While there's always an exception, I've not yet seen where separation to multiple tables actually solves anything such as what the OP has portrayed except to justify the need...

This might be a case where it depends what you want to do with the data and no obvious clear answer at the outset.  An example to compare it to...

We have found a replicated version necessary for...

Even then, you have two different paradigms that people need to be good at not to mention moving a shedload of data on a regular basis or smaller but...

And I'm still having a really hard time understanding why someone would put a DW and the source data on two different RDBMSs and then also have the requirement...

If...

Primary function of these audit columns is to help load data from oltp to data warehouse using insert/update audit columns using ssis. Thought about cdc, but they find this...

As for avoiding triggers, I think that too many people make the recommendation to avoid them because too many people have written poorly performing queries and the old wive's...

I'm uncertain whether you need to build a data warehouse or a database.  Based on what I'm seeing, you may want to assume that you're building a database that will...

He already stated that this was the approach that worked for him.

Just for curiosity sake, are you able and allowed you tell what the tables are.  In all my design years, I can't recall something like this.  I would just like...

I find the same.  I tell my bosses I don't single task well.  I need to have multiple things to work on.  When I only have one thing to work...

Completely disagree.  SSIS inserts large row counts this very well.  I can...

I would suggest that you alter the data type for your landing tables to be nvarchar.  This should resolve the issue.  You can change it after that as you stage...