Excute SQL Task and exporting results to variables

Question

Post reply

Excute SQL Task and exporting results to variables

jmhanks1373

SSChasing Mays

Points: 646
More actions
March 8, 2010 at 3:32 pm

#138619

Hello,
I'm trying to use an Execute SQL Task and use the results to set variables.
Scenario: Table with a list of fiscal years with begin and end dates. I would like to get the min(begin_date) and max(end_date) to pass to another part of the SSIS package. I have the SQL query to return one row, but am having a problem trying to set the variables.
Since this is executing a SQL script and returning 2 values to be used to set variables I'm not quite sure what I need to do. Everything I've found so far is for using a variable to be used in a SQL query. What I need is basically the reverse.
Any help is appreciated.
Thanks,
Jared

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

jmhanks1373 SSChasing Mays Points: 646 More actions · Answer 1

Ps. I'm using OLE DB connections to SQL Server 2008.

TangoVictor SSChampion Points: 10975 More actions · Answer 2

Depends what you're returning for a result set. On the Exec SQL task IDE set the result type as either single row or full result set. It's one of the drop down selections.

Then on the Result set tab click add, I generally name the results 0, 1, 2 ....

If your query returns a single row such as an aggregate or select top 1 then you map the result to the variables by position. If your result set is a single row with 3 columns then you will have 3 entries on the result set screen. The order of the entries top to bottom matches the order of the columns in the result set from left to right.

If it's multiple rows then you would need to create a variable as type object and select it on the result set screen. If you want to then use that object variable you can setup a for each loop and use the ADO Result set or something like that.

hth - Tom

jmhanks1373 SSChasing Mays Points: 646 More actions · Answer 3

Thanks for the help Tom. I thought I had tried that, but must have not done it correctly.

Now I just need to take my User::MinBegDate and User::MaxEndDate and pass it to an OLE DB data flow task.

I basically need to do the following in the OLD DB task:

Select column1, column2, column3, column4

from date_dim

where date_id between User::MinBegDate and User::MaxEndDate

I'm sure that's not the right syntax to pass variables, but that's essentially what I want to do.

Thanks for the help.

TangoVictor SSChampion Points: 10975 More actions · Answer 4

On the OLE Source enter "?" for the begin and end dates then click the parameters button, you can then map those to the variables.

jmhanks1373 SSChasing Mays Points: 646 More actions · Answer 5

Thanks again Tom. That was easier than I thought it was going to be.