Programming4us
         
 
 
SQL Server

SQL Server Integration Services : The Package Execution Utility (part 3) - The dtutil Utility

11/30/2010 3:05:49 PM

The dtutil Utility

You use the dtutil command-line utility to copy, move, delete, or verify the existence of a package. These actions can be performed on any SSIS package, regardless of whether it is stored in a Microsoft SQL Server database, the SSIS package store, or at the filesystem.

The dtutil options are additive. Depending on what you are trying to do, you string one or more options and their values together in the following form:

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

To show available options for dtutil, you use '/?' or '/H' or '/Help', as follows:.

c:> dtutil /?

Note that a dash (-) may be substituted for / in this command.

Alternatively, you can see the details for a particular option by using the available options indicator followed by the option name ('/?' [option name]). This invokes SQL Server Books online for that particular option.

The dtutil options include the following:

  • /~C[opy][StorageLocation];[PackageName]— This option identifies where the package is to be stored (StorageLocation value of DTS, FILE, or SQL) and the full destination path and filename of the package (PackageName). When the Copy action encounters an existing package at the destination, dtutil prompts you to confirm package deletion. Y overwrites the package, and N aborts the overwrite of the destination package. If you include the /Q (quiet) option, no prompt appears, and the existing destination package is overwritten.

  • /~Dec[rypt] Password— This option sets the decryption password used when loading a package with password encryption.

  • /~Del[ete]— This option deletes the package specified by the SQL, DTS, or FILE option.

  • /~DestP[assword] Password— This option specifies the password used with the SQL option to connect to a destination SQL Server instance using SQL Server authentication.

  • /~DestS[erver] Server— This option specifies the server name used with any action that causes a destination to be saved to SQL Server or a nonlocal or nondefault server when saving an SSIS package.

  • /~DestU[ser] User name— This option specifies the SQL Server username at the destination SQL Server instance.

  • /~DT[S] PackagePath— This option specifies that the SSIS package referenced is located in the SSIS package store, and the PackagePath argument is a relative path that commences at the root of the SSIS package store.

  • /~En[crypt] [StorageLocation];[;Path;ProtectionLevel[;Password]— This option encrypts the loaded package with the specified protection level and password and saves it to the location specified in Path. StorageLocation types are DTS, FILE, and SQL. ProtectionLevel determines whether a password is required.

    The possible ProtectionLevel values are 0 (strips sensitive information), 1 (sensitive information is encrypted using local user credentials), 2 (sensitive information is encrypted using the required password), 3 (package is encrypted using the required password), 4 (package is encrypted using local user credentials), and 5 (package uses SQL Server storage encryption).

  • /~Ex[ists]— This option is used to determine whether a package exists.

  • /~FC[reate] [StorageLocation];FolderPath;NewFolderName— This option creates a new folder with the name specified by NewFolderName. StorageLocation is SQL or DTS only. The location of the new folder is indicated by FolderPath.

  • /~FDe[lete] [StorageLocation] ExistingFolderPath;ExistingFolderName— This option deletes the folder specified by the name in ExistingFolderName from SQL Server (SQL) or SSIS (DTS). StorageLocation is SQL or DTS only. The location of the folder to delete is indicated by ExistingFolderPath.

  • /~FDi[rectory] [StorageLocation] FolderPath[;S]]— This option lists the contents, both folders and packages, in a folder on SSIS (DTS) or SQL Server (SQL). The optional ExistingFolderPath parameter specifies the folder whose contents you want to view. StorageLocation is SQL or DTS only. The optional SExistingFolderPath. parameter specifies that you want to view a listing of the contents of the subfolders for the folder specified in

  • /~FE[xists] [StorageLocation] ExistingFolderPath— This option verifies whether the specified folder exists on SSIS (DTS) or SQL Server (SQL). The ExistingFolderPath parameter is the path and name of the folder for which you need to verify its existence. StorageLocation is SQL or DTS only.

  • /~FR[ename] [StorageLocation]; ExistingFolderPath; ExistingFolderName; NewFolderName— This option renames a folder on the SSIS (DTS) or SQL Server (SQL). StorageLocation is SQL or DTS only. ExistingFolderPath is the location (path) of the folder to rename. ExistingFolderName is the name of the folder to be renamed, and NewFolderName is the new name to give the folder.

  • /~Fi≤ PathName— This option specifies that the SSIS package to be operated on is located in the filesystem, and the PathName value contains either a universal naming convention (UNC) path or local path.

  • /~I[DRegenerate]— This option creates a new GUID for the package and updates the package ID property.

  • /~M[ove] [StorageLocation]; PathandName— This option specifies a move action for an SSIS package. StorageLocation may be DTS, FILE, or SQL. PathandName indicates the package path (location) and/or package name: SQL uses the package path and package name, FILE uses a UNC or local path, and DTS uses a location relative to the root of the SSIS package store. If an existing package at the destination has the same name, dtutil prompts you to answer Y to overwrite this existing package or N to not do the move. If you specify the /Q (quiet) option, no prompt appears when an existing package may exist at the move destination, and it is just overwritten.

  • /~Q[uiet]— This option disables the Y/N prompts when a package with the same name as the specified package already exists at the destination location or if the specified package is already signed.

  • /~R[emark] [Text]— This option is a comment to the command line. There can be multiple remarks in a command line.

  • /~Si[gn] [StorageLocation]; ExistingPath; Hash— This option signs an SSIS package. StorageLocation may be DTS, FILE, or SQL. ExistingPath specifies the path (location) of the package to be signed. Hash specifies a certificate identifier expressed as a hexadecimal string of varying length.

  • /~SourceP[assword] Password— This option provides the password used with the SQL and SOURCEUSER options to connect to a SQL Server instance that uses SQL Server authentication.

  • /~SourceS[erver] Server— This option provides the name of the server where the package is to be stored.

  • /~SourceU[ser] User Name— This option provides the SQL Server username to use to access the SSIS package.

  • /~SQ[L] PathName— This option specifies the path (location) of the SSIS package stored in the msdb database.

