Full Result Set
In this section, we'll demonstrate how to set an object variable to hold the full result set and get each record from the variable. Let's continue to build the package.
- Open the package ExecuteSQLTask.dtsx in the project LearnSSIS1 if it is closed.
- Disable Contain 2 and enable Container 3 in the package.
- Create 3 variables FullResult, Name and Age as below.
- Right click "Full Result Set" task and choose "Edit..." to open the task editor and make the settings as follows.
- Click Result Set tab and then click Add button. Then change the NewResultName to 0 and set the Variable Name to User::FullResult.
- Click OK finish the task setting. The right click Foreach Loop Container and choose "Edit..." to open its editor.
- Click Collection tab, select "Foreach ADO Enumerator" as Enumerator and User::FullResult as ADO object source variable.
- Click Variable Mappings and select the User::Name and User::Age in the variable field. At last click OK button to save the settings.
- Edit "Script Task" in Container 3 and add "User::Name", "User::Age" in the ReadOnlyVariables and add the following code in the Script to display the result of the variable.
- Run the package and the following result will be displayed. Then click OK button you will have the name and age of the next record popped up.
The FullResult is defined as Object data type and will hold multiple rows after the SQL runs. The other 2 variables' Name and Age will hold the field value of each record.
The ResultSet field is set as "Full result set" and the SQLStatement was set as below.
SELECT name, age FROM dbo.student WHERE id = 1 or id = 2
After running the above SQL, the first 2 records of the table student will be returned.
The sequence of the variables must be set as User::Name first and then User::Age because they must be matched with the Select SQL statement.
MessageBox.Show(Dts.Variables["User::Name"].Value.ToString() + ":" + Dts.Variables["User::Age"].Value.ToString());
For details to make these settings, you can check the Script Task.
In summary, we use an object variable to hold the full result set and use foreach loop to to get each field of the record in the variable for further process. In the next section, we'll introduce the last ResultSet - XML and parameters in SQL statements.