Import and Schedule DTS on MSDE

I have .dts file from another sql server. I would like to import and install
this file as a DTS package on MSDE and schedule it to run periodically.
How do I do this in MSDE 2000.
You can use DTSrun.exe
Look at
http://groups.google.it/groups?q=DTS...hx.gbl&rnum=18
"inquisite" <inquisite@.discussions.microsoft.com> wrote in message
news:392C38CD-4DB3-4D39-AF2A-0A14CED0D74F@.microsoft.com...
> I have .dts file from another sql server. I would like to import and
install
> this file as a DTS package on MSDE and schedule it to run periodically.
> How do I do this in MSDE 2000.
|||Will it be possible for anyone of you to help me with a example, for the syntax to use in command prompt with DTSrun.exe.
The situation is this. I need to uninstall SQL Server and install MSDE for my client. I have DTS running already in SQL Server. I need to take a back up, and make it run in MSDE. The package name is dtsPack.dts. I have it in the location "c:\"
Thanks for your help|||Here's the section from Books Online, including examples at the end:
The dtsrun utility executes a package created using Data Transformation Services (DTS). The DTS
package can be stored in the Microsoft SQL ServerT msdb database, a COM-structured storage file, or
SQL Server Meta Data Services.
Syntax
dtsrun
[/?] |
[
[
/[~]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]
]
Arguments/?
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.
/E
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.
/Z
Indicates that the command line for dtsrun is encrypted using SQL Server 2000 encryption.
/!X
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.
/!D
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.
/!Y
Displays the encrypted command used to execute the DTS package without executing it.
/!C
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.
Remarks
Security Note Batch files may contain credentials stored in plain text. Credentials may be echoed
to the user's screen during batch execution.
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
HRESULT 25
Pointer 26
LPSTR 30
LPWSTR 31
For information about where to find or how to run this utility, see Getting Started with Command
Prompt Utilities.
Examples
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 /Rrepository_name
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ngaya" <ngaya.1pivm3@.mail.mcse.ms> wrote in message news:ngaya.1pivm3@.mail.mcse.ms...
> Will it be possible for anyone of you to help me with a example, for the
> syntax to use in command prompt with DTSrun.exe.
> The situation is this. I need to uninstall SQL Server and install MSDE
> for my client. I have DTS running already in SQL Server. I need to take
> a back up, and make it run in MSDE. The package name is dtsPack.dts. I
> have it in the location "c:\"
> Thanks for your help
>
> --
> ngaya
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message1244207.html
>
|||THanks a lot for your time. I still have one more question to be clarified. Should i save the DTS in the database msdb? or will it automatically get saved? Ok if i save it, should i attach the database fresh to the MSDE?
Thanks in advance
Ngaya|||Thanks...I got it at last...I took a backup of msdb database and restored in MSDE, and tried
exec msdb..sp_enum_dtspackages
go
to see the list and it was there.
I was able to run the pacakge successfully using dtsrun.
Phew!...this took a lot of my timelah!... If i had known the simple thing that dts packages are stored in msdb database...this would have been over long back...thanks for that info..
ngaya|||You can also open a package on the originating server and save as file, copy file, open file on dest
server and save to msdb.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ngaya" <ngaya.1pm9u1@.mail.mcse.ms> wrote in message news:ngaya.1pm9u1@.mail.mcse.ms...
> Thanks...I got it at last...I took a backup of msdb database and
> restored in MSDE, and tried
> exec msdb..sp_enum_dtspackages
> go
> to see the list and it was there.
> I was able to run the pacakge successfully using dtsrun.
> Phew!...this took a lot of my timelah!... If i had known the simple
> thing that dts packages are stored in msdb database...this would have
> been over long back...thanks for that info..
> ngaya
>
> --
> ngaya
> Posted via http://www.mcse.ms
> View this thread: http://www.mcse.ms/message1244207.html
>