SSIS Dynamic Column Mapping

Question

I am using SSIS to do data transfer from OLEDB SQL to OLEDB SQL. Both databases are SQL Server.

I have over 100 tables to transfer data from database-A to database-B (truncate-insert) and I want to do this with dynamic data task flow & one package.

Each table has different column names.

My steps:

• There is a parameter table includes source-table select statement / source table name / target table name.

• I've created variable to set parameter tables results ( source table select / target table etc.) and variables are set to default values.

• I've created "Execute Script Task" to read my parameter table and assigned result set to object variable (Var_Sytem_Object)

• I've created "Foreach Loop Container"

  • In the collection tab

    • Set Enumarator to "Foreeach ADO Numerator"
    • Set ADO object source variable to object variable (Var_Sytem_Object)
    • Set Enumarator mode to "rows in the first table"

  • In the variable mappings tab

    • Set variables according to sequence

• I've created "Data Flow Task"

  • OLEDB Source is set and source select variable (v_source_select) is set.
  • OLEDB Destination is set and target table name variable (v_target_table) is set.
  • Column mapping process is done based on default variables values.

After this configuration, when I executed package second loop takes error in the second loop of container.

Because of error, mapping wants same column mapping. Is there any way to manage dynamic column mapping or another way to transfer data in one package.

Answer 1

I will use a script task for this.

1. Creat a table with the following columns A. Source connection B. Destination connection C. Source query D. Destination table In mine I have additional columns like E. Pre execute query - where I truncate data, drop indexes or cause an error on week days so it only runs on weekends, buffer size, error log, timeout etc
2. Add a for each container and point to the table The table will have one line item for each of the hundred tables
3. In your script task you will use the current row to make an oledb connection, execute the sql in the load query column, into the table in the destination table column, using the destination connection. I hope this makes sense.