SQL Server 2012: SSIS Deployment Models
With SQL Server 2012, even Integration Services (SSIS) has started to support server deployment methodology similar to Analysis Services and Reporting Services. This new technique is called the project deployment model. This option will be available with the traditional deployment technique by using the Package Deployment utility. The advantage here would be to have all the configuration information related to deployed package available on the SSIS at a central location.
In earlier versions, the packages were either deployed as part of the file system or in msdb database in SQL Server. The issue here is the additional step of registering these packages to either file system or MSDB database using the Package Deployment utility. This step cannot be taken care during the package creation process in Business Intelligence Development Studio (BIDS). By default, the new SSIS project template is configured to use the project deployment model technique which can be converted to package deployment model based on preference.
The package deployment model will not support any of the new features used by project deployment model (like parameters and shared connection mangers etc.) and suitable alternatives need to be provided for these features before conversion.
When the project deployment model is used, the project will be realized as a deployment file (.ispac extension) which will contain all the configuration information related to the packages in that project. When we double click on the deployment file, it will run the Integration Services Deployment wizard to publish the packages onto the Integration Services server. The deployment can also be done using the Deploy option provided when we right click on the solution in BIDS or the newly named SQL Server Data Tools (SSDT).
As part of the deployment, the Integration Services server name and the folder where the packages need to be deployed should be provided. SSIS uses an Integration Services catalog database to store this information. The catalog database can be created by connecting to the SQL Server instance and selecting "Create Catalog" option from context menu on Integration Services folder as shown in the figure. Note that only one catalog database can be created for a specific Integration Services server.
The ispac file can be executed to deploy the packages to the SSIS server. Once done, tasks like configuring, validating and exporting packages can be performed from Management Studio. Also as part of SQL Server 2012, an Integration services dashboard is provided which presents information on executions, validations or any other operations that has taken place on the SSIS server.
The new deployment technique provides more flexibility in organizing your packages on a central server with improved management in SSMS. This comes as a nice alternative for the traditional deployment technique and should be readily accepted by the SSIS users.