how to load a variable from "Execute Sql Task" in SSIS package

Question

I have some variables (at package level, at dataflow level, doesn't matter) and I need to load them by running an Execute Sql Task. I added an Execute Sql Task, I have wrote my sql statement in filed SqlStatement, I have changed the ForcedExecutionValueType to string. Now I want that when the Execute Sql Task is executed the return value that that select returns, to be the exact value for my variable.

 example: 

 Select employer_name from employers where id=1
 Returs="John Doe"

 value for @employer_name must be "John Doe"

Any hints?

I have found the best explaination by Allan Mitchell. Try his link — BogdanM
– BogdanM, Commented Mar 14, 2013 at 13:32

user1896267 · Accepted Answer · 2013-03-14 22:23:28Z

3

Your more detailed discussion of the issue is a little difficult to follow, so I aplogize if my following steps cover items with you are already familiar.

Steps:

Under the Control Flow tab, drag over an Execute SQL Task and open it.
Select General on the left.
Define the Connection (based on a database source created in Connection Managers below).
Define the SQL Statement.
For Result Set, select Single Row.
Select Result Set on the left.
Select the Add button and notice that a new record was added above.
Under the Result name column header for the new record, change "NewResultname" to "0".
Under Variable Name column header for the new record, select either an existing User variable you've already created or scroll to the top and create a new one.
Select OK and you're done.

Running the Execute SQL Task by itself will now populate the variable. Make certain you've verified that the SQL will return only one value and no more. Otherwise, you will need to modify your SQL with "TOP 1" in it to be on the safe side. When multiple values are expected, then you to apply a variable defined with a Data Type of "Object" and use "Full Result set" instead of "Single Row" in the Execute SQL Task.

Hope this helps.

answered Mar 14, 2013 at 22:23

user1896267

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

1 Comment

BogdanM Over a year ago

Tnak you so much, it helped.

Collectives™ on Stack Overflow

how to load a variable from "Execute Sql Task" in SSIS package

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related