I have 40 field databases and 1 consolidated database which is a subset (all
tables but not all coumns) of all of the field databases. There are over 100
tables in the database and depending upon change requests, the tables
sometimes get fields added. Also, depending upon connectivity issues, the
databases may not get updated every day while others are updated daily.

I have a DTS process that I have set up and tested which will allow me to
get the data from one table but I do not want to go through the steps of
creating the Transform Data Task 100 times. Plus I do want to have to go into
the package everytime there is a column or table added.

I have created a query that looks at sysobjects, syscolumns and a user table
that lists all of the databases in the field and the last time they were
updated. With this I am able to get a list of all tables and columns on the
fly along with the databases and the last update date that I can tie to the
creation date field in the tables.

What I need is to tie the 2 together. I already loop through all of the
servers with no problem but I would like to loop through the tables (with
columns provided) as well while making use of the LastUpdate field.

Any suggestions?