SSIS package Execute SQL task dynamic SQL (not SP) with multiple returns but only capture final variable

Question

I have an SSIS package that is executing dynamic SQL. The Dynamic SQL will sometimes have result set and sometimes not, but I only want to capture the COUNT, which is the returned value in below script.

(or can I use C# Script task to accomplish this?), I know I can loop through record sets in C#, but I wont always have 2, is there way to tell difference in result sets by name or something, or only by index of array in resultset[x]?

-- These are passed values in SSIS as paramaters
DECLARE @VariablePassedForSelect AS VARCHAR(50) = ?
DECLARE @ScriptToExecute NVARCHAR(MAX) = ?


-- putting return value count from dynamic script into variable
DECLARE @ReturnRowsEffectedA INT

-- this MAY (or may not) return a result set as dynaic code
exec sp_executesql @ScriptToExecute, N'@VariablePassedForSelect BIGINT, @ReturnRowsEffected INT OUTPUT', @VariablePassedForSelect = @VariablePassedForSelect, @ReturnRowsEffected = @ReturnRowsEffectedA OUTPUT

-- now select results for returning,this is only value I want
SELECT @ReturnRowsEffectedA   AS RowCountR

All this works fine in SQL and returns the result set from the dynamic SQL and the count, but I only want the COUNT returned into a variable in SSIS.

If I use result set, I get error for single row (because of the dynamic sql can return results and count).

Three are reasons I am doing it this way and not using SP's or other means, but those details are not required for my question.

How can I only capture just the value for @ReturnRowsEffectedA?

Answer 1

You can do this using an Output parameter. First, create an SSIS variable that will hold the value. I'm going to use the name you already have and call this variable [User::ReturnRowsEffectedA]. You won't need @ReturnRowsEffectedA in the actual query. You can keep most of the query text the same, but replace @ReturnRowsEffectedA in the sp_execute call with ? to denote we're going to use an SSIS parameter variable. You Execute SQL query will look like this:

-- These are passed values in SSIS as paramaters
DECLARE @VariablePassedForSelect AS VARCHAR(50) = ?
DECLARE @ScriptToExecute NVARCHAR(MAX) = ?


-- this MAY (or may not) return a result set as dynaic code
exec sp_executesql @ScriptToExecute, N'@VariablePassedForSelect BIGINT, @ReturnRowsEffected INT OUTPUT', @VariablePassedForSelect = @VariablePassedForSelect, @ReturnRowsEffected = ? OUTPUT

Then in your Parameter Mapping, you will add a third entry (the first two will be your input params that feed @VariablePassedForSelect and @ScriptToExecute). You'll use [User::ReturnRowsEffectedA] as the Variable Name, set Direction to Output, and change Parameter Name to 2. You can leave the datatype set to LONG and the size set to -1.

I created an example on my system to illustrate how this works: Here's my query. I don't pass in the script, for simplicity. But it does still use an input and an output parameter.

Here you can see I have ResultSet set to None:

The Execute SQL Task only uses one parameter, so the Parameter Name here is 0.

I have paused execution after the task has completed so you can see that [User::ResultVar] is populated with the value of 1, which is the expected value here.

Edit: To add the results of the proc to an Object-type variable in SSIS, you'd first create an Object-type variable, then you'd set the ResultSet property to Full result set and then map the results to the variable.