In part 1 of this article, the package deployment model was introduced. This deployment model represents the manner in which packages were developed, configured and deployed when it was introduced in SSIS 2005 and used throughout all versions, right up to SQL Server 2012. In this version, the package deployment model was kept as a means for backwards compatibility with earlier developed packages or as a valid way of designing packages for developers who liked the strengths of this model.
This part of the article will introduce the project deployment model, the default way of designing packages and managing SSIS in SQL Server 2012 and later versions.
The Project Deployment Model
When you create a new project using SSDT-BI (the Visual Studio shell for developing BI projects), the project deployment model is the default project type. As mentioned in the previous part, using the package deployment model requires an explicit conversion.
Projects and Parameters
As the name already suggests, the project deployment is all about projects, which is its main difference from the package deployment model. In the latter, you develop packages as stand-alone objects with minimal interaction through the Execute Package Task Editor and parent configurations. A project there is a Visual Studio project and it loses all its meaning once it is deployed. You deploy packages only, nothing more, hence the name package deployment model. In the project deployment model, however, the project itself is an actual SSIS object. When you deploy, you deploy the entire SSIS project, thus its name, project deployment model. We’ll see more on deployments in a later paragraph.
One of the biggest advancements in the project deployment model is the introduction of parameters. These replace the package configurations from the package deployment model. They behave like regular SSIS variables, but they cannot change value during the runtime of the package. Parameters come in two flavors: at the project level and at the package level. Package parameters can be used only in their own package, while project parameters can be used throughout the entire project.
Project parameters can be accessed under the solution.
This opens up a new window in Visual Studio, where you can configure the parameters properties.
There are three main differences with a regular variable:
* The Sensitive field: When the project is deployed to the server, the contents of the parameter are encrypted.
* The Required field: The original value specified during design time must be replaced after the project is deployed. This can, for example, prevent the problem where a production package is updating the development environment by accident because a connection string was not changed.
* You cannot put an expression on a parameter. The value of a parameter cannot change when a package is running. SSIS parameters are very much like parameters of a function or procedure in any other programming language.
Package parameters have their own tab in the package development IDE.
Parameters can be used in expressions like regular SSIS variables. To make a clear distinction, project parameters are prefixed with $Project, package parameters with $Package, user variables with User, and system variables with System.
The Execute Package Task Editor has also been updated. To execute a package from the same project, you can choose the reference type Project Reference and choose the child package from a dropdown list. If you want to execute a package from outside the project, you can choose the “External Reference” type; however, this doesn’t play well with the project deployment model as packages are deployed to the SSIS catalog—more info on that later in the article—and not to the file system.
In the Parameters Bindings pane, you can pass values to parameters of the child package, much like a drill through action in Reporting Services. This setup allows you to easily control the execution of the child package. It is also more intuitive than using parent configurations—see part 1 of this article—as the parent passes values to the child, instead of the child retrieving values from the parent.
We’ll dive deeper into parameters and their configuration in later sections.
Another new feature in the new project deployment model is the project connection manager. This is simply a connection manager that is automatically added to every package in the project, once it is defined. This can significantly decrease maintenance on the various connections in a project. You can define a project connection manager using two methods:
You can right-click on the Connection Managers group in the solution.
When you have already a connection manager defined in a package, you can promote it to a project connection manager by right-clicking it and choosing Convert to Project Connection.
The SSIS Catalog
Another groundbreaking new feature is the SSIS Catalog. In contrast with earlier versions, where (limited) administration of folders and packages was done through the SSIS service, SQL Server 2012 provides a management interface inside the database engine.
Storage of all the metadata and the packages is moved from the MSDB database to its own dedicated database: SSISDB.
There is only one catalog available per database instance—also called SSISDB—but you can create as many subfolders as you want. The catalog is disabled by default so, after installing SQL Server, you need to enable it. It uses .NET CLR quite heavily behind the scenes, which means CLR has to be enabled in the database instance.
The following sections will explain the various aspects of the SSIS catalog.
Let’s start with getting our projects to the catalog. In the package deployment model, you have different options as to where to deploy your packages: the file system, the MSDB database or the SSIS package store. In the project deployment model, you can only deploy to the SSIS catalog. You must also deploy the project in its entirety. There is no option to deploy individual packages. This means ETL developers have to rely more on source control when developing packages. If one package is still under construction but you have to deploy another package to resolve a bug, for example, you will need to use branching used to make sure you are not deploying unfinished packages.
There are quite a few options on how to get your project into the catalog, but first of all you need to build the project. Right-click the solution in Visual Studio and choose Build.
This will create an .ispac file in the /bin/development folder of the project. Basically, this is just a container that holds all the packages of the project, the project connection managers, the project parameters, and some additional configuration info. This .ispac file is the object that needs to be deployed to the catalog. Here are your options:
Using the Integration Services Deployment Wizard. To start the wizard, either right-click a solution and choose Deploy or double-click the .ispac file in the explorer. This easy-to-follow wizard will guide you through the deployment process. You simply need to choose the server to which you want to deploy, choose the destination folder, review your selections, and you’re done.
You can also deploy a project from one server to another. Inside a folder in the catalog, right-click the Projects folder and choose Deploy Project. This will launch the same deployment wizard.
The wizard of the previous options uses an .exe file behind the scenes: ISDeploymentWizard. You can use it in a command line or in a batch file to script out a deployment.
The following article gives an overview of the different options and also provides code examples: SSIS Deployments with SQL Server 2012.
Regardless of the deployment method, every project has its protection level changed to ServerStorage, making SQL Server responsible for the protection of sensitive data inside the packages. For more information: Access Control for Sensitive Data in Packages.
As mentioned earlier, package configurations are replaced by using parameters. How does this work after a project has been deployed to the catalog?
The parameters can be configured by right-clicking a project or package in the catalog and choosing Configure…
In the resulting dialog you can review all the parameters and connection managers used in the project. For parameters, you can choose to edit the value or to use the value specified in the package itself. If a parameter has been marked as Required, a new value must be provided.
For connection managers, you can directly control a few properties through this interface:
These are all direct configurations specified manually, but this seems like a lot of work if there are a lot of projects inside the catalog. What if those projects share some common connection managers or parameters and they all need to be configured with the same value? Here environments provide the solution.
An environment is a bit like a classic configuration table from the package deployment model: It is a collection of variables with values assigned. You can link these environment variables (not to be mistaken for the environment variables of the operating system) to your connection managers’ properties or your package/project parameters. You can set up multiple environments in one single folder. When you execute or schedule a package, you can choose which environment you are going to use, allowing you to easily switch between sets of configuration values.
Let’s illustrate with an example. In the following screenshot, an environment variable ServerName with the value “localhost” was created for an environment called TestIntenseSchool.
Then a reference to the environment is added to the project in the configuration window of the project.
Next, the package connection manager is configured to use the environment variable called ServerName. Note that there isn’t any specification of which environment this variable belongs to.
When the package is executed or scheduled, an environment needs to be specified, otherwise it cannot run. The package will then execute, using the value from the environment variable.
Now another environment could be created with, for example, the server name of the production server specified instead of “localhost.” Now the same package can run in the same SSIS catalog, but targeting an entirely different environment.
For more detailed information:
The standard built-in logging providers are still present in the project deployment model, but the SSIS catalog itself also provides native logging capabilities. When packages are executed, events are logged to the catalog. The results can be monitored through several catalog views, but there are also several predefined reports and dashboards available on top of the catalog.
Here is an example of the All Executions report:
There are different logging levels available that allow you to specify which events are logged to the SSIS catalog:
Basic—The same events are logged when executing a package through the console. This is the default logging level.
Performance—Only the OnError and the OnWarning events are logged.
Verbose—Everything is logged. Use this only when you are running diagnostics.
A default logging level is specified in the catalog, but this can be changed when a package is executed or scheduled. Note that some logging levels have a significant overhead if a lot of packages are executed in the catalog.
One of the biggest advantages of the new catalog in the project deployment model is its advanced administration capabilities. First of all, there is fine-grained security to control access to folders and projects.
For more detailed information about the security options, see Managing SSIS Security with Database Roles.
Next, there is versioning of the deployed projects. When a project is deployed and it already exists in the catalog, it is not simply overwritten, but the old version is kept aside. If problems arise, you can always roll back to an older version of the project.
But the best part of all is that everything is made available through an API. Everything that has been already discussed in this article can not only be done through the GUI of Management Studio, but can also be realized with T-SQL or PowerShell. As mentioned before, you can deploy projects using stored procedures, but you can also manage security, create folders, execute packages, and so on. This gives administrators great leverage to automate about almost everything of SSIS.
For more information about the SSIS catalog and all of its configurable properties, check out the following MSDN article: SSIS Catalog.
There are a few different options for executing packages in the project deployment model:
- * As with the package deployment model, you can double-click a package and use the Execute Package Utility to execute a package.
- * DTEXEC is still alive and kicking.
- * SQL Server Agent jobs can also be used to schedule packages. There are a few new options to incorporate the project deployment model and there is an additional type of package source: SSIS Catalog. When this option is chosen, there are additional tabs to configure parameters, set the logging level, choose an environment, and so on.
- In the project deployment model, you can now also start packages using the T-SQL or PowerShell API. For examples, see Deploy and Execute SSIS Packages using Stored Procedures and SSIS Performance Testing.
This article introduced the project deployment model, a new way of working with Integration Services in SQL Server 2012 and later editions. It provides a comprehensive framework with easy-to-use parameters and environments, a flexible T-SQL and PowerShell administrative interface, and built-in logging and reporting capabilities. The following article also gives a comprehensive overview of the project deployment model and has a great comparison of the two available models in SSIS: SQL Server Integration Services 2012 – Project Deployment Model.