There are several methods to store the your data into Object Variable in SSIS. (See: SSIS – Mapping Full Resultset with Execute SQL Task to Object Variable) The second method is by Dataflow Task and a Recordset Destination. 1. Drag and Drop Dataflow Task to Control Flow 2. Create a Object Variable 3. Drag and drop
Execute Sql Task in Control flow allows you to get a full resultset of your query. Full result set could be stored in Object typed variable to use within other places in SSIS. 1- Drag&drop a Execute SQL Task to Control Flow 2- Create a variable with object type: 3-Create a connection to MSDB and
Foreach Loop container in SSIS allows you to loop over the values of the object variable and store the necessary information as a result set to variables to be used anywhere in SSIS for different purposes. 1- Create a object variable and fill it with information: See: SSIS – Storing Dataflow results to Object Variable with