4

enter image description hereI 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.

enter image description here

Improve this question
4
  • 1
    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). Commented Jan 28, 2019 at 12:36
  • By Stored Procedure, do you mean a File Connection? Because this is what I am using. Commented Jan 28, 2019 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... Commented Jan 28, 2019 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. Commented Jan 28, 2019 at 14:22

3 Answers 3

Reset to default
1

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

enter image description here

Then just assign your variables to output columns:

enter image description here

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
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

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?
|
1

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
Improve this answer

4 Comments

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.
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.

enter image description here

enter image description here

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.