Mutiple SQLDb TO SQLDb data transfer with a Generic Core code.

Urg.. What is that ...

Well I was not too sure how to title this question, and even after telling the client it was difficult if not impossible, he insists it's what he wants.. so here goes the question..

The client has several SQL systems running on site that collect data from different processes (Between 1 and 10 minute intervals depending on the process been checked). Note: these systems sit on different parts of the site..

Now the problem is.. They have a Old C# Service that collects New data from each system, and dumps it on two different SQL servers.. (One local, and one at Corporate Head office)..

There are a few problems along the way too... The Sql tables are not always exactly the same, Some may have extra fields, and others have differently named columns. IE.

Local SQL1's dataset is Col A,B,C,D,E.. and corporate will only have Col A,B,C,D ...
OR
Local SQL1's dataset is Col A1,B1,C1,D1,E1.. and corporate will have Col A1,B2,C2,D1,E1 .. Where the Data in B1 = B2 and C1 = C2 ..

I hope this is clear...

Okay lets go on...

Now there are plans to introduce further data collecting systems in the future, and the client does not want to have to rewrite the Service to include all the details of the New dataset's. He's looking for a system where he can simply add the new SQL Connection string, with a list of table names and possible column name changes or exclusions...

So.. I have to try and work out a New VB.net Service that will accept Loads of data, Possibly from Several Normalized SQL Tables, and use it in very Generic SQL Table moving code.. IE One or More Functions that will Copy New data from any SQL server to Any other SQL Server..

I hope it's clear..

Thanks...

Gremmy...