In 2005 and 2008, SSIS required a lot of manual configurations regarding deployment, configurations, environments, etc. It took a lot of time just to set up the environment for proper performance. Microsoft came up with a term for this, and called it the “package deployment model.” After 2012 was released, all existing deployments were referred to as this, and 2012 introduced a new concept: the “Project deployment model”. The easiest way that I thought of to remember it was like BI vs traditional SQL: set-based vs row-based. Project deployments are deployments of your entire project in SSDT, where the old package deployment model is deploying each individual package. A huge game changing advantage of SSIS in 2012 with the project deployment model is that you no longer need to install integration services as a separate instance of SQL, as it’s been baked directly into the SQL server engine, and a database within SQL now handles all of this. Another advantage of this (that’s outside of this post) is that you now get a full feature set of integration services performance monitoring straight from within management studio directly.
To use these new features, you’ll need to turn on the SSIS database in management studio and create a catalog to deploy your packages to. To start, create the database by connecting to management studio, and look towards the bottom of the object explorer for the Integration Services Catalogs section:
Create a new catalog by right-clicking and selecting “Create Catalog…” A new screen will appear that looks like the below:
In order to use this feature, CLR integration MUST be enabled on your server. Once you check that box, the rest of the screen will enable for you to edit. As of right now, you cannot rename the database, it must be called SSISDB in order for Power Shell and all the other OS features to work correctly. No word as of yet if this will be modifiable in the future. Enter a password for the encryption, and click OK. After you click OK, you now have a great centralized location for storing your packages.
I hope this post will help you if you are thinking about moving your packages to 2012 and how to go about creating the initial catalog.