Deployment of SSIS packages

Question 1

What type of file is used in for deployments in SQL Server 2012 and what information does this contain?

Answer 1

Deployment is performed via a single ispac file, which contains all packages and configuration variables (Project/Package Parameters, Environments, Environment variables).

Question 2

How is SSIS package and deployment information stored on SQLServer 2012?

Answer 2

Integration Services Catalog - Think of this catalog as the container for all deployed projects, their settings and historical versions. Behind this is a database called SSISDB

Question 3

How can a single SSIS 2012 package be used in multiple environments

Answer 3

1) Each project within the catalog can have multiple environments associated with it.
2) Each environment can then have its own set of values to a common set of variables.
3) The connection managers for a project can also be configured once deployed.
3) When a job calls a package it can then specify which environment it will use and also can have connections managers configured.

Question 4

Provide 5 places that variable values can be set within a 2012 SSIS package

Answer 4

1) Package Parameters
2) Project Parameters
3) Configured project within Catalog
4) set within Catalog Environment
5) Set within the SQL Server Job

Question 5

Do all packages need to be deployed when using the project deployment model

Answer 5

1) Yes, unless you are using SQL Server 2016 which allows for incremental deployments
2) It is still possible to use the legacy deployment model

Question 6

Is it possible to automate environment creation

Answer 6

1) Yes you can script out environments and variables at creation. Once they are created this is lost
2) Yes you can write your own scripts or use 3rd party tools

Question 7

List the steps of creating and using environments to set variables

Answer 7

1) Create a project parameter e.g. DatabaseName
2) Deploy project to catalog
3) Create environment in catalog project and create variable e.g. DatabaseName
4) Add reference to environment in project
5) Configure parameter to use environment variable
6) Select environment when creating job or executing project.