Need logic

[venkataramana_paidi]

Hi ,

I have 35 source systems on MS SQL Server. Source system/database  connection details are different.  From each source system/database I have to extract the 35 tables .  Table structures are same from one database tables to another database tables.  I have to load these tables into single target target database. In my scenario I will have 35 target tables . In the target table I have extra field name is source . I need to give the some source database details in the extra field like  USA ,UK etc .

DB1  A                             T A

DB1  B                             T B

DB2  A                             T A

DB2  B                             T B    

DB3  A                             T A

DB3  B                             T B    

.................................................

..................................................

................................................

I want to schedule my jobs  to load the data.

1. I cannot use the data store configurations  as I am using scheduling and I cannot change manually data store configuration option or system configuration option .

Do I need to develop the jobs for the one source database and replicate the jobs and changing the source tables from another data store ?

How can we reduce the job development effort without replicating the job and changing the source tables for remaining 34 databases?

Please help me in this .

Thanks & Regards,

Ramana.

[former_member187605 · Accepted solution]

You need:

• one workflow that contains your job logic
• 35 system configurations, one for each source system, linked to your substitution parameter and datastore configurations
• 35 identical jobs (ontaining that one workflow only), one for each system configuration

You can then schedule your jobs without any problem in the DS Management Console.

[venkataramana_paidi · Accepted solution]

Dear Dirk,

If we use system configurations , we need to select the system configuration while executing the job .

If I use the schedule  the job, is there any option to choose the system configuration as one time task ?

Thanks & Regards,

Ramana.

[former_member187605 · Accepted solution]

No, not that I know. The workaround I have posted here is the only one I have ever used.

[venkataramana_paidi · Accepted solution]

Thanks Dirk,

I have one issue while using the system configurations .

I have created the two data store configurations with two different databases .

I have created two system configurations for two database configurations .

But while running the job it loading data from default data store configuration only for both system configurations  .

It is not loading data from other data store configuration .

What will be the issue?

Thanks & Regards,

Ramana.

[former_member187605 · Accepted solution]

My answer is in your other post.

[venkataramana_paidi · Accepted solution]

Hi Dirk ,

Thanks for your support.

After mapping the Data store configurations to system configuration I have changed the data store configuration name . That is the issue . It is taking the default configuration as name is not available. I have deleted the system configuration and created again with modified data store configuration name.

Now It is working fine .

Thanks & Regards,

Ramana.

[former_member200473 · Accepted solution]

Hi Venkat sir,

How about following below mentioned idea :

1-keeping all the configuration data(data store name, database name, file path etc) into table with unique id.

2-using a loop ,fetch the configuration and pass them into Variable/Substitute parameter.

Please provide your inputs into this idea.

[venkataramana_paidi · Accepted solution]

Hi Shiva Sahu,

Your idea is good but it affects the performance. If I want to load some selected countries data  , loop is difficult.

Anyway data store configurations/system configurations/substitution parameter with scheduling the job will work perfectly and need less development efforts.  Everything under user control also.

Thanks & Regards,

Ramana.

[Former Member]

I think with the help of database links you can do your task easily with single datastore.

you can link the datastore via database link.