AFAIK, ETL integrates data from a fixed number of sources/tables. Is it possible that ETL can integrate data from an unknown number of tables which might be added in the future, given that these tables might be of different types (customer, shipping, employee, accounting etc.) ?
EDIT -
Let A,B,C be tables. We can have the following ETL scenarios - A + B + C = 1 row or A + B + C = 3 rows
I have the first one. Later, we can add tables D,E,F,G...etc all of which might be different from each other in terms of the data they contain.
There's no way around defining the transformations.
There is no efficiency in pre-defining transformations in some custom database vs pre defining transformations in SSIS anyway. The advantage is that the SSIS platform has already been built!
You need to define the transformations at some point and you cannot evaluate what these will be until you can see the source, the destination, and the business rules.
You might be getting confused with replication. In this case there are no transformations. You are just replicating an identical table via ETL. For example if tables D,E,F,G simple need to be replicated identically with no transformations then with most replication platforms its relatively simple to just add the table without needing to do any transformations.
BIML is a way of automatically generating SSIS packages. Perhaps you should do some research on this.
