SQL Server 2012 Integration Services introduces an innovative approach to deploying SSIS projects, known as Project Deployment Model. This is the default and recommended deployment technique, due to a number of benefits it delivers (such as, the ability to centralize management of package properties across deployed projects, as well as monitor and log package execution performance and progress). However, even though the new model has become the recommended and default configuration for new packages created using SQL Server Data Tools, the traditional, package-based methodology remains available and supported. More importantly, in some scenarios, it might be considered a more viable option, since it allows for separation of SQL Server Database Engine and SQL Server Integration Services roles, yielding performance benefits and facilitating a distributed extraction, transformation, and loading (ETL) activities. Project Deployment Model, on the other hand, requires SSIS to be collocated on a SQL Server 2012 instance, due to its dependency on SSIS catalog. We will discuss its characteristics and walk through its implementation in this post.
Reporting Services 2008 for Internet deployment
The deployment method available from a SQL Server Data Tools-based project is directly dependent on the model employed during its creation (it is also possible to switch between two models after a project is created by applying a conversion process, which is invoked from the Project menu in SQL Server Data Tools). Effectively, in order to use the traditional package deployment mechanism, you will need to first ensure that your project is based on the package deployment model, which you can easily identify by checking its label in the Solution Explorer window.
In addition, you will also have to modify default project properties. To access them, right-click on the node representing the project in the Solution Explorer window and select the Properties entry from its context sensitive menu. In the resulting dialog box, switch to the Deployment section, where you will find three settings displayed in the grid on the right hand side:
AllowConfigurationChanges – a Boolean value (i.e. True, which is the default, or False) that determines whether it will be possible to choose package configurations during its deployment and assign values of properties or variables they reference.
CreateDeploymentUtility – a Boolean value (True or False, which is the default) that indicates whether initiating the project build will result in the creation of its Deployment Utility.
DeploymentOutputPath – a path that points to the file system folder where the Deployment Utility will be created. The path is relative to the location where project files reside (and set, by default, to bin\Deployment).
Set the value of the CreateDeploymentUtility property to True and modify AllowConfigurationChanges according to your requirements (e.g. set it to False if your project does not contain any package configurations or you want to prevent their modifications during deployment). Next, start the build process (using the Build item in the top level main menu of SQL Server Data Tools), which will populate the output folder (designated by DeploymentOutputPath parameter) with the .dtsx package file (or files, depending on the number of packages in your project), an XML-formatted Deployment Manifest file (whose name is constructed by concatenating the project name and .SSISDeploymentManifestsuffix) and, potentially, a number of other, project related files (representing, for example, custom components or package configurations).
The subsequent step in the deployment process involves copying the entire content of the Deployment Output folder to a target server and double-clicking on the Deployment Manifest file at its destination. This action will automatically launch the Package Installation Wizard. After its first, informational page, you will be prompted to choose between File system and SQL Server deployments.
The first of these options creates an XML-formatted file (with extension .dtsx) in an arbitrarily chosen folder. If the project contains configuration files (and the AllowConfigurationChanges project property was set to True when you generated the build), then you will be given an option to modify values of properties included in their content. At the end of this procedure, the corresponding .dtsConfigfiles will be added to the target folder.
The second option, labeled SQL Server deployment in the Package Installation Wizard relies on a SQL Server instance as the package store. Once you select it, you will be prompted for the name of the server hosting the SQL Server Database Engine, an appropriate authentication method, and a path where the package should be stored. If you want to organize your packages into a custom folder hierarchy, you will need to pre-create it by connecting to the Integration Services component using SQL Server Management Studio. In case your package contains additional files (such as package configurations), you will also be given an opportunity to designate their location (by default, the wizard points to Program Files\Microsoft SQL Server\110\DTS\Packagesfolder).
In either case, you can decide whether you want to validate packages following their installation (which will append the Packages Validation page to the Package Installation Wizard, allowing you to identify any issues encountered during deployment). In addition, when using SQL Server deployment, you have an option to set a package protection level (resulting in assignment of ServerStorage value to the ProtectionLevel package property). When deploying to file system, this capability is not available, forcing you to resort (depending on the deployment target) to either NTFS permissions or SQL Server database roles for securing access to your packages and sensitive information they might contain.
Just as in earlier versions of SQL Server, local SQL Service Integration Services 11.0 instance (implemented as the MSDTSServer110 service) offers the ability to manage packages stored in the MSDB database and file system, providing you with additional benefits (such as monitoring functionality), which we will discuss in more detail in our upcoming articles. In the case of MSDB storage, this is accomplished by following the SQL Server deployment process we just described and is reflected by entries appearing under the MSDB subfolder of the Stored Packages folder of Integration Services node when viewed in SQL Server 2012 Management Studio. In the same Storage Packages folder, you will also find the File System subfolder containing file system-based packages that have been identified by the SSIS service in the local file system. By default, the service automatically enumerates packages located in Program Files\Microsoft SQL Server\110\DTS\Packages directory, however, it is possible to alter this location by editing the Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.ini.xml file and modifying the content of its StoragePath XML element. Incidentally, the same file controls other characteristics of MSDTSServer110 service, such as, package execution behavior in scenarios where the service fails or stops (by default, the execution is halted) or location of target SSIS instances (defined using the ServerName XMLelement).
While the Package Installation Wizard is straightforward to use, it is not well suited for deploying multiple packages. This shortcoming can be remedied by taking advantage of the DTUtil.exe command line utility, which in addition to its versatility (including support for package deployment and encryption), can also be conveniently incorporated into batch files. Its syntax takes the form DTUtil /option [value] [/option [value]] …, pairing option names with the values associated with them. For example, /SQL, /FILE, or /DTS options designating the package storage type (SSIS Package Store, File System, and MSDB database, respectively) can be combined with a value that specifies package location (as a relative or absolute path). By including COPY, MOVE, DELETE, or EXISTS options (with a value identifying package location) you can effectively copy, move, delete, or verify the existence of packages across all package stores.
In conclusion, the traditional package deployment methods available in earlier versions of SQL Server Integration Services are still supported and fully functional in the current release. However, in most scenarios, you should consider migrating your environment to the latest Project Deployment Model due to a wide range of advantages it delivers.