Setting a String Variable in SSIS

Question

I am trying to set a string variable in SSIS using an execute sql command.

For this task I have implemented this expression:

"select" +@[User::ID]+ "=  columnA
from table
where columnB = '123' "

Just to check that I am doing this in the right way I have added and script task to show, using a message box ,the value of the variable

But when I execute the task I am getting this error message from the execute sql task:

[Execute SQL Task] Error: Executing the query ""select" +@[User::ID]+ "= >columnA from table where columnB = '123' " ..." failed with the following error: "Incorrect syntax near '='.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I have also tried to follow this example, but always with an error as an answer http://dataqueen.unlimitedviz.com/2012/08/how-to-set-and-use-variables-in-ssis-execute-sql-task/

Answer 1

This is not how you do it. You need to setup your Execute SQL Task, so that it has a result set of a single row and type your SQL query as if you type it in management studio. See this snapshot

Then you will go to the result set of the task, and tell it to assign the results to your variable. See this snapshot

You need to make sure that your query will only return a single row. Otherwise, you will get an error.