Deploying SSIS Projects with Custom Components

Within SSIS you can make use of custom components which aren’t present out of the box. An example of some would be the Azure Feature Pack if you’re working with cloud resources.

These will let us use features not available natively. They can also provide a challenge down the line when we come to deploy changes to the project.

Here we’ll look at an example of this challenge, how to troubleshoot, and ultimately resolve the issue.

The problem

Recently I had a package fail which was part of a project that had been redeployed, but to which I hadn’t made changes. Looking at the SSIS execution log the error simply stated it couldn’t load one of the tasks:

This was confusing as that package hadn’t been changed and had ran without issue previously. Opening the package I saw the component which was raising the error:

Ok, so that looks odd. It looks like something might be missing. This wasn’t a package which had been changed though. What’s happened here?

Project deployment

This is due to using the Project Deployment method. When choosing this option it will rebuild and deploy the whole project at once.

This method produces a single .ispac file as its output. As its a single file for the whole project it means that all packages are rebuilt into it even if only a single one has changed.

If you want to spot differences between a package in different versions of a project, you can compare the outputs before and after. They’re actually just zip files so pick your favourite tool and extract away:

Comparing two versions of the same package (I’ve used Beyond Compare) we can see the difference in the metadata. Below we have the previously working version on the left and the now-broken version on the right.

Firstly we can see a connection has been completely removed:

Then for objects in the designer, these may be substituted for SSIS Replacement Tasks. Here’s the one shown previously:

We can clearly see something is missing which explains the odd behavior in the designer too. We’re missing these custom components from our SSIS (SQL Server Data Tools) installation.

Now we can see the issue, how can we fix the issue and protect ourselves better in the future?

Remediation

The simplest solution is to get the components installed. If you’re not sure of which they are it may be worth reviewing a good version of the package like above to see what details you can gleam from the metadata. Once you’ve identified and installed the components you should be able to rebuild and publish your project with all its features.

Having a copy of the project in a good state is crucial. That could be through source control, change control, or using the Versions feature in SSIS. Being able to both revert as well as compare different versions can be valuable when troubleshooting like above.

Moving forward, if you’re using Integration Services 2016 or higher you can opt to deploy using the Package Deployment approach. By only deploying specific packages (the ones you’ve changed!) you’ll be able to avoid this type of issue.

Given how I ran into this issue I’d also strongly advocate documentation around any projects which use custom components. Be kind to the next person to open that code. Be sure its clear which components are used across which packages inside a project.

Wrap up

In this post we’ve looked at the issue of deploying a SSIS project using custom components which aren’t installed. We’ve looked at the issue, what causes it, how to identify it, and some options to resolve and mitigate moving forwards.

This is one of those issues that can be resolved easily and once in place you won’t have to worry about it again. That makes documentation all the more important in my opinion as its infrequent enough to catch others out without warning if there isn’t some guidance in place.