SQL Insert via mappings table
Perhaps you could create an SSIS script process to dynamically build the insert commands and execute them.

--Demo setup
drop table if exists MappingTable;
create table MappingTable 
    ([StageTble] varchar(9), [StageColumn] varchar(9), [RealTable] varchar(8), [RealCol] varchar(6), [OrdinalPosition] int)
;

INSERT INTO MappingTable
    ([StageTble], [StageColumn], [RealTable], [RealCol], [OrdinalPosition])
VALUES
    ('tblStage1', 'Field_ABC', 'tblDest1', 'FldABC', 1),
    ('tblStage1', 'Field_ZXC', 'tblDest1', 'FldZXC', 2),
    ('tblStage1', 'Field_QWE', 'tblDest1', 'FldQWE', 3),
    ('tblStage2', 'Field_PLM', 'tblDest2', 'FldPLM', 1),
    ('tblStage2', 'Field_WER', 'tblDest2', 'FldWER', 2),
    ('tblStage2', 'Field_YTE', 'tblDest2', 'FldYTE', 3)
;




SSIS script task

--SSIS script task
--NOTE: the actual EXECUTE sp_executesql @cmd is commented out
Declare @Cmd nvarchar(max)
;with DistinctTables as
(
select distinct [RealTable], [StageTble] from MappingTable
),
InsertCommands as
(
select *,
'insert into ' + [RealTable] + '(' +
STUFF((
        SELECT ',' + [RealCol]
        FROM MappingTable t1
        where t1.RealTable = drt.RealTable
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ')' + 
' select ' + 
STUFF((
        SELECT ',' + [StageColumn]
        FROM MappingTable t1
        where t1.RealTable = drt.RealTable
        ORDER BY OrdinalPosition
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+ ' from ' + [StageTble]  as InsertCommand
 from DistinctTables drt 
)
select @cmd = 
STUFF((
        SELECT ';' + char(10) + [InsertCommand]
        FROM InsertCommands
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')
+';' from InsertCommands ic

print @cmd
--EXECUTE sp_executesql @cmd




Produces the following dynamic SQL

insert into tblDest1(FldABC,FldZXC,FldQWE) select Field_ABC,Field_ZXC,Field_QWE from tblStage1;
insert into tblDest2(FldPLM,FldWER,FldYTE) select Field_PLM,Field_WER,Field_YTE from tblStage2;