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 use SELECT * FROM sys.tables as an SQL Query and Set Resultset to Full Result Set
4- Go To Result Set Tab and write 0 for ResultSetName and select
ObjVariable as Variable Name. (When working with Full result set, Result
set name must be 0)
Now you are done with getting values to object variable.