SSIS 2012 Projects: Deployment Configurations and Monitoring

The Project Deployment Model introduced in SSIS 2012, which was explained in the first part of this series, speeds up the deployment of database projects in which there may be hundreds of SSIS packages per project. Not only that, but deployments can be configured differently for each environments such as test and staging, and there are now ways of monitoring the status and performance of packages and of versioning the SSIS Catalog.

In my first article on Project Deployment in SSIS 2012 ‘Setup, Project Creation and Deployment’, I described the basics of the project deployment model for SSIS. I then showed how to deploy a project to a SQL Server instance.

This article aims to go into more detail about the way of configuring the deployed projects to fit different needs and different environments. It will then go on to describe the ways of monitoring and versioning the SSIS Catalog.

There are two general options for SSIS environments setup:

• The SSIS projects being deployed to a server which handles SSIS operations and runs the packages towards different environments (TEST and PROD, for example)
• Or, the SSIS projects can be deployed multiple times – once on every environment (TEST and PROD)

Configuring SSIS projects hosted on the same server for multiple environments

In the previous article, we created a project with an SSIS package. This project had a Project parameter calledDatabaseName. The idea behind it was to be able to easily manage the different names of the databases, based on environment (let’s suppose that the database in the TEST environment is called ProjectDeployment_Test and the one in the PROD environment is called ProjectDeployment_Prod).

To achieve our goal in this case, the Project Deployment model provides us with the means of creating Environments – which is a collection of specific settings that map to the project variables.

To create an Environment, we have to connect to our SQL Server instance via SQL Server Management Studio (SSMS) and right-click on the ‘Environments’ folder in our deployed project:

After creating the two environments (Test and Prod), our project will look like this:

Now we must create a variable in each environment that has the same name as our Project Variable. We double-click each Environment and, in the ‘Variables’ tab, we enter the Variable name and the value for it. Keep in mind that the Variable name is the same, but the value for it will differ, depending on the environment.

Here is how the Test environment will look:

And here is how the Prod environment will look:

Now we have to map the environment variables to our project. Right-click on the deployed project and select ‘Configure…’

Then, in the ‘References’ tab, add both the Test and Prod environments. After adding them, the screen will look like this:

Then go to the ‘Parameters’ tab in the same wizard and add the Parameter value:

In the end, our screen will look like this:

Now that we have set up the Environments and mapped them to the project, it is time to execute the SSIS package.

Right-click on the SSIS package and click ‘Execute’. Select the appropriate environment and click ‘OK’.

This execution is just a single test execution. However, the dialog and configuration is very similar when we schedule SSIS packages via the SQL Server Agent.

In this case, when we have multiple environments configured on the same server, we have to make sure that every time we schedule a package for execution, we have configured the right Environment. I.e. in each SQL Server Agent job we need to make sure that the configuration is correct according to the destination (Test or Prod).

When we make changes to our BIDS project and when we re-deploy, we do not need to worry about any configurations – the SSIS packages in the project are deployed, but the Environments and the configurations are kept as-is (unless we change the names of the project variables).

Configuring SSIS projects hosted on multiple environments

If the SSIS projects are deployed to more than one server, the configuration is a bit different.

Let’s say we have a server called Test and a server called Prod, and we have to deploy the same project to both environments and configure it accordingly.

We could, of course, use the same technique as above, where we deploy our project to each server, and then set up one Environment in each server and point the project variable to it.

We don’t really need to do this, though. Instead, we can just deploy the projects to each server, and then right-click on the project name in the SQL Server instance and click ‘Configure …’:

Then we just need to enter the name of the project parameter:

When we deploy next time, the settings will remain the same as we have configured them in every server, even though in our project the value for the project variable is different.

Monitoring of SSIS Packages in the SSIS Catalog

First of all, when we run a manual test execution of a package in the SSIS Catalog, we are immediately asked whether we would like to navigate to the monitoring page in SSMS:

If we click ‘Yes’, we are automatically taken to the SSMS Report which shows the execution status of the current package.

It looks similar to this:

If we wanted to see what SSIS packages are currently executing on our instance, we would right-click on the SSISDB catalog and select ‘Active Operations’.

The dialog would look similar to this:

Unfortunately we do not have the option to click on the currently executing package and go directly to the execution statistics report from this tab.

In order for us to see the package execution statistics and the current execution status, we have to navigate to the reports. One way to do this is to right-click on the SSISDB catalog and select the ‘Integration Services Dashboard’report.

