Josh2442
asked on
Declare varible not working in Execute SQL task
Hi Experts,
I am trying to use a declare variable in the Execute SQL Task queryfield in SSIS and it is not returning me any resultsets. My query returns "PriorMonth" and "CurrentMonth" and I have this working in SSMS but the PriorMonth changing every quarter(according to the business reqt). Now the problem I am facing is if I keep that same query in "Execute SQL task" i get errors like "error while assigning value to the variable." On the result set in the General tab I have set it to Single Row and on the result set tab, I have two variable name which is as prior_month and current_month and the result set name is set as 0 and1. I would really appreciate your helpregarding this. Below is my query:
Declare @Current_Month VARCHAR(55);
DECLARE @Prior_Month_1 VARCHAR(20);
DECLARE @Prior_Month_2 VARCHAR(20);
DECLARE @Prior_Month_3 VARCHAR(20);
DECLARE @Prior_Month_4 VARCHAR(20);
DECLARE @Prior_month varchar(20);
set @Prior_Month_1 =''''+Convert(Varchar,date add(QUARTE R, -4,DATEADD(s,-1,DATEADD(mm , DATEDIFF(m,0,GETDATE())+1, 0))),101)+ ''''
set @Prior_Month_2 =''''+Convert(Varchar,date add(QUARTE R, -3,DATEADD(s,-1,DATEADD(mm , DATEDIFF(m,0,GETDATE())+1, 0))),101)+ ''''
set @Prior_Month_3 =''''+Convert(Varchar,date add(QUARTE R,-2,DATEA DD(s,-1,DA TEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))),101)+ ''''
set @Prior_Month_4 =''''+Convert(Varchar,date add(QUARTE R,-1,DATEA DD(s,-1,DA TEADD(mm, DATEDIFF(m,0,GETDATE())+1, 0))),101)+ ''''
SET @Current_Month =null;
DECLARE @month int;
SELECT
@Prior_month = CASE
WHEN MONTH(Getdate()) IN (1,2,3 ) THEN @Prior_Month_1
WHEN MONTH(Getdate()) IN (4,5,6 ) THEN @Prior_Month_2
WHEN MONTH(Getdate()) IN (7,8,9 ) THEN @Prior_Month_3
WHEN MONTH(Getdate()) IN (10,11,12) THEN @Prior_Month_4
ELSE NULL END
,
@Current_Month = ''''+ CONVERT(VARCHAR, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0) ),101)+''' '
I am trying to use a declare variable in the Execute SQL Task queryfield in SSIS and it is not returning me any resultsets. My query returns "PriorMonth" and "CurrentMonth" and I have this working in SSMS but the PriorMonth changing every quarter(according to the business reqt). Now the problem I am facing is if I keep that same query in "Execute SQL task" i get errors like "error while assigning value to the variable." On the result set in the General tab I have set it to Single Row and on the result set tab, I have two variable name which is as prior_month and current_month and the result set name is set as 0 and1. I would really appreciate your helpregarding this. Below is my query:
Declare @Current_Month VARCHAR(55);
DECLARE @Prior_Month_1 VARCHAR(20);
DECLARE @Prior_Month_2 VARCHAR(20);
DECLARE @Prior_Month_3 VARCHAR(20);
DECLARE @Prior_Month_4 VARCHAR(20);
DECLARE @Prior_month varchar(20);
set @Prior_Month_1 =''''+Convert(Varchar,date
set @Prior_Month_2 =''''+Convert(Varchar,date
set @Prior_Month_3 =''''+Convert(Varchar,date
set @Prior_Month_4 =''''+Convert(Varchar,date
SET @Current_Month =null;
DECLARE @month int;
SELECT
@Prior_month = CASE
WHEN MONTH(Getdate()) IN (1,2,3 ) THEN @Prior_Month_1
WHEN MONTH(Getdate()) IN (4,5,6 ) THEN @Prior_Month_2
WHEN MONTH(Getdate()) IN (7,8,9 ) THEN @Prior_Month_3
WHEN MONTH(Getdate()) IN (10,11,12) THEN @Prior_Month_4
ELSE NULL END
,
@Current_Month = ''''+ CONVERT(VARCHAR, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)
to handle date/time correctly, you should simplify your code... reading this article may help:
https://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
Usually, I create a view (or a table) that computes those values dynamically, so I can use them from anywhere without having to put the expression everywhere ...
and I agree that you might to put this code into a function or stored procedure ...
https://www.experts-exchange.com/Database/Miscellaneous/A_1499-DATE-and-TIME-don%27t-be-scared-and-do-it-right-the-first-time.html
Usually, I create a view (or a table) that computes those values dynamically, so I can use them from anywhere without having to put the expression everywhere ...
and I agree that you might to put this code into a function or stored procedure ...
ASKER
Hi All,
I am not allowed to call stored proc. I think it can be simplified. Here's what's needed to do. My priormonth should remain static for a quarter. Only the current month changes.. Sorry if it is too long, I just wanted to be clear. And I need to call this on my SSIS package via execute SQL task. Here's how it should look like:
Current month PriorMonth
October Sept
Nov. Sept
Dec Sept
Jan. Dec
Feb. Dec
Mar. Dec
Apr. Mar
May. Mar
Jun. Mar
Jul. June
Aug. June
Sept. June
Thanks in advance
I am not allowed to call stored proc. I think it can be simplified. Here's what's needed to do. My priormonth should remain static for a quarter. Only the current month changes.. Sorry if it is too long, I just wanted to be clear. And I need to call this on my SSIS package via execute SQL task. Here's how it should look like:
Current month PriorMonth
October Sept
Nov. Sept
Dec Sept
Jan. Dec
Feb. Dec
Mar. Dec
Apr. Mar
May. Mar
Jun. Mar
Jul. June
Aug. June
Sept. June
Thanks in advance
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for the reply. I am passing the currentmonth and priormonth as
variables in SSIS execute SQL task. I have attached a doc to show when the date starts as I have to go back as far as Dec 2011.
I need to capture both CurrentMOnth and PriorMonth so that I can use it again and again
in the entire package.Thanks in advance again Experts.
dateFormat.xls
variables in SSIS execute SQL task. I have attached a doc to show when the date starts as I have to go back as far as Dec 2011.
I need to capture both CurrentMOnth and PriorMonth so that I can use it again and again
in the entire package.Thanks in advance again Experts.
dateFormat.xls
I am not expert in SSIS and using variables actually, so I don't know what to suggest here ..
ASKER
I figured out a way to get it in SSIS but again thanks for your help
Also, if you explain in words what you need to achieve, perhaps we can come up with a solution that requires less code...