• Refer to the link: https://www.mssqltips.com/sqlservertip/2450/ssis-project-deployment-model-in-sql-server-2012-part-1-of-2/

    MNDN doesn't really have pros and cons.

    I recently upgraded my company from SSIS SQL 2008 R2 to SQL Server 2014 and we went from package deployment to project deployment. Here are the Pros I think are BIG:

    - Automatic Logging: You don't need to worry about logging anything. A execution history is set for you.

    - Encryption: Everything is encrypted automatically including password. It's a big security risk storing passwords in plain text on SQL 2008

    - Versioning: By Default, upto 10 versions of your project are saved on the server, and you can rolllback to any version with a click. I have mine set to Last 20 versions. I did need to rollback one time after deployment, and it was so so so easy.

    - Reporting: You can setup Queries/Reports to query SSISDB for package executions and system health. I have a SSRS report that gets mailed to me every to looks at overall system health.

    CON: Manaing SSIDB. It can grow pretty quickly depending on how many times you execute package. We are a really heavy SSIS shop, and our SSIDB is upto 40GB now. We retain our history for 30 days.

    ------------
    🙂