Currently, we have a lot of ETLs developed and the underlying table and column names are going to change. For example, the physical names used abbreviated names rather than full names since we wanted to deploy on Oracle, but for now, we decided we will use only SQL Server, hence there is a discussion on using full names at the DB level. This will impact the ETLs that we have already developed.
I was just wondering whether there is any efficient way to remap the ETL to the changed column names? I can provide a file with old and new table/column names as input.
Any help on this will be really appreciated.
One possible way is to use a SQL Override value in all of your Source Qualifier transformations that contains a SQL query with parameter file substitutions. This way you can change the column names and SELECT them in mapping sources without modifying the Source definitions. Of course this would require some up-front refactoring of your existing Source Qualifier transformations.
Alternatively, at the database level you could create views onto the existing tables that use the old abbreviated column names, effectively providing a translation between the new column name and the old one. This would not require any changes to your existing ETL.
However, I would generally advise against changing anything in this case, especially since the difference is purely cosmetic - it will only produce maintenance and complexity headaches.