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.