Next, let’s look at various examples of running dtutil.

dtutil Examples

The following example copies an existing package in SQL to the SSIS package store:

C:> Dtutil /SQL ExistingPackage /COPY DTS;destPackage

The following example copies an existing package from one location on the filesystem to another location on the filesystem:

C:> dtutil /FILE c:\Unleashed\HotCustomersPlus.dtsx /COPY
FILE;c:\UnleashedProduction\HotCustomersPlus.dtsx

The following example creates a new GUID (usually after you copy a package):

C:> dtutil /I /FILE HotCustomersPlus.dtsx

The following example deletes a package stored in the local server (msdb database):

C:> dtutil /SQL HotCustomersPlus /SOURCEUSER PBertucci
/SOURCEPASSWORD xyz /DELETE

The following example deletes a package stored in the filesystem:

c:> dtutil /FILE c:\UnleashedProduction\HotCustomersPlus.dtsx /DELETE

The following example verifies whether a package exists in a local server (msdb database):

C:> dtutil SQL HotCustomersPlus /SOURCEUSER Pbertucci /SOURCEPASSWORD xyz /EXISTS



The following example verifies whether a package exists on the local filesystem:

C:> dtutil /FILE c:\UnleashedProduction\HotCustomersPlus.dtsx /EXISTS

The following example moves a package from one server (msdb database) to another server (msdb database):

C:> dtutil /SQL HotCustomersPlus /SOURCEUSER Pbertucci
/SOURCEPASSWORD xyz /MOVE SQL;HotCustomersPlus
/DESTUSER sa /DESTPASSWORD zwx

The following example moves a package from one filesystem location to another:

c:> dtutil /FILE c:\Unleashed\HotCustomersPlus.dtsx /MOVE
FILE;c:\UnleashedProduction\HotCustomersPlus.dtsx

The following example signs a package on the filesystem:

dtutil /FILE c:\Unleashed\HotCustomersPlus.dtsx /SIGN FILE;
c:\Unleashed\HotCustomersPlus.dtsx;987377773999af33df399999333

Other -----------------
- SQL Server Integration Services : The SSIS Designer
- SQL Server Integration Services : Running the SSIS Wizard
- SQL Server Integration Services : A Data Transformation Requirement
- SQL Server 2008 : SSIS Tools and Utilities
- SQL Server 2008 : SSIS Architecture and Concepts
- SQL Server 2008 : SQL Server Integration Services - SSIS Basics
- Defensive Error Handling : Using Transactions and XACT_ABORT to Handle Errors
- Managing Security Within the Database Engine : Securables
- Managing Security Within the Database Engine : Database Security
- Managing Security Within the Database Engine : Creating SQL Server Principals
- SQL Server 2008 : Performance Tuning - Locks, Blocking, and Deadlocks
- SQL Server 2008 : Performance Tuning - Tracing
- SQL Server 2008 : Implementing Error Handling - Managing and Raising User-Defined Errors
- SQL Server 2008 : Implementing Error Handling - Understanding Errors
- Implementing SQL Server Objects Using Managed Code (part 2)
- Implementing SQL Server Objects Using Managed Code (part 1)
- Encryption Catalog Views
- Built-In Cryptographic Functions
- SQL server 2008 : Managing Security - Permissions
- SQL server 2008 : Managing Security - Schemas
 
 
Most View
- Windows Server 2008 : Managing Terminal Services User Connections
- A Brief History of Legacy .NET Distributed Technologies : .NET Remoting
- SharePoint 2010 : Modify the Top or Left Navigation Bar (part 1)
- Windows Azure : Understanding Message Operations
- Windows 7 : Removing an Icon from Control Panel
- The Art of SEO : Trending, Seasonality, and Seasonal Fluctuations in Keyword Demand
- SharePoint 2010 : Organizing Information - Building an Information Architecture
- Adding iPad to the Mix
- Developing an SEO-Friendly Website : Creating an Optimal Information Architecture (part 1)
- Auditing an Existing Site to Identify SEO Problems (part 3) - Fixing an Internal Linking Problem
Top 10
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 3) - Configuring Recipient Filtering
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 2)
- Implementing Edge Services for an Exchange Server 2007 Environment : Utilizing the Basic Sender and Recipient Connection Filters (part 1)
- Implementing Edge Services for an Exchange Server 2007 Environment : Installing and Configuring the Edge Transport Server Components
- What's New in SharePoint 2013 (part 7) - BCS
- What's New in SharePoint 2013 (part 6) - SEARCH
- What's New in SharePoint 2013 (part 6) - WEB CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 5) - ENTERPRISE CONTENT MANAGEMENT
- What's New in SharePoint 2013 (part 4) - WORKFLOWS
- What's New in SharePoint 2013 (part 3) - REMOTE EVENTS