I’m having trouble with SQL variables in SSIS. I’m pretty new to this stuff. Here’s what’s going on:
I’ve got this SQL query:
declare @startDate datetime, @endDate datetime, @monthStart datetime, @monthEnd datetime, @maxResponseTime int
set @endDate = '2014-06-27'
set @maxResponseTime = 24
set @startDate = dateadd(day, -90, @endDate)
set @monthStart = dateadd(day, -2, @endDate)
set @monthEnd = dateadd(day, -1, @endDate)
When I put this in the OLE DB Source Editor using SQL command mode, all the variables turn into question marks. It looks weird, like this:
DECLARE ? datetime, ? datetime, ? datetime, ? datetime, ? int
SET ? = ?
SET ? = 24
SET ? = dateadd(day, -90, ?)
SET ? = dateadd(day, -2, ?)
SET ? = dateadd(day, -1, ?)
The query builder gives me a syntax error saying ‘The Declare SQL construct or statement is not supported.’
What’s causing this? How can I fix it? Do I need to set up these variables in SSIS instead of the SQL query? Any help would be great!
SSIS restricts the use of T-SQL variable declarations within the SQL Command text, which is why the variables are automatically replaced by question marks. Instead of declaring and setting variables directly in your SQL query, you can use SSIS package variables.
By creating package variables, you can update your query to include parameter placeholders (the question marks) and then map each placeholder to a corresponding package variable.
For example, change your query to something like:
SELECT * FROM YourTable
WHERE Date BETWEEN ? AND ?
AND ResponseTime <= ?
This approach aligns with SSIS practices and resolves the syntax errors you encountered.
hey there OwenExplorer55! i’ve run into this before too. SSIS doesn’t like variable declarations in SQL queries. try moving those variables into SSIS package variables instead. You can then use them in your SQL with ? placeholders. It’s a bit diffrent but works better. have you tried that approach?
yo owen, ClimbingMonkey’s right. SSIS can be picky bout SQL variables. what i do is set up package variables in SSIS, then use parameters in the query. like SELECT * FROM table WHERE date BETWEEN ? AND ?. then map SSIS vars to those ? in the params tab. should fix ur issue!