Passing values to Variables in DTS

#1

Hello

I am using SQL Server 2000

In my Transform Data Task Properties' Source tab of my DTS package, I have a SQL Query to get the SOURCE data, which is filtered by time period (begin date - end date). In the Gobal Variable tab of package properties, I have two variables defined like: BeginDate with value of 2002Q1 and EndDate with value of 2004Q1. Now, my problem is that I am not having any luck addressing these variables in the source SQL Query. I tried the ? thinking the values from Global Variable will pass to the query in its respective order, but didn't work. Then I tried the SQL syntax %BeginDate% and %EndDate% but that, too, failed. Does anyone know how I can rewrite the WHERE clause of my source SQL Query to address the global variables and pass its values during the execution time? I think it's just my syntax that's wrong, but don't know how to correct it. When I click on the Parameter button on the source query box, it either says no parameter found or gives syntax error. Thanks for your help. Following is my query

-- MDMbr
Select PA_PRODUCT = DLE.PA_PRODUCT
DETAIL_PRODUCT = DLE.MajorProd
Market_Name = DLE.MktName
Source = 'PA'
System = DLE.System
FIN_ARR = 'FI'
REC_SUM_CAT = DLE.REC_SUM_CAT
REC_DETAIL_CAT = DLE.REC_DETAIL_CAT
MktSeg_Summary = T.MktSeg
TFM_GrpInd = T.TFM_GrpInd
DATA_TYPE = 'MED_MBRS'
PA_SRVC_TYPE_1 = 'MD_MBRS'
PA_SRVC_TYPE_2 = 'MD_MBRS'
Account = 'UNSPECIFIED'
Name = '0'
PA_SERVICE_1 = 'MD_MBRS'
PA_SERVICE_2 = 'MD_MBRS'
Trend_IND = 'TREND'
DMonth = '0'
Qtr = T.Qtr
Half = T.Half
[3QYTD] = T.[3QYTD]
Year = T.Year
Amount = Sum(MDMbrs)
Comments = '0
From dbo.NNTR_Q
inner join dbo.vwDimLEntity DL
on T.System = DLE.System an
T.CEMkt = DLE.CEMkt an
T.CEProd = DLE.CEPro

Where Qtr Between %BeginDate% AND %EndDate
-- Where Qtr Between ? AND

Group by DLE.PA_PRODUCT
DLE.MajorProd
DLE.MktName
DLE.System
DLE.REC_SUM_CAT
DLE.REC_DETAIL_CAT
T.MktSeg
T.TFM_GrpInd
T.Qtr
T.Half
T.[3QYTD]
T.Year

#2

JN Try wrapping the query in a stored procedure and passing the variables as

execute myprocedure ?,

in the query window.

#3

On Tue, 6 Apr 2004 15:06:07 -0700, "JN"
<anonymous (AT) discussions (DOT) microsoft.com> wrote:

Quote:

Yeah, I figured that after spending a few hours on using these parameters in the query window. I did create an sp and passed the parameters, which worked. I am bummed that it wouldn't work unless you wrap the code in an sp! I have lots of such DTS packages we ran every month/quarter so dates parameter are always there with UNION and joins so always needing to change the dates at numerous places at each run, which is why I thought of using parameter, but it didn't work the way I wanted so I guess I'll need to convert each SQL into an SP. The statement in my post referenced dates at 18 different places so you could see how time consuming and frustrating the task of changing dates can be each time the package is run if there isn't a global variable/parameter to take of these changing value.

If anyone else has a better solution to this (other than SQL code wrapped in SP), please let me know.

I am not sure exactly what you are doing.

If you have a SQL statement that contains a bunch of dates, and the
dates need to be changed, there are at least 2 easy ways to do this.

First, update a table with the dates, and use select statements to
retrieve the dates.

Second, and related to the above, create variables in the statement,
and assign the dates to them at the beginning of the statement. Then
you only have to edit them in one place each time you run it.

#4

On Wed, 7 Apr 2004 08:46:05 -0700, "JN"
<anonymous (AT) discussions (DOT) microsoft.com> wrote:

Quote:

DTJ,

I already have a table with only dates in it and yes, I tried that, too, but it failed in my situation (in the source query in Transform Data Task window). The only thing that has worked so far is the SP route. Your suggestion does work if a query is to run on Enterprise Manager's Query Builder or Query Analyzer, but not in Source tab of Data Transform Properties within a DTS package. It gives error regarding violation and some other such things. So it seems using sp is the only way to use variables in this kind of situation. Note that the dates are same through out the code, but due to numerous Unions, the same dates need to be repeated 18+ times on different UNION statements’ WHERE clause, which is why I wanted to use a variable. This query is about 5 pages long!

Thanks all for your help.

Actually it does work in DTS as we are doing it in a number of DTS
packages I created.

We have a table that we update from a web page, with the date range
the user wants. We use these dates in DTS packages.

#5

In message <F8B00055-5032-44F1-94E4-DAC7146F4C9C (AT) microsoft (DOT) com>, JN
<anonymous (AT) discussions (DOT) microsoft.com> writes

Quote:

Yeah, I figured that after spending a few hours on using these
parameters in the query window. I did create an sp and passed the
parameters, which worked. I am bummed that it wouldn't work unless you
wrap the code in an sp! I have lots of such DTS packages we ran every
month/quarter so dates parameter are always there with UNION and joins
so always needing to change the dates at numerous places at each run,
which is why I thought of using parameter, but it didn't work the way I
wanted so I guess I'll need to convert each SQL into an SP. The
statement in my post referenced dates at 18 different places so you
could see how time consuming and frustrating the task of changing dates
can be each time the package is run if there isn't a global
variable/parameter to take of these changing value.

If anyone else has a better solution to this (other than SQL code
wrapped in SP), please let me know.

Probably not better, but the old method was to build the statement up
first in script then set it. A a variation is to just change part of the
statement each tie such as the top few lines that hard code some DECLARE
and SET statements fro your variable values, but of course these are
updated dynamically at run-time. Some links-

Global Variables and SQL statements in DTS
(http://www.sqldts.com/default.aspx?205)

Use Global Variables in Execute SQL Tasks (GVCustomTask)
(http://www.databasejournal.com/featu...le.php/1462181)

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Passing values to Variables in DTS

microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts

Passing values to Variables in DTS - 04-06-2004 , 01:06 PM

RE: Passing values to Variables in DTS - 04-06-2004 , 04:41 PM

Re: Passing values to Variables in DTS - 04-06-2004 , 06:39 PM

Re: Passing values to Variables in DTS - 04-07-2004 , 05:36 PM

Re: Passing values to Variables in DTS - 04-12-2004 , 08:21 PM