Parameterize OLE DB Destination?

[Windows Server]

I have a large number of tables to backfill and I was hoping to automate this process with a package parameter instead of creating a dataflow for each table (see screenshot below).

1. Package parameter like 'tbl_a, tbl_b, tbl_c'
2. Followed by an Execute Script that splits this string into an Array.
3. The array would then be enumated in a Foreach loop container.
   3a. SQL command from enumerated variable would be fed into an OLE DB Source SQL Command 'Select * from tbl_a', 'Select * from tbl_b', etc.
   3b. Table name or view name variable in OLE Destination would be given different table names 'tbl_a' , 'tbl_bl', tbl_c' through the enumerated variables 

Where it fails is when tbl_a, tbl_b is enumarated into OLE DB Destination

Error:

0xC020201B at Data Flow Task, OLE DB Destination [2]: The number of input columns for OLE DB Destination.Inputs[OLE DB Destination Input] cannot be zero.
Error:

0xC004706B at Data Flow Task, SSIS.Pipeline: "OLE DB Destination" failed validation and returned validation status "VS_ISBROKEN".

Is there a way to parameterize OLE DB Destination? 

Is there a way to avoid need column mappings or somehow query all the columns from the OLE DB Source to feed into OLE DB Destination? Each table is different and mapping the columns for one backfill would completely blow up the next backfill in the batch. 

Is there another Task I can use in place of OLE DB Destination that can be preceded by OLE DB Source?

Thanks in advance

View the full article