SSIS Foreach Loop enumerate different SQL Server views data into a single staging table

Question

I'm trying to setup a SSIS package to load multiple views data into a single staging table. All the views have same column structure and needs to be loaded into a staging table which also has same column structure.

Purpose: we have 30 different views and each view contains different logic and needs to be fed into a single destination table.

Current solution: using a DFT task with 30 different sequence containers that contains simple source and target adapter for each views.

Required solution: use a single Foreach loop container and enumerate the 30 views and load each view's result into the single target table. This could help us to easily scale up the task if required in future.

Answer 1

Figure the answer:

• Created two variables. Variable 1 as Object datatype and Variable 2 as String

• Using a Execute SQL, wrote a SQL statement like below sample, and set the "Result set" property to "Full Result Set", On the Result Set property Assigned the Object type variable for Result Name 0.

  Select 'select * from view1' as SQL_Code UNION ALL
  Select 'select * from view2' as SQL_Code 
  

• Added a Foreach Loop with Collection as "Foreach ADO Enumerator"

• Under Enumerator Configuration, chose the Variable1 which is the Object type and set the Enumeration mode to "Rows in the First Table"

• In the Foreach Loop Variable Mappings, Choose the Variable2 which is the string datatype.

• Inside the Foreach Loop, added a DFT task, and used a simple source and target adapter. In source adapter, choose the SQL Command from Variable option and chose the Variable2 as source. Then set the ValidateExternalMetadata property to "False" for both source and target adapters and set the DelayValidation Property of the DFT task to True.

Now, when I execute the package, the Execute SQL task loads the Variable1 of object variable with each row as "Select * from View" for each views that I wrote in the sql statement of the Execute SQL task

Foreach Loop, enumerates on each row and loads that "Select * from View1" statement to the Variable1 which will be the source SQL command for each load.