CODEWITHSUNDEEP
sql-servervisual-studiossis
BogdanM
BogdanM

Reputation: 997

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

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?

Upvotes: 1

Views: 7147

Answers (1)

user1896267
user1896267

Reputation:

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:

  1. Under the Control Flow tab, drag over an Execute SQL Task and open it.
  2. Select General on the left.
  3. Define the Connection (based on a database source created in Connection Managers below).
  4. Define the SQL Statement.
  5. For Result Set, select Single Row.
  6. Select Result Set on the left.
  7. Select the Add button and notice that a new record was added above.
  8. Under the Result name column header for the new record, change "NewResultname" to "0".
  9. 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.
  10. 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.

Upvotes: 3

Related Questions