SQL Server Integration Services

Known as SSIS, this is an optional part of Microsoft SQL Server that can perform a wide range of data migration tasks. It was introduced in SQL Server 2005 and was the replacement for the old DTS technology, which was similar but SSIS was a complete rewrite. It does however explain some directory and file naming.

The SSIS packages can be stored in one of three locations:

  • FILE - a UNC path is used to store the files
  • SQL - the msdb database stores the packages
  • DTS - this is the root of both the above but not the same
It should be noted that with FILE you can specify any UNC path, which is good for clustered solutions where the share should be part of the Windows Cluster configuration and hence will failover properly, although a central file store should also work. With DTS the "\File System" path is to a specific folder on the server, by default this is C:\Program Files\Microsoft SQL Server\110\DTS\Packages, this is configured in the SSIS Configuration File MsDtsSrvr.ini.xml.
  • dtutil Utility - documentation for standard utility
  • SQLPSX - SQL Library for PowerShell that can work with SSIS Packages

The dtutil command is quite simple to use. To do a simple listing use the following:

dtutil /FDi SQL;"\ssis_folder" /SourceS hostname\instance - list everything in the ssis_folder on SQL
dtutil /DestServer HOSTSERVER.GDS.LOCAL\SQLINSTANCE /file "C:\Temp\ssis\packagename.dtsx" /copy SQL;"\ssis_folder\ssis_packagename" - upload package from local file system to SQL database
dtutil /SourceServer HOSTSERVER.GDS.LOCAL\SQLINSTANCE /SQL "\ssis_folder\ssis_packagename" /delete - delete a package

On a clean install you will see "Data Collector" and "Maintenance Plans" in the SQL store.

You do not need to run dtutil in the SQL Server itself, you can run it from another server. However when you install on this "deployment" server you must install "Integration Services" along with Client Connectivity Tools and Management Tools as you might normally do. If you miss this dtutil will report an error, along the lines of "The SQL Server SSIS Package Utilities require Integration Services to be installed...", in which case you just need to run setup again and select "Integration Services". It is also important to note that dtutil will parse the SSIS package, so if you are using a third party SSIS Connector, or one that is not bundled, like the Attunity Connector for example, then you will need to install this on your "Deployment Server" otherwise you will get strange errors when dtutil tries to parse your package.