Jaa

dtutil Utility

  • Artikkeli

The dtutil command prompt utility is used to manage SQL Server 2005 Integration Services (SSIS) packages. The utility can copy, move, delete, or verify the existence of a package. These actions can be performed on any SSIS package that is stored in one of three locations: a Microsoft SQL Server database, the SSIS Package Store, and the file system. The storage type of the package is identified by the /SQL, /FILE, and /DTS options.

Note

On a 64-bit computer, Integration Services installs both a 32-bit and a 64-bit version of the dtutil utility (dtexec.exe).

Note

Many of the operations that are performed by dtutil can also be performed visually in SQL Server Management Studio when you are connected to an instance of Integration Services. For more information, see Managing Integration Services Packages.

The options can be typed in any order. The pipe ("|") character is the OR operator and is used to show possible values. You must use one of the options that are delimited by the OR pipe.

All options must start with a slash (/) or a minus sign (-).

Arguments must be strings that are either enclosed in quotation marks or contain no white space.

Double quotation marks within strings that are enclosed in quotation marks represent escaped single quotation marks.

Options and arguments, except for passwords, are not case sensitive.

Syntax

dtutil /option [value] [/option [value]]...

Parameters

Option

Description

/?

Displays the command prompt options.

/C[opy] location;destinationPathandPackageName

Specifies a copy action on an SSIS package. Use of this parameter requires that you first specify the location of the package using the /FI, /SQ, or /DT option. Next, specify the destination location destination package name. The destinationPathandPackageName argument specifies where the SSIS package is copied to. If the destination location is SQL, the DestUser, DestPassword and DestServer arguments must also be specified in the command.

When the Copy action encounters an existing package at the destination, dtutil prompts the user to confirm package deletion. The Y reply overwrites the package and the N reply ends the program. When the command includes the Quiet argument, no prompt appears and any existing package is overwritten.

/Dec[rypt] password

(Optional). Sets the decryption password that is used when you load a package with password encryption.

/Del[ete]

Deletes the package specified by the SQL, DTS or FILE option. If dtutil cannot delete the package, the program ends.

/DestP[assword] password

Specifies the password that is used with the SQL option to connect to a destination SQL Server instance using SQL Server Authentication. An error is generated if DESTPASSWORD is specified in a command line that does not include the DTSUSER option.

ms162820.note(en-US,SQL.90).gifNote:

When possible, use Windows Authentication..

/DestS[erver] server_instance

Specifies the server name that is used with any action that causes a destination to be saved to SQL Server. It is used to identify a non-local or non-default server when saving an SSIS package. It is an error to specify DESTSERVER in a command line that does not have an action associated with SQL Server. Actions such as SIGN SQL, COPY SQL, or MOVE SQL options would be appropriate commands to combine with this option.

A SQL Server instance name can be specified by adding a backslash and the instance name to the server name.

/DestU[ser] username

Specifies the user name that is used with the SIGN SQL, COPY SQL, and MOVE SQL options to connect to a SQL Server instance that uses SQL Server Authentication. It is an error to specify DESTUSER in a command line that does not include the SIGN SQL, COPY SQL, or MOVE SQL option.

/DT[S] filespec

This option specifies that the SSIS package to be operated on is located in the SSIS Package Store. The filespec argument is a relative path, starting at the root of the SSIS Package Store.

If the DT[S] option is specified on the same command line as any of the following options, a DTEXEC_DTEXECERROR is returned:

  • FILE
  • SQL
  • SOURCEUSER
  • SOURCEPASSWORD
  • SOURCESERVER

/En[crypt] {SQL | FILE}; Path;ProtectionLevel[;password]

(Optional). Encrypts the loaded package with the specified protection level and password, and saves it to the location specified in Path. The ProtectionLevel determines whether a password is required.

  • SQL - Path is the destination package name.
  • FILE - Path is the fully-qualified path and file name for the package.
  • DTS - This option is not supported currently.

ProtectionLevel options:

Level 0: Strips sensitive information.

Level 1: Sensitive information is encrypted by using local user credentials.

Level 2: Sensitive information is encrypted by using the required password.

