https://www.simple-talk.com/sql/ssis/ssis-2012-projects-deployment-configurations-and-monitoring/
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:
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).
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.
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.
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.
SSIS 2012 Projects: Deployment Configurations and Monitoring
14 November 2014
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 called DatabaseName. 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.
