ETL and Data Integration tools - Modern ETL software for everyone

Hi

I need to do the following

We have more than 100 locations running ms-sql server and a main office with a ms-sql server

Each evening two specific tables are populated with data from the day’s business (one is header one is detail)

I need to attach to all locations and retrieve the data then insert all records in a consolidated table – exactly the same format as the store tables.

This needs to run unattended (or should be able to)

Once the data is inserted into the consolidated table the records at the store can be deleted or the table could be truncated (faster as there will be 10,000’s of detail records per day)

I have to believe this should be a simple project but I cannot seem to get it

Regards,
John

John.

Yes it is possible to achieve your goal

Depending on the situation there several ways to do it.

1. If you are able to connect from the main office to all 18 locations remotely (direct connection to the database).
You would need to create two transformations and populate a table with connection details for all locations.
Than you can use variables + sql script to switch from connection to connection and a package to run transformations
It is not straight forward but it is possible.

2 If you are not able to connect.
You can export the data into the files and put it on ftp site
In the main office download the files form the ftp server and load them into the database using mask.
Or instead if using Ftp you can just email the files to the main office ,
and in the main office again using Advanced ETL Processssor Ent download emails save the attachments into the directory and load the files into the database

Best Regards
Mike,