0

As the title says, I'm trying to use multiple variables in a single Execute SQL Task. Something like this:

declare @payyear int

select max(PAY_YEAR) as payyear
from dbo.table1
select ? = @payyear

declare @payperiod int

select max(pay_period) as payperiod 
from dbo.table1 
where pay_year = ?)

select * 
from dbo.table1
where [payPeriod] >= @payperiod and [payYear] = ?

How do I get @payperiod into a variable like @payyear is as ? or do I have to do that query as a separate Execute SQL Task and make it it's own variable?

Or is there a better way altogether to do what I'm trying to achieve. The Select query after the 2 first ones declaring the variables is much more complicated with multiple joins and a ROW_NUMBER() OVER (PARTITION BY f.field1 ORDER BY f.field2) AS row_Num in it. I'm wondering if making it a stored procedure is a better idea. But at some point, I have to show the user output so they can manually make changes and add certain ID's to a not in clause before actually creating the table to be merged later. Right now it's just a series of 11 .sql files in a solution that can easily be messed up by a mistype somewhere in the files or a missing , or '. I'm trying to lock it down as much as possible. A dba, developer or someone with SQL skills usually does this process (myself) but the company wants documentation to be written up so that anyone can do it (yeah, exercise in futility but that's what they want in case I get hit by the proverbial bus) and it's a very difficult process if you don't have the skills because you have to comment and uncomment parts so you can see the data and make changes prior to creating a table with it. I have pre-created the tables and just truncate them with a GO between in the Execute SQL task prior to this one. This is my first real deep look into using SSIS as an option.

I do have experience with Powershell and XAML, so that could be an option too. I'm thinking maybe ps2exe to convert to an executable to further lock it down and only show the output of the queries in a gridview or listview.

Any thoughts or suggestions appreciated.

Improve this question
9
  • I am a bit confused and I think you are making this more difficult. In an Execute SQL task you can pass multiple values/parameters by using multiple ? (the order they are assigned is in the parameters mapping section of the SQL task. Or if it is complicated, as you said, just create an SP. If you need to RETURN multiple values into variables you can do that as well with the Result set (can use single result set and set the values to specific variables on output). Commented Mar 18 at 18:22
  • 1
    Ahh, thank you @Brad, in all the documentation I've read so far, I've only seen examples of a single variable. That makes much more sense. I'll give it a shot right away and let you know how I do! Commented Mar 18 at 18:25
  • @Brad Do you think you can give me an example using my parameters? when I try to use an declare statement in the query, it gives me a parse error. even though, it clearly shows it's possible here. link Commented Mar 18 at 19:19
  • Anytime I declare variables in an Execute SQL task I declare them all at the very top of the script. Then I set the values using the ? (for values I am passing) all at the top of the script task. I see you doing this, this is not right: select ? = @ payyear are you trying to return the value as a result set? You do that differently. If you are trying to set the variable = ? then you can do SET @ payyear = ? Commented Mar 18 at 19:26
  • If you are trying to set the @ payyear to the results of the first query you can just do Select @ payyear = max(PAY_YEAR) If that is what you are trying to do. Commented Mar 18 at 19:29

1 Answer 1

Reset to default
1

From your comments and the script it does not look like you are trying to pass any values to the Script task, just use values you lookup inside the script task?

If that is the case you do not need the ? at all (as that is only used/needed if you are passing a variable value from the SSIS package into the script task). You just set variables like you would in a normal SQL script in SSMS.

If you need to return the result set, the select does that, you just need to make sure you update the result set values in the Script Task to accept the values (if multiple rows, as an Object variable).

I think this is what you are trying to do:

declare @payyear int
declare @payperiod INT

SELECT @payyear = MAX(PAY_YEAR)
from dbo.table1

Select @payperiod = MAX(pay_period)
from dbo.table1
WHERE pay_year = @payyear

Select *
FROM dbo.table1
where payPeriod >= @payperiod
AND payYear = @payyear

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

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.