Dynamically mapping source & target (has to be copy column)

Hi All, i am pretty new to sql server and i have a task to be accomplished, but somehow got stuck up with the below

I have 2 databases in 2 servers,each database has same tables, say


Database_name Server Name Tablename
Eg:(1)
Database 1: Northwind --- Server A --- Customers
Database 2: Datawarehouse --- Server B --- Customers

Eg(2)
Database 1: Northwind --- Server A --- Orders
Database 2: Datawarehouse --- Server B --- Orders


Eg(2)
Database 1: Northwind --- Server A --- Employees
Database 2: Datawarehouse --- Server B --- Employees

Now the problem is, using activeX task first i input the source table name and assign that to a global variable, then assign source table to target table

DTSGlobalVariables("SourceTableName").Value = inputbox("Enter the Source Table Name:")
DTSGlobalVariables("TargetTableName").Value = DTSGlobalVariables("SourceTableName").Value

Then i use a dynamic task to change the source table and destination table name of the datapump task.

The first time i choose the transformation , i say copy coulmn ....... say the source and target tables are customers initially.


This package executes fine for the mapping i choose

But when i enter orders as tablename ....... everything gets changed ,.......... except the mapping ....... the mapping dosent change even if the source and destination table names of the datapump change.

Can someone help me out in resolving how to automap source and destination when they have same number of columns, same names and datatypes


I know it looks wiered to do this way .... but my project manager wants it this way!!