Wednesday, 20 May 2020

Single package deployment in SQL Server Integration Services 2016

SQL Server 2016 has some exciting enhancements to SQL Server  Integration Services. In this article, we take a look at how the issue of single package deployment into an SSIS Catalog has been resolved with SQL Server release.

Prerequisites

The demonstrations in this article were conducting using SQL Server 2106 RC3 and Visual Studio Community 2015.

Background

In my personal experience, delivering SQL Server Business Intelligence solutions using SQL Server Data Tools (SSDT) is by far a more favorable experience compared to its predecessor, Business Intelligence Development Studio (BIDS). Some of many advantage that makes SSDT better than BIDS ranges from features like multiple deployment models to choose from, built in package catalog logging mechanisms to trivial things such as the ability to undo or re-do changes in you SSIS packages.

Most of the aforementioned features relating to SSIS packages such as catalog logging are only available in the project deployment. Unfortunately, one of the key limitations in project deployment model is its inability to deploy single packages. For instance, if you had to refactor Package 73 in a solution with 100 packages, you will have to re-deploy the entire solution with all 100 packages even though you only made changes to a single packages. Figures 1 and 2 illustrates the lack of single package limitation where by the Deploy option is available at the project level of an SSDT 2013 solution but disappears when attempting to deploy individual package PackageC.
Figure 1: Project deployment in SSDT 2013
Figure 2: Attempting to deploy single package in SSDT 2013
The only workaround to this limitation was to firstly convert existing project deployment mode to package deployment model and then deploy the converted solution. However, this workaround has always been ineffective as package deployment models are not setup to make the deployments into SSIS Catalog which meant that every time you converted from project to package deployment model you had to forego the features inherently available in the SSIS Catalog.

How to Deploy Single Package into SSIS Catalog

  • Visual Studio

    With the release of SQL Server 2016 and SSDT 2016 the issue of single package deployment is now a thing of past. For instance, Figure 3 shows an SSIS 2016 project with the New Deploy Package option that comes up for deploying individual packages within a project deployment model. This means you no longer have to convert to package deployment before you can deploy individual packages!
Figure 3: Project Deployment Model in SSDT 2016
With this new feature, you are not just limited to deploying single packages instead you can also deploy multiple packages. As shown in Figure 4, all you need to do, is to click and hold down the control key (Ctrl) and then choose the packages that you want to display.
Figure 4: Multiple Package Deployment in SSDT 2016
So far we have seen how we can deploy a single package as well as multiple packages within the project  deployment model. So what happens if, like Figure 3 we chose to deploy PackageB but after been redirected to the Deployment Wizard screen we realized that actually we needed to deploy PackageA too? Well in this version of SSDT 2015/SSIS 2016, we are able to correct our selection prior to deploying to the SSIS Catalog.

All we need to do is go back to the Select Source Step and check(uncheck) the package that we want to add/remove in our deployment step as shown in Figure 5.
(Note: The Integration Services Deployment Wizard often jumps from the Introduction step to the Select Destination Step. So, if we  want to get to the Select Source Step we will have to click the Previous button)

Figure 5: Integration Services Deployment Wizard

  • ISDeploymentWizard

    Generally, many features in Visual Studio are rendered as standalone executable programs. Not surprisingly, whenever we click Deploy or  Deploy Package in SSDT 2015, Visual Studio launches a separate application, ISDeploymentWizard as shown in Figure 6.
Figure 6: Location of IsDeploymentWizard file
 Therefore, another way of deploying packages is to launch the ISDeploymentWizard application directly. There are advantages and disadvantages of deploying packages directly from wizard. One advantage is that because Visual Studio can sometimes consume more of  our system resources such as memory and CPU, as shown in Figure 7, deploying packages directly from the wizard is likely to save from a possible system performance bottleneck experience.
Figure 7: Task Manager
Prior to launching the deployment wizard Visual Studio collects all information that will be part of the deployment and passes it along to the wizard. That is why the Select Source step is often skipped as it is already preloaded with source information collected by Visual Studio. Unfortunately, when launching wizard directly, we get a blank screen as shown in Figure 8 and we will have to specify everything from the beginning.
Figure 8
  • Command Line Deployment

Command line deployments are usually necessary when SSIS build and deployment is automated or managed as part of Continuous Integration process. Command line package deployment to catalog is again made possible via the ISDeploymentWizard execution file.

The hardest part in this type of deployment is getting the command line syntax correct. The easiest way to get the command syntax is by copying it out of the wizard's summary page as shown in Figure 9.
Figure 9: Deployment summary screen
The next step would be then to launch windows command line program and change its working directly to the SQL Server path that contains ISDeploymentWizard exe file. Finally, we just need to past the command line copied above and press enter to deploy as shown in Figure 10.
Figure 10: Command line package deployment
Soon after a new line with path to working directory will come up which will mean that our deployment was successful. Another way to confirm that the deployment was successful is by looking at version number of our project - the current version should have deployed time closer to the time you deployed via command line.

Figure 11: Catalog project versions

Conclusion

With all its advantages, SSDT had previously failed to address the issue surround single or manual package deployment into the SSIS Catalog. However, the recent release of SSDT 2015 for developing BI solutions into SQL Server 2016 introduces various ways of deploying single packages without converting to package deployment model.

SQL Server Query Execution Plans for beginners – Clustered Index Operators

In this article we will continue discussing the various execution plan operators related to clustered indexes and what they do, when do the...