SQL Server Integration Services 2012 – Project Deployment Model

click to enlarge

By default it is not created, so this should be your first step before you can deploy your PDM solutions. After you created the catalog you will notice 2 items with the same name. One is a database created in the database engine. This database holds all the information of the SSIS Catalog, but also stored procedures giving you the means to manage and run packages and projects stored in your SSIS Catalog. These stored procedures are used behind the scenes when using the SSIS Catalog, but more advanced developers can also call them manually if they want to integrate them in a specific solution.

The other one is the SSISDB item under Integration Services Catalogs node, which is your management interface on that SSISDB database. Here, you can create folders to manage your solutions. When you create a folder, it will automatically contain 2 subfolders "Projects” and "Environments”. These 2 concepts will be described in more detail later on.

Also the concept of automatic versioning has been introduced in the SSIS Catalog, making it possible to reset a package to a previously deployed version. If you deploy a new version of your project that afterwards appears to contain errors, you can easily roll back to a previous deployed version of that project.

Projects

In previous versions of SQL Server Integration Services, there was absolutely no notion of a project. You created a solution/project in Business Intelligence Development Studio (BIDS), which held a set of packages. So during development, everything was grouped as a project. But after the packages are deployed to the server, the notion of a project is gone and there is no way to easily share common variables between these packages.

Now with the PDM in SQL Server 2012 the concept of project also exist on the server side. You have single deployment, where you deploy an entire project to the SSIS Catalog. This also means that it is possible to have internal references between packages. For instance with a parent-child package relation, you will not need to create configurations anymore.

Figure 3: Project structure of Project Deployment Model in Data Tools

Project.params opens up a "variable-like” window. Here you can define project parameters that will be available for every package in the project.

The Shared Connection Manager-node replaces the "shared data sources” from previous releases, which weren’t very useful, because they were only useful during development. Again, after deployment the concept was gone. Shared Connection Manager deals with this problem, because connections now can remain persistent after deployment. At Server level, they are still shared between all the packages in a project. A Shared Connection Manager can be converted to be just a Package Connection manager and vice versa.

The SSIS Packages node is the same as earlier, containing all the packages you create or import to your project.

Parameters

Parameters exist on 2 levels, the project level and the package level. Unlike variables, which still exist in SSIS 2012, parameters are read-only, so they cannot be changed at run-time. Except for the fact that parameters are read-only, they can be used just the same as a variable.

During development the project parameters are managed in de Project.params file. Here you can configure project parameters with their corresponding attributes like Name, Data type, Value and Description. There are also 2 new attributes.

• Sensitive determines whether you want the information to be encrypted or not.
• Required on the other hand will indicate whether you want it to be obligated or not. If set to true, you must define a value for the package to run. It will not use the design-time values if you don’t define a value at run-time.

Figure 4: Project parameters in the Project.params file

The Package parameters can be found in the Parameter tab in every package and the interface is the same as that of the Project parameters.

Figure 5: Package parameters in the Parameter tab of the package

Deployment

In earlier releases hitting the Deploy-button resulted in the creation of a deployment utility, which you then had to startup separately. In de Project Deployment Model, the Deploy-button immediately launches a deployment wizard.

Figure 6: Package parameters in the Parameter tab of the package

In this wizard, you can choose whether you want to deploy the current project you are working on, or if you want to import an existing project from another server if, for example, you want to move your project from development to production.
After this, you choose the server where you want to deploy to and the location (Folder) in the SSIS Catalog. As simple as that!
One thing you should bear in mind though is that you can only deploy your entire project.

Environments

An environment is used to define values for a set of variables for a certain scenario. As the name states, you can do this to run your packages in different physical environments, for instance Development, Test and Production, and define variables like connection string, server name, etc. But this is not necessarily why you should use this. It can also be used to set different filters for different business areas, so your environments may be something like Sales, Marketing, Finance, etc.

Figure 7: Different Environments in your project

Under the tab Variables of you Environment Properties, we can define multiple variables the same way we do this in BIDS. These variables can, later on, be linked with the Project Parameters.

Figure 8: Setting an Environment Variable

Linking Environment Variables with Project Parameters makes the execution of your packages dynamic. This can be accomplished via the Configure button when right clicking on your project. In this example, you can see that we link our previously created environment variable "Databasename” (see Figure 8) to our Project Parameter "DatabaseName” (see Figure 4).

Figure 9: Linking Environment Variables with Project Parameters

Running, Logging and monitoring

Now that everything is set, we can run our package. Simply right click you package and hit Execute. This will open up the Execute Package window. If we defined Project Parameters with the required option set to True, you will get a warning because no value is set. As stated before, you will not be able to run the package at this point. The OK button is greyed out.

Figure 10:
Package Execution window before configuration

We can choose to manually enter a value or we can choose to select the settings of the earlier defined environments. This will automatically set all the linked parameters to the corresponding values of that environment. After configuring the necessary parameters and connection managers, the OK button will become available and you can execute your package.

Figure 11: Package Execution window after configuration

Another difference in the Project Deployment Model is the fact that you won’t get an execution overview window like you did in earlier releases. Instead, you get a message stating that execution has started and that you can track execution in the Integration Services Dashboard report. You can choose to go directly to the report of you package.

Figure 12: Package Execution window after configuration

The SSIS Catalog contains 3 out-of-the-box integrated reports that you can use to view general execution information, analyze performance, and track logging messages of the execution. All this information is stored in the SSISDB database, so you can query it at any time. This also means there is no need to set execution logging manually in every package -like we were used to in earlier releases of SQL Server.

Project Deployment vs. Package Deployment

Now that we have 2 types of deployment models, we will have to make some considerations before choosing one type over the other. Both types not only have conceptual and structural difference, but there will also be differences in development and administrative options becoming available in function of the choice made.

In the following table, we listed some of the major differences between the 2 types:

Table 1: Comparison between Project Deployment and Package Deployment

We recommend using the new Project Deployment Model if you are using SQL Server 2012 and don’t have to migrate existing solutions. If you already have existing solutions, build in previous releases, you can choose to upgrade to the new PDM. Bear in mind though, that for complex packages it might be more interesting to re-develop them, to benefit from the new features in SSIS.

Migrating solutions

An easy to use wizard to migrate your solutions from Package to Project Deployment can be found by right clicking on your project and selecting "Convert to Project Deployment Model”. This wizard will try to convert existing package configurations to parameters. If a configuration is not found for some reason (maybe the file connection couldn’t be made) you can add it manually.

You can also convert in the other direction. Project Deployment solutions can be converted to Package Deployment solutions. There is one restriction though. As long as you didn’t use any of the new features, you can migrate to Package Deployment.

Conclusion

In this insight, we have explained the main features of the new Project Deployment Model and guided you on how to build a working project. We also reviewed the differences with the previous deployment model, to make clear that using (or upgrading to) the new PDM holds a lot of advantages.

Your packages and solutions will become more manageable and logging and monitoring is much more simplified by the default automated logging. It speaks for itself that this will have a positive impact on development productivity.

The addition of parameters is a good step in the right direction, but the future will make it clear if making them read-only was a good decision or not. In some cases it will suffice, but in other cases parameters might not help you at all.

All this and the possibility to migrate you current solutions in an easy and straightforward manner, gives you more than enough arguments to start making use of the new Project Deployment Model.