Hello,

I'm just starting to use SSIS, and I would appreciate some help on my
current challenge...

I have 20 source views I want to fetch data from, and insert them into 20
nearly identical destination tables in another database. The destination
tables have the same columns as the source views, in addition to a
"TransferId" column (to uniquely identify that specific data transfer).

I use a "Conditional Split" data flow task after the source to filter out
specific rows from the views (I only use a single output from the
Conditional Split).

Between the "Conditional Split" and the destination, I use a "Derived
Column" to fetch the value for the "TransferId" column from the variable
@TransferId.

I was starting to make nearly identical data flow tasks for each of the
view/table pairs when I thought that there must be a better way... Is it
possible to use a loop to perform the same task for all 20 views and tables?
The field names are identical in all the view/table pairs (except for the
"TransferId" column as specified above), so I'm hoping there's some sort of
auto-mapping I could use...

Thanks for any help and suggestions, all are welcome!


Regards,
Nils Magnus Englund

Hello Nils,

If everything is the exact same as it sounds then it should be a case of
simply calling the right view in the source adapter and the right destination
in the destination adpater. The fact that the @TransferID variable value
changes makes no difference because it is a data value and will not change
the fabric of the package/data flow.

You could grab the name of the views you want to use as the source and destinations
through a query and then use a variable as the source and destination object
names. You could then loop over this collection using a ForEach loop.

Allan