Level 3: Package is encrypted by using the required password.

Level 4: Package is encrypted by using local user credentials.

Level 5 Package uses SQL Server storage encryption.

/Ex[ists]

(Optional). Used to determine whether a package exists. dtutil tries to locate the package specified by either the SQL, DTS or FILE options. If dtutil cannot locate the package specified, a DTEXEC_DTEXECERROR is returned.

/FC[reate] {SQL | DTS};ParentFolderPath;NewFolderName

(Optional). Create a new folder that has the name that you specified in NewFolderName. The location of the new folder is indicated by the ParentFolderPath.

/FDe[lete] {SQL | DTS}[;ParentFolderPath;FolderName]

(Optional). Deletes from SQL Server or SSIS the folder that was specified by the name in FolderName. The location of the folder to delete is indicated by the ParentFolderPath.

/FDi[rectory] {SQL | DTS};FolderPath[;S]

(Optional). Lists the contents, both folders and packages, in a folder on SSIS or SQL Server. The optional FolderPath parameter specifies the folder that you want to view the content of. The optional S parameter specifies that you want to view a listing of the contents of the subfolders for the folder specified in FolderPath.

/FE[xists ] {SQL | DTS};FolderPath

(Optional). Verifies if the specified folder exists on SSIS or SQL Server. The FolderPath parameter is the path and name of the folder to verify.

/Fi[le] filespec

This option specifies that the SSIS package to be operated on is located in the file system. The filespec value can be provided as either a Universal Naming Convention (UNC) path or local path.

If the File option is specified on the same command line as any of the following options, a DTEXEC_DTEXECERROR is returned:

  • DTS
  • SQL
  • SOURCEUSER
  • SOURCEPASSWORD
  • SOURCESERVER

/FR[ename] {SQL | DTS} [;ParentFolderPath; OldFolderName;NewFolderName]

(Optional). Renames a folder on the SSIS or SQL Server. The ParentFolderPath is the location of the folder to rename. The OldFolderName is the current name of the folder, and NewFolderName is the new name to give the folder.

/H[elp] option

Displays text extensive help that shows the dtutil options and describes their use. The option argument is optional. If the argument is included, the Help text includes detailed information about the specified option. The following example displays help for all options:

dtutil /H

The following two examples show how to use the /H option to display extended help for a specific option, the /Q [uiet] option, in this example:

dtutil /Help Quiet

dtutil /H Q

/I[DRegenerate]

Creates a new GUID for the package and updates the package ID property. When a package is copied, the package ID remains the same; therefore, the log files contain the same GUID for both packages. This action creates a new GUID for the newly-copied package to distinguish it from the original.

/M[ove] {SQL | File | DTS}; pathandname

Specifies a move action on an SSIS package. To use this parameter, first specify the location of the package using the /FI, /SQ, or /DT option. Next, specify the Move action. This action requires two arguments, which are separated by a semicolon:

  • The destination argument can specify SQL, FILE, or DTS. A SQL destination can include the DESTUSER, DESTPASSWORD, and DESTSERVER options.
  • The pathandname argument specifies the package location: SQL uses the package path and package name, FILE uses a UNC or local path, and DTS uses a location that is relative to the root of the SSIS Package Store. When the destination is FILE or DTS, the path argument does not include the file name. Instead, it uses the package name at the specified location as the file name.

When the MOVE action encounters an existing package at the destination, dtutil prompts you to confirm that you want to overwrite the package. The Y reply overwrites the package and the N reply ends the program. When the command includes the QUIET option, no prompt appears and any existing package is overwritten.

/Q[uiet]

Stops the confirmation prompts that can appear when a command including the COPY, MOVE, or SIGN option is executed. These prompts appear if a package with the same name as the specified package already exists at the destination computer or if the specified package is already signed.

/R[emark] text

Adds a comment to the command line. The comment argument is optional. If the comment text includes spaces, the text must be enclosed in quotation marks. You can include multiple REM options in a command line.

/Si[gn] {SQL | File | DTS}; path; hash

