Solved

Declare varible not working in Execute SQL task

Posted on 2012-12-24
7
466 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
Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Passing Parameter to Stored Procedure 4 25
sql server service accounts 4 29
SQL Count issue 24 18
physical_device_name field in SQL 3 6
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 different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

825 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