-1

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.

Steam
  • 9,368
  • 27
  • 83
  • 122
  • Too broad - are you asking about specific ETL tools? – D Stanley Jan 14 '14 at 19:27
  • @DStanley - Its a general principle. But, to narrow the scope I will say SSIS. – Steam Jan 14 '14 at 19:29
  • 1
    I don't know if it's possible, but the Transformation process is usually customized for each data stream or extraction from tables. I think that the idea of 'unknown number of tables' has not sense because you must know them for cleaning and transform the data then. – carexcer Jan 14 '14 at 20:12
  • 1
    @carexcer - I have to make the transform logic also dynamic. I am thinking of storing that logic in a table. Don't know if this is a good way. – Steam Jan 14 '14 at 20:22
  • "General principal"? Are you reading from a text book or something? – John Saunders Jan 15 '14 at 01:54

1 Answers1

1

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.

Nick.Mc
  • 18,304
  • 6
  • 61
  • 91