Need logic

system · March 26, 2015, 2:00pm

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.

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.

Ramana (BOB member since 2009-04-30)

system · March 26, 2015, 2:50pm

Use a while loop to capture the table names in a variable. Pass the variable names to a parameter and use SQL transform to call the parameter and load one by one to the target table.

Arun.K (BOB member since 2011-10-18)

system · March 26, 2015, 5:58pm

You need to use System Configurations. This is exactly what it was designed for.

eganjp (BOB member since 2007-09-12)

system · April 9, 2015, 6:18am

I am not quite sure if the SQL tranform logic would work as the Datastores need to be changed.

One solution that might work is :

Create jobs/Dataflows from Files to Target database. Files would have the same structure as hose 35 tables.

Run time , in DS script , use SQL bulk export to export table data to a file.In DS script you can change the connection value using variable.

now same job/dataflow can be used for multiple source systems with connection name.

Worth a try.

basy2005 (BOB member since 2013-02-13)