Hello Everyone,

One of my customer is upgrading their entire setup from SQL 2008 R2 to SQL 2014. They have around 14 SSIS packages, which they deploy using File System for SQL 2008 R2.

I've suggested them to change the deployment process/method to SSISDB catalogue (Project deployment model) when we upgrade all SSIS packages to SQL Server 2014.

They've asked me why should they change the deployment method? What are good reasons or advantages of SSISDB catalogue?

I would like to convince them on this and would like to share the benefits of Project deployment model.

Can anybody provide any convincing material or MSDN links, which could be shared with customer, so that they could understand the process and it's robustness.

Also, is there any utility or tool like DTUtil for Project deployment model that could make the deployment super easy with SQL 2014?

Any help would be much appreciated.

The "Deploy" menu in SSDT-BI or Visual Studio is already super convenient to deploy a project.

To convince I suggest you study their operations, sometimes the catalog fits and sometimes it may not, in short, you will be more convincing by giving them examples of what will improve.

The big items are the ability to operate programmatically over the packages ans its variables, the packages in the catalog can be part of back up and the catalog allows versioning e.g. when you want to revert back to an earlier package build.

Hi Ankit,

Based on your description, you want to know the benefits of Project Deployment Model and SSISDB Catalog.

Based on my research, SSIS Catalog is a new repository model for Integration Services, which introduced from SQL Server 2012. This new repository model brings a lot of abilities for developers, testers and administrators. It includes the followings:

• Versioning: Version History will be stored at the time of project deployment, we can see list of all versions with date and time of deployment. We can also restore specific version.
• Validation: Previous versions of SSIS (2008R2 or earlier), were supported validation, but as a part in execution time. From 2012 with help of SSIS Catalog validation can be done as a separate step for packages or project.
• Execution & Logging: Logging can be enabled in the package execution GUI, and no extra work is needed during package development.
• Environments & Parameters: SSIS Catalog provided new repository for environments, where we can create multiple environments with parameters for example for test and production.
• Reports: There are 4 main type of reports gathered by SSIS Catalog with help of logging modes; Execution, Validation, Operation and Connection reports. Reports are based on Reporting Services (SSRS) and shows details of execution or validation of packages and projects.
• Permission: Permission can be applied on each folder, project, environment and operation. SQL Server roles can be selected and read, execute or other permissions can be granted or revoked for them. All permission details will be stored in tables in SSISDB catalog database.
  

Besides, to deploy a project to the Integration Services Server, we can use three methods. For more details, please refer to the following documents:
https://msdn.microsoft.com/en-IN/library/hh231102.aspx
http://sqlmag.com/sql-server-integration-services/ssis-deployment-sql-server-2012

For more details about the benefits of SSISDB Catalog, please refer to the following links:
http://www.rad.pasfu.com/index.php?/archives/73-SSIS-Catalog-Part-1-What-is-the-Catalog.html
http://stackoverflow.com/questions/27969960/sql-server-2014-ssisdb-vs-msdb-for-package-deployment

Thanks,
Katherine Xiong