connecting to multiple sql server databases from java

Concurrency to the rescue.

To achieve the best throughput for your heavy workload, write your application as multithreaded from the start, then you can speed it up or throttle it back, depending on performance constraints.

ExecutorService is a nice way to break down tasks in a scalable way. I would suggest you define each database-import task as a Callable, and then 'invoke' all the tasks from an ExecutorService.

I'd do something like this:

List<YourCallableImportJobs> work= yourFactory.getAllWork();
// this variable can be used to tweak performance. 
// Begin with a low number and then ramp it up if it's too slow.
int nThreads=10;     
ExecutorService service = ExecutorService.newFixedThreadPool(nThreads);
List<Future<T>> futures= service.invokeAll(work);

You can poll the Futures to check when the work is done...

Finally, if you wanted concurrent access to each database (particularly for your destination database), I recommend using a connection pooling mechanism such as C3PO. This means that you don't spend too much time opening and closing connections. (You could even break down each import into individual queries - this is when connection pooling would help as well).

Hope this helps

Maintain a queue of database connections, with ipaddresses of those databases, use multithreading to connect to each of the database, now as the work from a database finish, close the connection from that database and remove the connection from queue.