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.

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). — Thom A, Jan 28 '19 at 12:36
By Stored Procedure, do you mean a File Connection? Because this is what I am using. — Boels Maxence, Jan 28 '19 at 13:26
No, by stored Procedure I mean the SQL Object. You're using an Execute T-SQL Task; you're not querying a file... — Thom A, Jan 28 '19 at 13:34
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. — Thom A, Jan 28 '19 at 14:22

Yahfoufi · Answer 1 · 2019-01-28T13:19:50.917

1

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

At the end of SQL Statement add the following line

SELECT @benefeciary as benefeciary, @gempar as gempar

In the Execute SQL Task, change the ResultSet option to single Row
Go To Result Set Tab
Map the Column index (starting 0) to the relevant Variable
```
0 >> benefeciary
1 >> gempar
```

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

edited Jan 28 '19 at 13:19

answered Jan 28 '19 at 12:36

Yahfoufi

2,220
1
22
41

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. – Thom A Jan 28 '19 at 12:39
1

@Larnu no problem, i will provide more details – Yahfoufi Jan 28 '19 at 12:39
It is not possible to click on the Add button in the Result Set section. Maybe because the script is a File Connection? – Boels Maxence Jan 28 '19 at 13:25
@BoelsMaxence check that the in the General Tab, the Result Set option is set to `Single Row`. – Yahfoufi Jan 28 '19 at 14:49

Alexander Volok · Answer 2 · 2019-01-28T13:14:30.243

1

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

edited Jan 28 '19 at 13:14

answered Jan 28 '19 at 12:39

Alexander Volok

5,630
3
17
33

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. – Boels Maxence Jan 28 '19 at 13:11
@BoelsMaxence, updated answer, you are using PRINT, it should be SELECT.. Print statements are ignored by SSIS.. – Alexander Volok Jan 28 '19 at 13:15
I removed the print from the sql script – Boels Maxence Jan 28 '19 at 13:17
Just add this as the last line: – Alexander Volok Jan 28 '19 at 13:18
Should I use the Parameter Mapping or the Result Set? – Boels Maxence Jan 28 '19 at 13:18
@BoelsMaxence ResultSet – Yahfoufi Jan 28 '19 at 13:20
You do not need in this case parameter mapping, but only result set. Please check my answer above, there is a SELECT you have to include as the last line your SQL script – Alexander Volok Jan 28 '19 at 13:20

Weihui Guo · Answer 3 · 2020-02-26T12:24:03.260

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.

edited Feb 26 '20 at 12:24

answered Jul 20 '19 at 21:30

Weihui Guo

3,669
5
34
56

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

3 Answers3

Linked