Dynamic Column Mappings in OLE DB source/destination Data Flow

• A. Roney

  SSC Veteran

  Points: 261

  More actions

  November 15, 2006 at 3:14 pm

  #115061

  Hello,

  I have a table that stores a SQL Server name, a database name and a schema.table name in 6 separate columns - 1 each for source and target (i.e. source server, target server, source db, target db etc.).

  I want to create an SSIS package that loops through these records and does the following:

  1)  Sets the Servername of my source and target datasources

  2)  Sets the Initial Catalog to the Database name in my source and target datasources

  3)  Extracts the data from the source schema.table and inserts it into the target schema.table.

  Using package variables, I can set the data source servername and initial catalogs just fine for the connection each time I loop through a record (using a ForEachLoop Control Flow).

  My problem is that I can also set the OLE DB Source/Destination tables (using a data access mode = "table name or view name variable"), but I lose the mappings with each iteration of a new table.  This is a straight shot of one table to another with identical column structure.  Does anyone know how to dynamically set the mappings so that as I loop through each table, my mappings line up between my source and destination?

  Thanks in advance for any help you can provide.

• November 20, 2006 at 8:00 am

  This was removed by the editor as SPAM

• Suresh Bose

  SSC Veteran

  Points: 213

  More actions

  November 21, 2006 at 11:17 pm

  #673383

  Hi,

  Did you try using Script Component? I hope it's quite possible to achieve it usng the script component.

  Thanks,

  S Suresh