CODEWITHSUNDEEP
ssis
Maximus
Maximus

Reputation: 812

Storing values to more than one variables from execute SQL task returned by different queries

I have a situation where I need to store values returned by 2 different queries into 2 different variables using single execute sql task. I know about the single row result set type which allow us to store more than one value, but it should be returned in single rows. Can anyone help with this?

Upvotes: 0

Views: 1691

Answers (1)

user1896267
user1896267

Reputation:

If you want to combine the results of two different queries into a single row, but with two columns, then you must make certain that the field type defined in both are the same and match the type define for the output variables. That is, as shown in the following example, Row1 must be the same type as variable test1 and Row2 must be the same type as variable test2.

I ran a test using your scenario that seemed to work.

1) Set up the following query that combined the results from two nested queries.

SELECT
    (SELECT Row2 FROM Tests WHERE TestID = 4) AS Row1
    ,(SELECT Row2 FROM Tests WHERE TestID = 5) AS Row2

2) Since the results returned are both of type varchar (or string), I set up in a SSIS package a variable with data type "String" called test1 and a second variable called test2.

3) Created an Execute SQL Task defining the above SQL statement for SQLStatement and set the ResultSet to "Single row".

4) For the Result Set, I added two new rows. The first has Result Name = 0 and Variable Name is "User::test1" and the second with Result Name = 1 and Variable Name is "User::test2". The idea here is that the result for Row1 from the Query will go to test1 and Row2 will go to test2.

I executed the task and it was successful.

Let me know if this helps.

Thanks.

Upvotes: 1

Related Questions