Go Premium for a chance to win a PS4. Enter to Win

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

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;

@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


  @Current_Month = ''''+ CONVERT(VARCHAR, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)),101)+''''
  • 3
  • 3
1 Solution
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...
Guy Hengel [angelIII / a3]Billing EngineerCommented:
to handle date/time correctly, you should simplify your code... reading this article may help:

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 ...
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Guy Hengel [angelIII / a3]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

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.
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 ..
Josh2442Author Commented:
I figured out a way to get it in SSIS but again thanks for your help

Featured Post


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now