SQL Server Integration Services 2012 – Project Deployment Model
SQL Server Integration Services (SSIS) is the popular ETL (Extraction, Transformation & Loading) solution of the Microsoft BI Suite. With the introduction of SQL Server 2005, Microsoft completely reshaped the way data loads could be designed in SQL Server. Where previous releases used Data Transformation Services (DTS), which mainly could only perform Extract and Load tasks, Integration Services offered full Extract, Transform and Loading functionality.
With the release of SQL Server 2012, Microsoft now introduces the Project Deployment Model (PDM) in Integration Services, shifting the way SSIS solutions can be managed and deployed. This new model doesn’t mean that you will need to upgrade your existing packages and solutions, because the "old” model, called "Package Deployment Model” in SQL Server 2012, still will be available.
But the new Project Deployment Model goes further than just the way your packages and solutions are deployed. The new model also contains many new and interesting features (like simplifying interactions between SSIS Packages), which will be described in this Insight.
In SQL Server Integration Services 2012, you can not only migrate your existing Package Deployment solutions to a new Project Deployment solution, you can also migrate in the other direction. A PDM can thus be converted (back) to Package Deployment.
Main concepts and features
In order to understand how the new Project Deployment Model works, we will describe several important concepts separately. This will give you a step-by-step overview on how the new model works and what steps you need to take to produce a working project.
The new deployment model makes use of the new SSIS catalog. This is a service inside the database engine of SQL Server 2012. It is a central storage and administration point for the SSIS solutions of the PDM.
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.
click to enlarge
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.
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 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
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.
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
click to enlarge
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.
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:
Project Deployment Model
Package Deployment Model
Unit of deployment: Project
Unit of deployment: Package
Environment variables are used to store environment specific information.
Configuration files are used to store environment specific information.
Package properties are stored in parameters.
Package properties are stored in configuration files.
CLR integration required.
CLR integration not required.
A project, with its corresponding packages and parameters, is deployed to the SSISDB catalog.
Packages and configurations are stored on the file system. Packages can be saved in the MSDB database.
Packages are executed by starting an execution on the database engine.
dtExec can also be used for execution.
Packages are executed by thedtExecandDTExecUIutilities.
Projects and packages can be validated before execution. They can also be validated with SSMS (Management Studio), stored procedures, or managed code.
Packages are validated at the start of execution. The can also be validated with dtExec or managed code.
Events produced during execution are captured automatically and saved to the catalog. They can be queried with T-SQL views.
Events produced by a package are not captured automatically. Logging must be added manually for each package.
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.
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.
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.