For a BI project, I'm trying to pull data from several independent source databases and merge the data into one single target database. The source databases are structurally identical, only their content is different.
Currently, I use ONE transformation which performs all steps in one shot (I attached a pdf a screenshot on page 1 "Current Situation"). I set up the transformation for one database (accessing multiple tables with their corresponding connection settings). Then I made a copy of the operators, and changed the connection settings so they refer to another database (i. e. the settings for the "Table input" operator).
This is already a bit cumbersome, but the worst part is this: The project is "work in progress", and whenever I need to make a change (i. e. add a calculation, or pull another field), I need to make that change for each database from where I'm pulling, which is very hard to maintain.
To solve that, I'm trying to achieve this: (see the drawing (not working) on page 2 in the pdf, "Goal") I'd like to use one and the same transformation (not copies) each time I need to access a different database, and only change the paramaters (variables?) for the connection settings, then append the different streams, and write it into the target database.
This way I would only need to maintain one version of the transformation, and any change would be reflected in all instances of that transformation.
Would somebody be able to put me on the right track?
Thanks a lot in advance!