Signs an SSIS package. This action uses three required arguments, which are separated by semicolons:

  • The destination argument can specify SQL, FILE, or DTS. A SQL destination can include the DESTUSER, DESTPASSWORD and DESTSERVER options.
  • The path argument specifies the location of the package to take action on.
  • The hash argument specifies a certificate identifier expressed as a hexadecimal string of varying length.

Information about your certificate can be found by using the CertMgr utility. For more information, see the CertMgr section in the topic, "Signing and Checking Code with Authenticode," in the MSDN Library.

/SourceP[assword] password

Specifies the password that is used with the SQL and SOURCEUSER options to enable the retrieval of an SSIS package that is stored in a database on a SQL Server instance that uses SQL Server Authentication. It is an error to specify SOURCEPASSWORD in a command line that does not include the SOURCEUSER option.

ms162820.note(en-US,SQL.90).gifNote:

When possible, use Windows Authentication.

/SourceS[erver] server_instance

Specifies the server name that is used with the SQL option to enable the retrieval of an SSIS package that is stored in SQL Server. It is an error to specify SOURCESERVER in a command line that does not include the SIGN SQL, COPYSQL, or MOVESQL option.

A SQL Server instance name can be specified by adding a backslash and the instance name to the server name.

/SourceU[ser] username

Specifies the user name that is used with the SOURCESERVER option to enable the retrieval of an SSIS package stored in SQL Server using SQL Server Authentication. It is an error to specify SOURCEUSER in a command line that does not include the SIGN SQL, COPY SQL, or MOVE SQL option.

ms162820.note(en-US,SQL.90).gifNote:

When possible, use Windows Authentication.

/SQ[L] package_path

Specifies the location of an SSIS package. This option indicates that the package is stored in the msdb database. The package_path argument specifies the path and name of the SSIS package. Folder names are terminated with back slashes.

If the SQL option is specified on the same command line as any of the following options, a DTEXEC_DTEXECERROR is returned:

  • DTS
  • FILE
    The SQL option may be accompanied by zero or one instance of the following options:
  • SOURCEUSER
  • SOURCEPASSWORD
  • SOURCESERVER

If SOURCEUSERNAME is not included, Windows Authentication is used to access the package. SOURCEPASSWORD is allowed only if SOURCEUSER is present. If SOURCEPASSWORD is not included, a blank password is used.

ms162820.note(en-US,SQL.90).gifImportant:

Do not use a blank password. Use a strong password.

dtutil Exit Codes

dtutil sets an exit code that alerts you when syntax errors are detected, incorrect arguments are used, or invalid combinations of options are specified. Otherwise, the utility reports "The operation completed successfully". The following table lists the values that the dtutil utility can set when exiting.

Value Description

0

The utility executed successfully.

1

The utility failed.

4

The utility cannot locate the requested package.

5

The utility cannot load the requested package

6

The utility cannot resolve the command line because it contains either syntactic or semantic errors.

Remarks

You cannot use command files or redirection with dtutil.

The order of the options within the command line is not significant.

Examples

The following examples detail typical command line usage scenarios.

Copy Examples

To copy a package that is stored in the msdb database on a local instance of SQL Server using Windows Authentication to the SSIS Package Store, use the following syntax:

dtutil /SQL srcPackage /COPY DTS;destPackage

To copy a package from a location on the File system to another location and give the copy a different name, use the following syntax:

dtutil /FILE c:\myPackages\mypackage.dtsx /COPY FILE;c:\myTestPackages\mynewpackage.dtsx

To copy a package on the local file system to an instance of SQL Server hosted on another computer, use the following syntax:

dtutil / FILE c:\sourcepkg.dtsx / COPY SQL;destpkgname

Because the /DestU[ser] and /DestP[assword] options were not used, Windows Authentication is assumed.

To create a new ID for a package after it is copied, use the following syntax:

dtutil /I /FILE copiedpkg.dtsx

To create a new ID for all the packages in a specific folder, use the following syntax:

for %%f in (C:\test\SSISPackages\*.dtsx) do dtutil.exe /I /FILE %%f

Use a single percent sign (%) when typing the command at the command prompt. Use a double percent sign (%%) if the command is used inside a batch file.

Delete Examples