This report shows an overview of all current and past executions:

As we can immediately notice, there is a problem with our package execution. In this case, the failure is due to a validation error in my project. I have forgotten to specify the connection for one of my task components.

This is a great learning opportunity: keep in mind that, in the project deployment model, there may be validation errors and the project can still be deployed even with them. If this happens, the execution of all packages in the project will fail on the server, even though the error is in one of the packages. As I mentioned previously, there is no way to deploy only a single package; we have to deploy the entire project every time we make changes. This is why we have to be extra careful when we deploy with the project deployment.

Performance monitoring

Now that we have our project working successfully and we can execute packages, we are concerned with the performance of our packages.

It is very easy to monitor the performance of an SSIS package on our server. All we have to do is click on the ‘View Performance’ link in the Package Overview report.

Then we will see the performance report, which looks similar to this:

As we can see, there has been some improvement in run duration of the package.

Conclusion

In this article I’ve described how to deploy SSIS projects with the project deployment model for use in different environments by means of configuration. I have then described the ways of monitoring and versioning of the SSIS Catalog. The management is almost effortless, since the metadata and SSIS project versions are stored in the SSISDB database within the SQL Server instance.

Reference: https://www.simple-talk.com/sql/ssis/ssis-2012-projects-deployment-configurations-and-monitoring/

SSIS 2012 Projects: Setup, Project Creation and Deployment

It used to be that SQL Server Integration Services (SSIS)  packages had to be deployed individually. Now, they can be all deployed together from a single file by means of the Project Deployment Model introduced in SSIS 2012. Where there are tens or even hundreds of SSIS packages to deploy, this system is essential.  Feodor Georgiev talks us through the basics in the first of a two-part series.

Starting with SQL Server 2012 it is possible to use the newly-introduced Project Deployment model for SQL Server Integration Services. The new model promises to provide deployment flexibility, improved configuration options and easier monitoring and maintenance.

Up until SQL 2012 the SSIS deployment was package-based, i.e. each SSIS package had to be deployed separately, and the deployed packages could be stored either on the file system or in a system database repository. The new Project Deployment model uses its own designated database (called SSISDB) and the deployment is performed via a single ispac file, which contains all packages and configuration variables (Project/Package Parameters, Environments, Environment variables).

This article covers the process of deploying the project, including the setting-up of the SSIS Catalog and creating the project. In the next article, 'SSIS 2012 Projects: Deployment Configurations and Monitoring' I cover the configuration and management of environment variables, and explain how to monitor individual packages.

Setting up the Integration Services Catalog

Before we can deploy any projects to a SQL Server instance, we first need to create the Integration Services Catalog. Think of this catalog as the container for all deployed projects, their settings and historical versions.

There is only one Catalog per SQL Server instance; it is represented by a separate SQL Server database called SSISDB, which contains the deployment’s versioning, settings, and even statistical performance data. It uses SQL Server encryption; This is why we are asked, during the catalog creation, to provide a password, which will be used to create the database master key which will be used to encrypt sensitive data.

In order to use the Project Deployment model, SQLCLR needs to be enabled on the SQL Server instance.  Here is a script which enables the SQLCLR:

sp_configure 'show advanced options', 1;

GO

RECONFIGURE;

GO

sp_configure 'clr enabled', 1;

GO

RECONFIGURE;

GO

In order to create the catalog, we need to connect to the SQL Server instance and right-click on the ‘Integration Services Catalogs’ and click ‘Create Catalog’.

In the next screen we are asked for the encryption password and whether we would like to run the Catalog.startupprocedure every time the SQL Server instance starts (the Catalog.startup stored procedure fixes the status of the packages in the catalog if there were packages running when the SQL Server instance went down):

After we have created the Catalog, we can create a new folder under it, which will contain our first project. To create the folder, simply right-click on the SSISDB catalog and click ‘Create Folder’:

Then we are presented with a dialog to enter the folder name:

Under every newly-created folder we have two sub-folders: ‘Projects’ and ‘Environments’.

The ‘Projects’ folder will contain the deployed SSIS project and the ‘Environments’ folder will contain the different sets of environment settings we will be creating later on. These will include such things as the settings for Development server, for Test server, Production server.

Creating the SSIS project

The process for the creation of development projects in BIDS has not changed, except for the fact that the default setting in SQL 2012 onwards is the Project Deployment model. There is, of course, a way to right-click on the project solution and convert it back to the Package Deployment model.

