SSIS Command Line Utilities part 1: dtutil

I am back on the study wagon, getting ready for Exam 70-448: TS: Microsoft SQL Server 2008, Business Intelligence Development and Maintenance. One topic that is key for is understanding the command line utilities that allow you to carry out various functions across all the BI technologies. Possibly in excessive depth, but no-one can accuse the MS BI certifications of being easy.

There are two that impact SSIS – dtutil and dtexec. In simple terms, dtutil moves packages, and dtexec executes them. In this post I will be focussing on dtutil. dtexec will be covered in a later post.

dtutil: deploying packages

The main use of dtutil is to script the deployment of packages. For file operations it is equivalent to a simple copy operation, though you can add SSIS specific operations, such as changing the package GUID. However it also allows for movement to and from the SQL Server msdb store, which can’t be done via copy / paste.

Deploying a package requires you specify its source location using one of the following options:

  • /Fi (or /File) + File Path + Package Name.dtsx – for packages in the File System
  • /DT (or /DTS) + Package Name – for packages in the SSIS Package Store
  • /SQ (or /SQL) + Package Name – for packages in msdb

There are a few important things to note about the above options. Firstly for File System operations, you need to specify the .dtsx extension. Secondly, for SSIS Package store operations you don’t need to, even though it is still strictly speaking a file system operation. Finally, you don’t need to know where the Package Store is – dtutil works that out for you, which is handy if you have custom setups of the package store.

For packages located in msdb, you may also need to provide connection details using the following options:

  • /SourceS (or /SourceServer) – the source server name. If not used, it assumes localhost
  • /SourceU (or /SourceUser) – the SQL Authentication user name
  • /SourceP (or /SourcePassword) – the SQL Authentication Password

Note that the username and password options are only required if SQL Authentication is being used. If left out, dtutil will try and authenticate to msdb using Windows Authentication for the current user. 

Next up, you need to tell dtutil to copy the package and specify the destination. The option to move it is /C or (/Copy). This is then followed by the same options as specifying the source – though without the preceding backslash (i.e. Fi, DT or SQ) – then a semicolon, and finally the destination path. If you are using a SQL Server destination you may also need to provide connection details with the /DestS, /DestU and /DestP options.

As usual, an example is worth its weight in gold, so heres some to help you understand. In each case I will give the Full and Abbreviated versions – both of which do exactly the same job.

Moving a package from File to SSIS Package Store:

dtutil /File C:\Package.dtsx /Copy DTS;Package

dtutil /Fi C:\Package.dtsx /C DT;Package

Moving a package from msdb on a Named Instance of SQL Server using SQL Server Authentication to the file system

dtutil /SQL Folder\Package /SourceServer SQLSERVER.INSTANCE /SourceUser Monkey_User /SourcePassword P@$$word /Copy File;C:\Package.dtsx

dtutil /SQ Folder\Package /SourceS SQLSERVER.INSTANCE /SourceU Monkey_User /SourceP P@$$word /C FI;C:\Package.dtsx

Using a little batch scripting these can easily be converted into jobs which will deploy a bundle of packages quickly and easily.

dtutil: altering packages

dtutil is more than a deployment tool – it can also alter packages using the following options:

  • /Encrypt – copies and encrypts the package with a specified password and protection level
  • /IDRegenerate – Generates a new GUID for the package
  • /Sign  – Applies a digital signature to a package

The last two can be useful as options within the deployment process, where the Encrypt can replace the Copy operation if you want the deployed environments packages to be more secure.

dtutil: file and folder operations

Finally, dtutil can work components of the file system or MSDB folder structures, creating and deleting files and folders

  • /Delete – Delete a package
  • /Exists – Test if a package exists
  • /FCreate – Create a folder
  • /FDelete – Delete a folder
  • /FDirectory – List the contents of a folder
  • /FExists – Test if a folder exists
  • /FRename – Rename a folder
  • /Move – Move a package from one location to another

Most of these would be useful in an initial deployment or for tidying up.

dtutil: other things

There are a few leftover functions which I don’t have a categorisation, so here they are.

  • /Quiet – suppress prompts
  • /Remark – add a comment
  • /Decrypt – decrypt a package as an operation is performed

Why use dtutil?

The main reason for using dtutil is simply that the BIDS supplied deployment options, and all that mucking about with Deployment Manifests, is limited, not as scriptable, and also has been buggy, having problems with configuration files (though I believe this is now fixed in 2008, but since I stopped using it long ago, I can’t be sure). dtutil also offers additional functionality, such as being able to script package operations such as encryption.

Official MSDN documentation can be found here for 2008 and here for here for 2005.

About BI Monkey

Comments

4 Responses to “SSIS Command Line Utilities part 1: dtutil”
  1. DimitryL says:

    Great post! I find this particular article and the whole web site really useful and full of practical information. Currently, I am preparing for 70-448 exam; and I would like to thank author for such brilliant done work to create and constantly update the BI Monkey site.

  2. Dewi Felstead says:

    I too have found DTUtil useful for many of the same reasons listed here. One thing that I have been keen to do is integrate SSIS deployment tasks within an MSBuild script – this is obviously possible by calling the EXEC task from within a suitable target but is limited. The main reason for needing to do this is to automate the build and deployment of both our BI Databases and ETL packages.

    I am in the process of creating a custom MSBuild ToolTask that wraps DTUtil functionality so it can be called from within an msbuild target – code can be made available if interested.

  3. BI Monkey says:

    Dewi, that sounds pretty cool. I’ve been a bit more limited in terms of what we have used msbuild for – all i’ve used it for is just wrapping up packages into an installer. I probably wouldn’t make much sense of the code but it sounds like the sort of thing that would have a home on codeplex.

    How would you use this in environments where TFS can’t talk to the target server? In one environment i’ve used it in, we have been constrained because our TFS box is in a separate domain to the test and prod boxes and there is no permitted path to communicate to those boxes. Hence using installers which are at least portable as files.

Trackbacks

Check out what others are saying about this post...
  1. […] my previous post on dtutil, I looked at the command line utility to move and alter SSIS packages. In this post I will be […]



Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!