Multiple Instance Job

[bi_fujitsu]

Hi

I have a scenario where i need to populate 50+dimension tables. The source is same for all the dim tables and the structure is also same for all the dimension tables. I have a static table with the following structure & values

Attribute_Name
QA_001
QA_002
QA_003
QA_004
QA_005

The naming convention for the dim tables is PL_DIM_#Attribute_Name#. where this variable will have values like QA_001,QA_002 etc

My select statement from the source would be

Select Column_1,Column_2,Column_3,Column_4 where Attribute_name = #Attribute_Name#.

I want to design a multiple instance job where my invocation_id would be the #Attribute_Name#.

Please help me how i can create this multiple instance job.

I want that every time this job gets executed it should pick up the next value of Attribute_name from the static table and this job should keep on getting invoked with a different value of attribute_name till it reads the last record from the static Table.

Please help me how i can achive this.

[kumar_s]

You can frame in the same job using a stored procedure.
Select the Attribute name from the static table and pass it as parameter to a stored proc. The stored procedure can have the table name concatinated with the given input parameter.
Or use loop activity, you can pass each value to the Multiple instance job as parameter.

[bi_fujitsu]

My another question is say suppose i have 5 records in the stati Table with values like
QA_001
QA_002
QA_003
QA_004
QA_005.

Is it possible to execute the multi instance jobs simultaneously with all the 5 above mentioned values.

If yes what should be the design of my sequence. I don't want to hardcode any values as the records in the static table might increase or decreased.

[ray.wurlod]

How about YOU have a go at designing the sequence, and we'll constructively criticise it?

Designing things is how some of us earn our living. Providing the same service for free on DSXchange is not something I (for one) am not prepared to do.

[JoshGeorge]

Select Attributes Job: Select the Attribute names from the static table to a seq file and the total count to another file
Main Seq:
Read the count using UVactivity (Write a routine to read seq file)
Start a loop (1 to count), read current loop num line from the seq file with attribute names and pass it as parameter to your Multi Instance Job (This job call should be a routine which will start an instance of the job with the parameter and runs independently)