DTS package using stored procedure with date parameters

Posted on 2005-03-25
Medium Priority
Last Modified: 2013-11-30
hi, is it possible to create a dts package using a sp with
date parameters (startdate and enddate) to create a
report and export it from sql server 2000 into excel?
Question by:graphix03
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

ABaruh earned 1920 total points
ID: 13633572
In the DTS you would use a Global variable.  Global variables are only accessible inside an "ActiveX Script Task".  If you've not worked with ActiveX Script tasks, they are very simple.  Essentially, they accept any VBScript code so you can use ADODB to connect to your database and execute the stored procedure passing in the Global Variable values (which already have to have been set.

So, how do you pass the parameters to the global variables?  If you're accessing the package through VB/VC++ code, you should be able to access the package object directly.  If you're calling the DTS using DTSRun at the command line, you supply the values for the global variables like shown in the dtsRun info here:

dtsrun Utility
The dtsrun utility executes a package created using Data Transformation Services (DTS). The DTS package can be stored in the Microsoft® SQL Server™ msdb database, a COM-structured storage file, or SQL Server Meta Data Services.

[/?] |
        /[~]S server_name[\instance_name]
        { {/[~]U user_name [/[~]P password]} | /E }
        {/[~]N package_name }
        | {/[~]G package_guid_string}
        | {/[~]V package_version_guid_string}
    [/[~]M package_password]
    [/[~]F filename]
    [/[~]R repository_database_name]
    [/A global_variable_name:typeid=value]
    [/L log_file_name]
    [/W NT_event_log_completion_status]
    [/Z] [/!X] [/!D] [/!Y] [/!C]


Displays the command prompt options.


Specifies that the parameter to follow is hexadecimal text representing the encrypted value of the parameter. Can be used with the /S, /U, /P, /N, /G, /V, /M, /F, and /R options. Using encrypted values increases the security of the command used to execute the DTS package because the server name, password, and so on, are not visible. Use /!Y to determine the encrypted command.

/S server_name[\instance_name]

Specifies the instance of SQL Server to connect to. Specify server_name to connect to the default instance of SQL Server on that server. Specify server_name\instance_name to connect to a named instance of SQL Server 2000 on that server.

/U user_name

Is a login ID used to connect to an instance of SQL Server.

/P password

Is a user-specified password used with a login ID.


Specifies a trusted connection (password not required).

/N package_name

Is the name of a DTS package assigned when the package was created.

/G package_guid_string

Is the package ID assigned to the DTS package when it was created. The package ID is a GUID.

/V package_version_guid_string

Is the version ID assigned to the DTS package when it was first saved or executed. A new version ID is assigned to the DTS package each time it is modified. The version ID is a GUID.

/M package_password

Is an optional password assigned to the DTS package when it was created.

/F filename

Is the name of a structured storage file containing DTS packages. If server_name is also specified, the DTS package retrieved from SQL Server is executed and that package is added to the structured storage engine.

/R repository_database_name

Is the name of the repository database containing DTS packages. If no name is specified, the default database name is used.

/A global_variable_name:typeid=value

Specifies a package global variable, where typeid = type identifier for the data type of the global variable. The entire argument string can be quoted. This argument can be repeated to specify multiple global variables. See the Remarks section for the different available type identifiers available with global variables.

To set global variables with this command switch, you must have either Owner permission for the package or the package must have been saved without DTS password protection enabled. If you do not have Owner permission, you can specify global variables, but the values used will be those set in the package, not those specified with the /A command switch.

/L log_file_name:

Specifies the name of the package log file.

/W Windows_Event_Log

Specifies whether or not to write the completion status of the package execution to the Windows Application Log. Specify True or False.


Indicates that the command line for dtsrun is encrypted using SQL Server 2000 encryption.


Blocks execution of the selected DTS package. Use this command parameter when you want to create an encrypted command line without executing the DTS package.


Deletes the DTS package from an instance of SQL Server. The package is not executed. It is not possible to delete a specific DTS package from a structured storage file. The entire file needs to be overwritten using the /F and /S options.


Displays the encrypted command used to execute the DTS package without executing it.


Copies the command used to execute the DTS package to the Microsoft Windows® clipboard. This option can also be used in conjunction with /!X and /!Y.

If you do not specify any command line switches, specify an incorrect command line switch, or your command statement contains a syntax error, dtsrun returns error information and usage instructions. If you enter dtsrunui on the command line without any command line switches, you will start the DTS Run utility.

Spaces between command switches and values are optional. Embedded spaces in values must be embedded between double quotation marks.

If an option is specified multiple times, the last occurrence takes precedence. One exception is the /A command switch. Specifying more than one /A switch creates multiple global variables.

When specifying a global variable with the /A command switch, you must use a type identifier to indicate the data type of the global variable.

A tilde (~) character after the forward slash (for example, /~Z) indicates that the parameter value is encrypted and what follows is the hexadecimal text of the encrypted value.

The table shows the global variable data types and their IDs.

Data type Type ID
Integer (small) 2
Integer 3
Real (4-byte) 4
Real (8-byte) 5
Currency 6
Date 7
String 8
Boolean 11
Decimal 14
Integer (1-byte) 16
Unsigned int (1-byte) 17
Unsigned int (2-byte) 18
Unsigned int (4-byte) 19
Integer (8-byte) 20
Unsigned int (8-byte) 21
Int 22
Unsigned int 23
Pointer 26

For information about where to find or how to run this utility, see Getting Started with Command Prompt Utilities.

To execute a DTS package saved as a COM-structured storage file, use:

dtsrun /Ffilename /Npackage_name /Mpackage_password

To execute a DTS package saved in the SQL Server msdb database, use:

dtsrun /Sserver_name /Uuser_nName /Ppassword /Npackage_name /Mpackage_password

To execute a DTS package saved in Meta Data Services, use:

dtsrun /Sserver_name /Uuser_nrame /Ppassword /Npackage_name /Mpackage_password /Rrepository_name
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 80 total points
ID: 13633761
>>Global variables are only accessible inside an "ActiveX Script Task".  <<
Not entirely true.  You can also pass then into a stored procedure using the "Execute SQL Task" object, so there is no need for an ActiveX script, you can execute the stored procedure directly.

Author Comment

ID: 13633923
oh my god, Abaruh, you are so great. thanks so much.
I will give it a try.
thanks to acperkins too for your input. i will work on
it and see.  thanks very much again to both.

Featured Post

What Is Blockchain Technology?

Blockchain is a technology that underpins the success of Bitcoin and other digital currencies, but it has uses far beyond finance. Learn how blockchain works and why it is proving disruptive to other areas of IT.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
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.

764 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