Re: Passing values to Variables in DTS

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Darren Green (darren.green_at_reply-to-newsgroup-sqldts.com)
Date: 04/13/04 

Date: Tue, 13 Apr 2004 02:21:29 +0100

In message <F8B00055-5032-44F1-94E4-DAC7146F4C9C@microsoft.com>, JN
<anonymous@discussions.microsoft.com> writes
>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/features/mssql/article.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

Relevant Pages

  • Re: Multiple text files to multiple tables
    ... > That's for bulk insert but if you really want to usr dts you can do a similar thing setting a global variable using dtsrun then using a dynamic properties task to set it as the source file. ... These subsequent SQL tables will then be queried to ... The batch file passes Global Variables to a DTS package that first ... .txt file to the staging table which works well. ...
    (microsoft.public.sqlserver.dts)
  • Re: Dynamic Properties Task Not consistant
    ... the DTS's stored on the SQL Server and at run time it sets the SQL Server, ... SQL Catalog, SQL Username, SQL Password, and the Textfile to be used to ... The first step in my DTS is a dynamic Properties Task ... which sets these global variables to the SQL connection and the Textfile ...
    (microsoft.public.sqlserver.dts)
  • Re: DTSRun and Batch file
    ... >> install the DTS DLLs on the local machine. ... So the DTS package is running ... >> user to start a SQL Agent Job that runs the package. ... Darren Green (SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)
  • Re: How do I run a .BAS file saved from a DTS package?
    ... Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP) ... I have used DTS for import and export. ... > The DTS Designer is easy to understand and use. ...
    (microsoft.public.sqlserver.dts)
  • Re: Using a parameter in a subquery
    ... Using the INNER JOIN works great! ... > Global Variables and Stored Procedure Parameters ... > the SQL statement, but maintain the entire statement dynamically. ... > Darren Green (SQL Server MVP) ...
    (microsoft.public.sqlserver.dts)