For the purpose of this article, however, I will create a database ProjectDeployment_Test and two tables calledInfoSource and InfoDest.

Here is the script for creating the objects:

CREATE DATABASE [ProjectDeployment_Test]

 CONTAINMENT = NONE

 ON  PRIMARY

( NAME = N'ProjectDeployment_Test', FILENAME = N'D:\Databases\ProjectDeployment_Test.mdf' , SIZE =4096KB , FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'ProjectDeployment_Test_log', FILENAME = N'D:\Databases\ProjectDeployment_Test_log.ldf' ,SIZE = 1024KB , FILEGROWTH = 10%)

GO

ALTER DATABASE [ProjectDeployment_Test] SET RECOVERY SIMPLE

GO

USE [ProjectDeployment_Test]

GO

CREATE TABLE [dbo].[InfoSource](

       [id] [int] NULL,

       [SomeData] [int] NULL

) ON [PRIMARY]

GO

CREATE TABLE [dbo].[InfoDest](

       [id] [int] NULL,

       [SomeData] [int] NULL

) ON [PRIMARY]

GO

INSERT  INTO [dbo].[InfoSource]

        ( id ,

          SomeData

        )

        SELECT  1 ,

                12345

        UNION ALL

        SELECT  2 ,

                134545

        UNION ALL

        SELECT  3 ,

                4576467

After I have created the database and the objects, let’s create a simple SSIS package in our project. The package will truncate the destination table and copy the data from the InfoSource table to the InfoDest table.

The package and project look like this:

Now let’s create a project parameter by double-clicking the Project.params. The newly created parameter looks like this:

Project parameters are accessible by any package in the project and are very useful for dynamic configurations of the deployed projects.

I will go in detail about how to use project parameters in the next article on Project Deployment: Configuration and Management of the SSIS, but for now all we need to do is to put this project parameter in use.

We can do this by clicking once on the connection in our SSIS package and then editing the Expressions in the Properties:

Deploying the project

Now let’s deploy the project to our SQL Server instance. By right-clicking on the project solution in BIDS and clicking Deploy, we get to the following wizard:

We have to fill in the Server name, in my case it is the localhost. Then we click ‘Next’and then ‘Deploy’.

Another way to deploy our project, if we do not have sufficient rights to do so, is to go to the bin folder of our SSIS project after we have built the solution and deliver the *.ispac file which is found there to a system administrator who can deploy the project for us. (in this case my file is called ProjectDeploymentSSIS.ispac)

The system administrator then has to login to the SQL Server instance, go to the SSIS Catalog, right-click on the Projects folder in the correct folder and click ‘Deploy Project’:

Then the following wizard appears:

The deployed project now looks like this:

If you encounter an error “A .NET Framework error occurred during execution of user-defined routine or aggregate "deploy_project_internal": System.ComponentModel.Win32Exception: A required privilege is not held by the client”, then make sure that the SQL Server service account is added to the Local security policy “Replace a process level token” in ‘User Rights Assignments’ in the secpol.msc console. A restart of the SQL Server service is required to change the permissions.

From this point on, we can execute the deployed package by scheduling an SQL Server Agent Job and using the package in it.

Here are a few caveats of the Project Deployment model:

• keep in mind, that in the project deployment model there is no way to deploy only changes to a single package. Instead, the entire project has to be deployed every time.
• The SQLCLR is used to deploy the packages to the SSIS catalog, but not needed further on for running the packages. So, even in organization with strict security policies, the SQLCLR can be enabled while setting up the SSIS catalog and while deploying, after which it can be disabled again
• Finally, if you are using Transaction Option: Required in your SSIS packages, you might run into trouble with the permissions of the MSDTC. Here is a very useful article on how to solve the problems with the SSIS and the distributed Transactions Coordinator: Troubleshooting MSDTC Permission Issues when a Distributed Transaction Starts

Conclusion:

So far we have seen how easy it is to deploy packages by using the new Project deployment model. This really speeds up the deployment of large scale environments (in large organizations there may be 100s of SSIS packages per project).

In the next article of the series 'SSIS 2012 Projects: Deployment Configurations and Monitoring', we pay special attention about dynamic configurations, the use of environment variables and large scale deployments. I'll also explain how easy it is to monitor the packages.

Reference: https://www.simple-talk.com/sql/ssis/ssis-2012-projects-setup,-project-creation-and-deployment/