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 this SQL Server release.
Prerequisites
The demonstrations in this article were conducted using SQL Server 2016 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 favourable experience compared to its predecessor, Business Intelligence Development Studio (BIDS). Some of the many advantages 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 your SSIS packages.
Most of the aforementioned features relating to SSIS packages such as catalog logging are only available in the project deployment model. 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 package. Figures 1 and 2 illustrates the lack of single package limitation whereby the Deploy option is available at the project level of an SSDT 2013 solution but disappears when attempting to deploy individual package, PackageC.
The only workaround to this limitation was to firstly convert existing project deployment model 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 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
Fortunately, with the release of SQL Server 2016 and SSDT 2015 the issue of single package deployment is now a thing of the 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 2015 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 deploy.
Figure 4: Multiple Package Deployment in SSDT 2015 So far we I have demonstrated how you can deploy a single package as well as multiple packages within the project deployment model. So what happens if, like in Figure 3 you have chosen to deploy PackageB but after you have been redirected to the Deployment Wizard screen you soon realise that actually you need to deploy PackageA too? Well, in this version of SSDT 2015/SSIS 2016, you are still able to later correct your selection prior to deploying to the SSIS Catalog.
All you need to do is go back to the Select Source step and check (uncheck) the package that you want to add/remove in your deployment as shown in Figure 5.
(Note: The Integration Services Deployment Wizard often jumps from the Introduction step to the Select Destination step. So, if you want to get to the Select Source step you 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 you 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 to deploying packages directly from the wizard. One advantage is that because Visual Studio can sometimes consume more of your system resources such as memory and CPU, as shown in Figure 7, deploying packages directly from the wizard is likely to save you 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, you get a blank screen as shown in Figure 8 and you will have to specify everything from the beginning.
Figure 8: Task Manager -
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, you just need to past the command line copied above and press enter to deploy as shown in Figure 10.
Soon after a new line with path to working directory will come up which will mean that your deployment was successful. Another way to confirm that the deployment was successful is by looking at version number of your project – the current version should have deployed time closer to the time you deployed via command line.
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.
Reference
Sifiso's LinkedIn profile
View all posts by Sifiso W. Ndlovu