August 20, 2002 at 8:42 pm
I'm trying to run a DTS package from command line whilst specifying the global variables. All documentation seems to state that you can use the "/A" flag to identify your variables, but it's not working for me. Is this because its only available under SQL 2000?
August 21, 2002 at 3:08 am
You are correct here is the information from SQL 7 BOL
quote:
dtsrun UtilityThe dtsrun utility allows you to retrieve, execute, delete, and overwrite a package created using Data Transformation Services. The DTS package can be stored in the Microsoft® SQL Server™ msdb database, a COM-structured storage file, or Microsoft Repository. You can display the available switches by entering
dtsrun ?
Syntax
dtsrun [{/? | {/[~]S server_name {/[~]U user_name [/[~]P password] | /E}
| {/[~]F filename/[~]R repository_database_name} }
{/[~]N package_name [/[~]M package_password] |
[/[~]G package_guid_string] | [/[~]V package_version_guid_string]}
[/!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, /F, /R, /N, /M, /G, and /V 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
Is the network name of the server running SQL Server to which to connect.
/U user_name
Is a login ID used to connect to the server running 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.
/M package_password
Is an optional password assigned to the DTS package when it 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.
/F filename
Is the name of a structured storage UNC file containing DTS packages. If server_name is also specified, then the contents of filename are overwritten with the DTS package retrieved from SQL Server.
/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.
/!X
Retrieves the DTS package from SQL Server, and overwrites the contents of filename, without executing the package. If this option is not specified, then the DTS package is executed immediately.
/!D
Deletes the DTS package from 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
Spaces between command switches and values are optional. Embedded spaces in values must be embedded in double-quotes.
If an option is specified multiple times, the last occurrence takes precedence.
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 Repository, use:
dtsrun /Sserver_name /Uuser_nName /RRepository_nName /Ppassword /Npackage_name /Mpackage-password
It is always a good idea to keep a copy of SQL 7 BOL handy. If you need a copy I can send it too you or check on the MS site to download.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
August 21, 2002 at 5:49 pm
No, thats ok. By default I usually check the web, thus got mislead on the /A flag. I have SQL 7 BOL, and though it didn't state it there, I thought a later patch may have introduced it.
It was quite annoying to find out that my package couldn't run from the command line. Fortunately I found another way to set my global variables from outside the package through the use of ActiveX scripting (InputBox), which will have to suffice for now.
November 20, 2002 at 8:45 am
This is one way of ruuning a DTS Package form the command line
DTSRun /S "(local)" /N "Package_name" /G "{09391365-4C6C-413A-8206-BF69C0A273FA}" /W "0" /E
Substitute "Package_name" with name of the required package
The "{09391365-4C6C-413A-8206-BF69C0A273FA}" value as shown above is the local Package GUID, to work this value out i just view it in design mode >> Package Properties >> General Tab
Use any relevant switches as required
Hope this helps
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply