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,dateadd(QUARTER, -4,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))),101)+''''
set @Prior_Month_2 =''''+Convert(Varchar,dateadd(QUARTER, -3,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))),101)+''''
set @Prior_Month_3 =''''+Convert(Varchar,dateadd(QUARTER,-2,DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())+1,0))),101)+''''
set @Prior_Month_4 =''''+Convert(Varchar,dateadd(QUARTER,-1,DATEADD(s,-1,DATEADD(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)+''''
Josh2442Asked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
just a quick sql to show how to get what you need for all 12 months, in terms of formula:
;with months as (
select top 12 row_number() over (order by name) mon
  from sys.objects
)
select mon
     , datename(m, dateadd(m, mon, '2012-12-01')) month_name
     , mon - 1 - ( (mon-1) % 3) 
     , datename(m, dateadd(m, mon - 1 - ( (mon-1) % 3), '2012-12-01')) prev_month_name
  from months
;

Open in new window


put into play for your request:
 declare @var datetime
--get first of this month
set @var = convert(datetime, convert( varchar(8), getdate(), 120) + '01', 120)

select dateadd(m, datepart(m, @var) - 1 - (( datepart(m, @var) -1) % 3) , @var) 

Open in new window

0
 
ValentinoVBI ConsultantCommented:
If you need that much code, you're probably better off using a stored procedure.  It's more interesting for maintenance (you don't need to change your SSIS package if your logic changes for some reason) and you'll find it easier to use as well.

Also, if you explain in words what you need to achieve, perhaps we can come up with a solution that requires less code...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
to handle date/time correctly, you should simplify your code... reading this article may help:
http://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 ...
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Josh2442Author Commented:
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
0
 
Josh2442Author Commented:
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
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
I am not expert in SSIS and using variables actually, so I don't know what to suggest here ..
0
 
Josh2442Author Commented:
I figured out a way to get it in SSIS but again thanks for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.