SSIS Command Line Utilities part 1: dtutil

[James Beresford]

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.



Get More from the original blog...