Solved

Declare varible not working in Execute SQL task

Posted on 2012-12-24
7
463 Views
Last Modified: 2013-01-02
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
Comment
Question by:Josh2442
  • 3
  • 3
7 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38719944
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38719963
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
 

Author Comment

by:Josh2442
ID: 38720113
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 38720128
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
 

Author Comment

by:Josh2442
ID: 38720419
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 38726713
I am not expert in SSIS and using variables actually, so I don't know what to suggest here ..
0
 

Author Closing Comment

by:Josh2442
ID: 38737624
I figured out a way to get it in SSIS but again thanks for your help
0

Featured Post

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now