HI,
This is kind of an open-ended question or a discussion, and am sure the item discussed would be of use to others.
My predicament is that i have multiple source sybase datbases that i extract data from to insert into a single destination sql server database. The source data consists of hierarchy information and staging data that will go into fact tables eventually.
I differentiate between source instances by setting a field for the source machine e.g. "inst1" "inst2" or "inst3".
At the moment i have 4 packages that do the following:
1) Extract data from source to stage tables.
2) Extract hierarcy data from source to stage tables.
3) Process the hierarchy
4) insert fact data and build dimensions for all instances in olap task.
Packages 1 to 3 use globals to determine whcih instance to work on.
The problem is that different customers will have different numbers of source instances. some may have only 1 instance (easy) or 15 + (hard).
so when i come to schedule these up i either have to create many separate sql jobs for each instance or come up with a generic way to cope with N instances. i would prefer the latter as it saves customer specific config.
The first solution would involve lots of separate sql jobs per instance per step - e.g. 4 instances * 3 steps = 12 jobs, + 1 job for the final step (4). All previous steps must be completed before the final cube build in step 4.
Therefore, are there any nice ways of somehow setting up the instances that exist beforehand (into a table or file or whatever), and then looping through each instance per step ? The rule being you cannot move to the next step until the previous one has ended.
Not sure i explaine that one very well!
MrPeds
This is kind of an open-ended question or a discussion, and am sure the item discussed would be of use to others.
My predicament is that i have multiple source sybase datbases that i extract data from to insert into a single destination sql server database. The source data consists of hierarchy information and staging data that will go into fact tables eventually.
I differentiate between source instances by setting a field for the source machine e.g. "inst1" "inst2" or "inst3".
At the moment i have 4 packages that do the following:
1) Extract data from source to stage tables.
2) Extract hierarcy data from source to stage tables.
3) Process the hierarchy
4) insert fact data and build dimensions for all instances in olap task.
Packages 1 to 3 use globals to determine whcih instance to work on.
The problem is that different customers will have different numbers of source instances. some may have only 1 instance (easy) or 15 + (hard).
so when i come to schedule these up i either have to create many separate sql jobs for each instance or come up with a generic way to cope with N instances. i would prefer the latter as it saves customer specific config.
The first solution would involve lots of separate sql jobs per instance per step - e.g. 4 instances * 3 steps = 12 jobs, + 1 job for the final step (4). All previous steps must be completed before the final cube build in step 4.
Therefore, are there any nice ways of somehow setting up the instances that exist beforehand (into a table or file or whatever), and then looping through each instance per step ? The rule being you cannot move to the next step until the previous one has ended.
Not sure i explaine that one very well!
MrPeds