2

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

enter image description here

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/

Improve this question

1 Answer 1

Reset to default
3

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

enter image description here

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

enter image description here

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

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

1 Comment

You are welcome @d2907. Can you please mark this as an accepted answer if it answers your question.

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.