BI Infrastructure

SSIS – Storing Dataflow Results to Object Variable with…

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 a Datasource

4.Drag and drop a RecordSet Destination and Map object variable

Now you are done with getting values to object variable.

BI Infrastructure

SSIS – Mapping Full Resultset with Execute SQL Task…

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.

BI Infrastructure

SSIS – Looping Over Object Variables with as ADO…

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 Recordset Destination
See: SSIS – Mapping Full Resultset with Execute SQL Task to Object Variable
(Both examples above fill Object variable with sys.tables data within the msdb database in SQL server.)

2- Create two more variables to hold the table name (string) and object id (integer)

3- Drag&Drop a Foreach Loop container to Dataflow:

4- Go Collection tab and Select “Foreach ADO Enumerator” for Enumeration and select your ObjVariable as “ADO Object Source Variable” and specify “Enumeration Mode” as “Rows in the First Table”

5- Go to Variable Mappings Tab and add your map your variables to relevant index of your data in the Object Variable

Variable index is zero based and must be index of the column in the dataset. We use Tablename and objectId which indexes are 0,1 in order.

6- Drag&Drop  Script Component to see the values mapped and run the package.