• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 492
  • 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;

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)+''''
0
Josh2442
Asked:
Josh2442
  • 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...
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
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

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.

Join & Write a Comment

Featured Post

Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

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