How to set multiple SSIS variables in SQL Task

Question

Say I want to get the key values of two tables and assign to two package variables.

I can easily assign one, setting the Execute SQL Task to return a single row. But is it possible to set multiple variables with multiple result sets?

I have this SQL:

SELECT COALESCE(MAX(LogKey),0) AS LogKey FROM Log
SELECT COALESCE(MAX(HeaderKey),0) AS HKey FROM Header

I've tried setting the ResultSet property to both single row and Full result set, but neither seem to work. Do I have to use a separate Execute SQL task?

phoenixgr · Accepted Answer · 2013-07-17 11:50:48Z

3

You could try this to force the values on the same resultset:

SELECT
A = (SELECT COALESCE(MAX(LogKey),0) AS LogKey FROM Log),
B = (SELECT COALESCE(MAX(HeaderKey),0) AS HrKey FROM Header)

answered Jul 17, 2013 at 11:50

phoenixgr

1865 bronze badges

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

Collectives™ on Stack Overflow

How to set multiple SSIS variables in SQL Task

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related