How to use a variable value created in an Execute SQL Task in Precedence Constraint Editor?

Question

I need to run n-multiple Execute SQL Task based on n-multiple variable value (isnull true or false). This variable is created in a ssms script in the previous execute sql task (CONDITIONAL QUERY). How can I output the variable value from the ssms script to the conditional expression in ssis?

my two variables are @beneficiary and @gempar. They either store a date type value or null.

Answer 1

In addition to map result set to variable, you may also need to set the execution result of the task to the variable using ExecValueVariable.

Attached is one of my SSIS packages with a variable named HasRecord. Click the Execute SQL Task and set the ExecValueVariable to the desired variable, User::HasRecord.

Answer 2

You can specify a ResultSet inside the execute SQL Task, and map the result to a variable by following these steps:

1. At the end of SQL Statement add the following line

   SELECT @benefeciary as benefeciary, @gempar as gempar
   

2. In the Execute SQL Task, change the ResultSet option to single Row

3. Go To Result Set Tab

4. Map the Column index (starting 0) to the relevant Variable

   0 >> benefeciary
   1 >> gempar
   

---

For more details, there are many articles describing this process:

1. SSIS Basics: Using the Execute SQL Task to Generate Result Sets
2. Map Result Sets to Variables in an Execute SQL Task
3. How To Set and Use Variables in SSIS Execute SQL Task

Answer 3

Your conditional query task should have a "Result Set" set to a "Single Row", just like on a screenshot:

Then just assign your variables to output columns:

If your query returns two values, you can use ordinal positions,

so ResultName = 0, can be mapped to a @beneficiary and ResultName 1 to @gempar

More details: SSIS Basics: Using the Execute SQL Task to Generate Result Sets

---

Update: The script used in T-SQL task should return values via SELECT, but not via PRINT:

--SOME SELECTs..
..
SELECT @benefeciary, @gempar