CODEWITHSUNDEEP
sql-serverssis
bsivel
bsivel

Reputation: 2959

Setting multiple variables from a Execute SQL Task result object with a single row

I have the following sql in an EXECUTE SQL TASK:

SELECT [CnxnStrValue1] as INT_Support_CnxnStr
,[CnxnStrValue2] as Lawson_CnxnStr
,[CnxnStrValue3] as Lawson_HRIS_CnxnStr
FROM  [dbo].[InterfaceDBCnxn] 
WHERE InterfaceName = ?

The result set is set to an object variable. I also have three string variables to hold the values and typically I would map them to a For Each Loop Container. But, in this case, my result set will always only be one row because InterfaceName is the primary key of the table.

Whats is the best way to set the variables with out using a for each loop container?

Upvotes: 2

Views: 2695

Answers (2)

billinkc
billinkc

Reputation: 61269

Change your result set from Full to Single Row. I use this pattern for my DW loads to get the surrogate key value for my unknown members.

enter image description here

ResultSet set to Single row

enter image description here

Map your parameters as needed. Here, I have 8 variables that get mapped

enter image description here

Upvotes: 3

edhedges
edhedges

Reputation: 2718

Given your table is Table and a column is Column_name and Column_name_two you can do something like this.

SELECT @yourVar = Column_name,
       @yourSecondVar = Column_name_two
FROM Table
WHERE Table_id = 1

Upvotes: -2

Related Questions