I have a database on a SQL Server 2000 machine with tables that need to be copied to a SQL Server 2005 database on a different machine. Both servers are on separate networks and the way our firewall is configured is such that the 2000 server can see the 2005 server but not the other way around; so it looks like my only option is to initiate the transfer from the 2000 server using DTS instead of SSIS from the 2005 server.
Every table in my 2000 database exists in the 2005 database and both have the exact same schema so there's no concern for crazy column mapping: it's just a straight column-to-column transfer. What I have done thus far is add a Transform Data Task for each table that needs to be copied but as the number of tables grow or shrink it has become bothersome to manage the DTS package.
My question: Is there some way I could dynamically specify which table is to be copied for a single Transform Data Task and then automatically reconfigure the column mappings and execute the task so that I won't have so many tasks to manage? I was hoping to just select a list of tables in an ActiveX Script Task then modify the settings for a single Transform Data Task that exists in the Package. Has anyone faced this issue before? If so, your thoughts?
I have attached a screenshot of my current DTS in which I have one Transform Data Task for each table to be copied.