Result of Execute Sql Task how the DataSet returns

The result dataset for Execute SQL task has the following four main types, and when Execute SQL task returns results, you need to use SSIS Variable to receive the data.

The data table code used in the example is as follows

Create Tabledbo.test (Codeint, namevarchar(Ten), txtNvarchar(Ten))Insert  intodbo.test (code,name,txt)Values(1,'a'N'b'),(2,'C'N'D'),(3,'e'N'F'),(4,'g'N'h')

1, when the result set is a single row, you need to define three variables to receive the data, the datatype of the variable to be consistent with the datatype of column, and the result The set specifies the mapping between the returned column and the received variable. In a downstream component, a variable is used to refer to the return value of the Execute SQL task.

2, when result set is full result set, the returned result set is replaced by the name, and the returned result set is stored in a variable of type object, which exists internally as a collection and holds the entire query result set.

Result name is 0, and the resulting set is stored in an object's SSIS variable.

3, how to use a variable of type object, the variable of type object is actually a dataset, each row of data is extracted and stored in a variable to be used by other components, so you must define SSIS Variable to hold the data.

In the foreach Loop component, Enumerator selects the foreach ADO Enumator and selects user::varobj in the ADO Object Source variable list.

Create three variables to hold the data, the data type of the variable is consistent with the data type of column in object, or SSIS will error.

In variable mappings, set the Ojbect column and the mapping,index=0 of the variable, representing the first column of object, using User::varcode to hold the first column of data, and so on.

Result of Execute Sql Task how the DataSet returns