As part of a SSIS ETL process, I have multiple staging tables. I have used xxxx just to resemble data: e.g. tblStage1
Field_ABC Field_ZXC Field_QWE xxxx xxxxxxxxx xxxxx tblStage2 Field_PLM Field_WER Field_YTE xxxxx xxxxxx xxxxxxx
I then have the 'real' tables that they need to be inserted into:
tblDest1 FldABC FldZXC FldQWE tblDest2 FldPLM FldWER FldYTE
Using info from the Information Schema I have built a mapping table that details which table and field from the staging tables map to which real table: tblMapping:
StageTble StageColumn RealTable RealCol OrdinalPosition 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
How do I loop through each staging table and insert into the real tables\columns based on the values in tblMapping?
Would this be possible via a Cursor and a Loop?
Any help would be greatly appreciated.