I've set up a SSIS control flow that selects the following columns from a table into a system.object: db2SrcQuery, destTblName
A loop then maps variables via the Foreach ADO Enumerator:
User::db2SrcQuery / User::destTblName
Inside the loop a data flow task connects to DB2 and executes the User::db2SrcQuery's select statement's result into the destination table (User::destTblName).
All works great except for the big showstopper of table schema changes.
Found a blog with a nice solution for this except that it uses OpenQuery -- something the client stands firm on not using. I need to stay with connection managers for the db2 extraction - also, they don't want files so bulk insert is not an option.
Are there any great suggestions out there on how to do this in SSIS?
1. Foreach ADO Enumerator collection / variable mapping of sql query & dest table name
a. connect to db2 via connection manager and exec sql variable command
b. insert result into destination table (via table name variable) -- table schemas differ