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.
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