To delete a package that is stored in the msdb database on an instance of SQL Server that uses Windows Authentication, use the following syntax:

dtutil /SQL delPackage /DELETE

To delete a package that is stored in the msdb database on an instance of SQL Server that uses SQL Server Authentication, use the following syntax:

dtutil /SQL delPackage /SOURCEUSER srcUserName /SOURCEPASSWORD #8nGs*w7F /DELETE

Note

To delete a package from a named server, include the SOURCESERVER option and its argument. You can only specify a server by using the SQL option.

To delete a package that is stored in the SSIS Package Store, use the following syntax:

dtutil /DTS delPackage.dtsx /DELETE

To delete a package that is stored in the file system, use the following syntax:

dtutil /FILE c:\delPackage.dtsx /DELETE

Exists Examples

To determine whether a package exists in the msdb database on a local instance of SQL Server that uses Windows Authentication, use the following syntax:

dtutil /SQL srcPackage /EXISTS

To determine whether a package exists in the msdb database on a local instance of SQL Server that uses SQL Server Authentication, use the following syntax:

dtutil SQL srcPackage /SOURCEUSER srcUserName /SOURCEPASSWORD *hY$d56b /EXISTS

Note

To determine whether a package exists on a named server, include the SOURCESERVER option and its argument. You can only specify a server by using the SQL option.

To determine whether a package exists in the local package store, use the following syntax:

dtutil /DTS srcPackage.dtsx /EXISTS

To determine whether a package exists in the local file system, use the following syntax:

dtutil /FILE c:\srcPackage.dtsx /EXISTS

Move Examples

To move a package that is stored in the SSIS Package Store to the msdb database on a local instance of SQL Server that uses Windows Authentication, use the following syntax:

dtutil /DTS srcPackage.dtsx /MOVE SQL;destPackage

To move a package that is stored in the msdb database on a local instance of SQL Server that uses SQL Server Authentication to the msdb database on another local instance of SQL Server that uses SQL Server Authentication, use the following syntax:

dtutil /SQL srcPackage /SOURCEUSER srcUserName /SOURCEPASSWORD $Hj45jhd@X /MOVE SQL;destPackage /DESTUSER destUserName /DESTPASSWORD !38dsFH@v

Note

To move a package from one named server to another, include the SOURCES and the DESTS option and their arguments. You can only specify servers by using the SQL option.

To move a package that is stored in the SSIS Package Store, use the following syntax:

dtutil /DTS srcPackage.dtsx /MOVE DTS;destPackage.dtsx

To move a package that is stored in the file system, use the following syntax:

dtutil /FILE c:\srcPackage.dtsx /MOVE FILE;c:\destPackage.dtsx

Sign Examples

To sign a package that is stored in a SQL Server database on a local instance of SQL Server that uses Windows Authentication, use the following syntax:

dtutil /FILE srcPackage.dtsx /SIGN FILE;destpkg.dtsx;1767832648918a9d989fdac9819873a91f919

To locate information about your certificate, use CertMgr. The hash code can be viewed in the CertMgr utility by selecting the certificate, and then clicking View to view the properties. The Details tab provides more information about the certificate. The Thumbprint property is used as the hash value, with spaces removed.

Note

The hash used in this example is not a real hash.

For more information, see the CertMgr section in Signing and Checking Code with Authenticode.

Encrypt Examples

The following sample encrypts the file-based PackageToEncrypt.dtsx to the file-based EncryptedPackage.dts using full package encryption, with a password. The password that is used for the encryption is EncPswd.

dtutil /FILE PackageToEncrypt.dtsx /ENCRYPT file;EncryptedPackage.dtsx;3;EncPswd

See Also

Other Resources

How to: Run a Package in Business Intelligence Development Studio
Integration Services Considerations on 64-bit Computers

Help and Information

Getting SQL Server 2005 Assistance

Change History

Release History

17 July 2006
Changed content:
  • Corrected name and SOURCESERVER options.
  • Noted ability to perform dtutil tasks in SQL Server Management Studio.

5 December 2005
Changed content:
  • Added description of how to sign a sample package.
  • Added example of how to encrypt a package.
  • Expanded description of how to use the copy and move options.