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

ssis precedence constraint expression multiple conditions
ssis precedence constraint not working
what is the difference between the success and the completion value of precedence constraint?
precedence constraints in real-time systems
containers in ssis
what is the delayvalidation property of data flow task? why does one use this property?
variables in ssis
checkpoint in ssis

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.

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

Working with Precedence Constraints in SQL Server Integration , This means that you can use tasks with precedence constraints to determine the For example, after the first Execute SQL task runs, the precedence Note that I created the PrecedenceConstraints.dtsx package in SSIS 2005. The precedence constraint expression then uses the variable value to  Connect the Toss Script Task to each of the Execute SQL Tasks. 17. you need to change the precedence constraints so that the control will pass to the appropriate tasks. 18. Double-click the precedence constraint pointing to Heads to bring up the Precedence Constraint Editor. Change the Evaluation Operation from Constraint to Expression.

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

Precedence Constraints, Precedence constraints link executables, containers, and tasks in The precedence constraint uses a constraint value, an expression, both, The precedence constraint can use the following execution results Variable names are case-sensitive. Click OK to close the Precedence Constraint Editor. The constraint value is set to Success and the expression is @X >== @Z. Task B, the constrained task, runs only if task A completes successfully and the value of variable X is greater than or equal to the value of variable Z. Executables can also be linked by using multiple precedence constraints that contain different expressions.

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.

Using Precedence Constraints, Using Boolean Expressions with Precedence Constraints For example, the value of a variable that changes as the package executes can be compared to a Create a new SSIS project in SQL Server Data Tools and create a new package. 3. Double-click the Clear Results Task to open the Execute SQL Task Editor. 6. Use an Execute SQL task to populate the variable: on the General tab set the Result Set to “Full Result Set”. On the Result tab map the Result Name “0” to the variable you created above. 2) Create a string variable Add a Foreach Loop. On the Collection tab, set the Enumberator to “Foreach ADO Enumerator”.

Overview of SSIS Precedence Constraints, We use SSIS precedence constraints to define the program flow from one task to another. In this editor, we have the following SSIS Precedence Constraints options: Evaluation operation: Expression and Constraint; Value: Success SSIS Precedence constraint returns true because execute SQL task 1  1. Create 2 variables "MyVariable" and "OVariable" of type string. The initial value of MyVariable is "Hello World". 2. Add SQL Task to set you SQL as below. I have 2 parameters one for INPUT(right ?) and other OUTPUT (left ?) in the sql statement. 3.

SSIS Expression Tasks vs Evaluating variables as expressions, Flow Component; Precedence constraints: conditions can be set using expressions Derived Column Transformation: uses values created by using Expression editor: The textbox where the user must enter the Execute SQL Task in SSIS: SqlStatementSource Expressions vs Variable Source Types. In this particular case, the variable value is EMPTY, i.e., NULL. USING SELECT. Just like SET, we can also use SELECT to assign values to the variables, post declaring a variable using DECLARE. Below are different ways to assign a value using SELECT: Example: Assigning a value to a variable using SELECT . Syntax:

Pro SQL Server 2005, Create a new user-defined variable called JobCandidateCount with a data in the SQL statement section, change the Connection property to use the Mail Task Editor in the General properties, change the Name property to Pull Advertising. Create a precedence constraint from Execute SQL task to the Retain or Start  You set the property value in the Properties window or by using the Execute SQL Task Editor. The conversion will not result in data truncation. Map result sets to variables in an Execute SQL Task. This section describes how to create a mapping between a result set and a variable in an Execute SQL task.

Comments
  • Not really sure what you mean here, but you can capture the output of a SELECT statementin a Execute T-SQL Task my using the ResultSet setting in General Pane, and then mapping the individual columns (if using Single Row) or the dataset object (if using Full result set) to a variable in the Result Set pane. Or, if you're using a Stored Procedure with OUTPUT parameters, you map them in the Parameter Mapping pane (unsurprisingly).
  • By Stored Procedure, do you mean a File Connection? Because this is what I am using.
  • No, by stored Procedure I mean the SQL Object. You're using an Execute T-SQL Task; you're not querying a file...
  • Images of code isn't very helpful to the users, however, in your SQL you don't have any output parameters. As I mentioned, OUTPUT parameters are part of a Stored Procedure; you can't have them as part of an inline statement. If you want to use those, you'll need to convert your SQL to a Stored procedure.
  • I am not able to click on the Add button in the Result Set section. I updated the question with the SQL T-Script which is put as file connection in the Execute SQL Task.
  • @BoelsMaxence, updated answer, you are using PRINT, it should be SELECT.. Print statements are ignored by SSIS..
  • I removed the print from the sql script
  • Just add this as the last line:
  • Should I use the Parameter Mapping or the Result Set?
  • Link only answers are generally not well received on Stack Overflow; they often attract down votes, the links can become dead (making them useless to future readers), and there is a specific review option for the removal of them in the low quality queue. To improve your answer, I would suggest adding the relevant parts of the articles you have linked, otherwise this is likely better as a comment.
  • @Larnu no problem, i will provide more details
  • It is not possible to click on the Add button in the Result Set section. Maybe because the script is a File Connection?
  • @BoelsMaxence check that the in the General Tab, the Result Set option is set to Single Row.