(OLE DB) Execute SQL Task - Using output parameters

Question

I'm trying to use "Execute SQL Task" in SSIS to get a value to be stored in a user variable. The documentation says to use '?' as a placeholder for parameters in the query but when I do I get a syntax error trying to parse the query. So the query is like this:

SELECT ? = AVG(Score) FROM Scorecards

I've also tried adding OUTPUT after the '?'. All examples I can find for having an output parameter are similar to

EXEC ? = MyStoredProc param1, param2, ...

This isn't of any use though since I'm not using a stored proc. The use of the parameter seems similar though so why won't SSIS take it for a select statement?

I can't find an example in my code, but I'm sure you also have to use the Parameter Mapping and/or Result Set to get the data to the user variable. — thursdaysgeek
– thursdaysgeek, Commented Mar 25, 2011 at 22:02
Yes, I've also added to Parameter Mapping though SSIS isn't getting that far. It gives me a syntax error when I press "Parse Query" to check if it think the statement is correct. — nuit9
– nuit9, Commented Mar 25, 2011 at 22:09

grapefruitmoon · Accepted Answer · 2011-03-26 11:53:52Z

2

You'll need to set the ResultSet property to Single Row, then amend your query to something like:

select avg(score) as AvgScore from ScoreCards

Then in the Result Set pane, click Add, set the Result Name to AvgScore, and your variable to Variable Name (for example, User::AvgScore).

answered Mar 26, 2011 at 11:53

grapefruitmoon

3,0281 gold badge23 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

nuit9 Over a year ago

I tried this. While it does execute successfully the variable is not updated in the variables window when it hits a breakpoint after the task.

grapefruitmoon Over a year ago

The variable value doesn't update in that window. You'll be able to to check the value if you add a watch on the variable and open the Watch or Locals window during execution.

richardpilgrim Over a year ago

I was using an ODBC driver and found using the number 1 as the Result Name worked instead of entering the column alias. Just in case that helps anyone out there :)

Collectives™ on Stack Overflow

(OLE DB) Execute SQL Task - Using output parameters

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related