No, execute SQL task return only one result set at a time. If we are using multiple results set in SQL script it will throw an error.
There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".
Let’s see the example
I am taking execute SQL task
Now I am doing the configuration.
Here I am configuring Result set if full result set and the connection type is OLEDB.
Writing the two select statement in the SQL statement. It will return two result set. To storing this result sets I am creating two object variable.
Mapping this variable with Resultset.
Click ok. Now we are able to run this package
As expected this package is failed
With below error.
There is an invalid number of result bindings returned for the ResultSetType: "ResultSetType_Rowset".
Now I am keeping only one dataset.
Also removed one result set
Now running this package.
Package executed successfully.
Note: we can use multiple inserts, update and delete query. But in this case, we need to select result set is none.
How do you get to the window showing "General, parameter Mapping, Result Set, Expressions"?
ReplyDeleteRight Click on the Execute SQL Task control box, and then click Edit
DeleteRight click on the Execute SQL Task control box, and then click Edit
DeleteRight-click on the Execute SQL Task control box, and then click Edit
ReplyDeleteHi Bagesh, I just found out that in this case the resultset is not in the form of multiple DataTables. It will be in a single DataSet. We can add a Script component after this control block to split the DataSet into DataTables, like this:
ReplyDeleteobject dsObject = Dts.Variables["User::objDataSet"].Value;
DataSet ds = (DataSet)dsObject;
Dts.Variables["User::objDT1"].Value = ds.Tables[0];
Dts.Variables["User::objDT2"].Value = ds.Tables[1];
And after the Script Task block you can have a split control to process User::objDT1 and User::objDT2