SSIS Project with ZappySys fails when upgraded from SQL 2012 to SQL 2016

22 Jan

Subtitled – And I can’t edit them with VS 2010 either.

Background

I am currently testing our upgrade process, for moving all of our databases from SQL Server 2012 instances to SQL Server 2016. The SSIS projects that use the ZappySys PowerPack tools are failing with a particularly unhelpful message. In addition, attempts to open those projects with SQL Server Data Tools 2010 fails – with an equally unhelpful message. This article records what I did to remedy this situation.

Initial Error Messages

Having installed SQL Server 2016 and copied the databases, logins, jobs etc. from our 2012 instance I then copied the SSIS packages, using the process I documented in this article. When testing the jobs on the new 2016 instance that used these SSIS packages, the job would fail.
The integration Services Catalog (ISC) report has a series of error messages – the first being:
ZS Advanced File System Task:Error: Failed to load task “ZS Advanced File System Task”, type “”. The contact information for this task is “Advanced File System Task; ZappySys LLC (c) All Rights Reserved; SSIS PowerPack; http://www.zappysys.com/support”.
As that error message didn’t tell me anything useful, I attempted to import the project into Visual Studio Data Tools 2010 using the process previously documented here. When the tool attempted to import the package it failed with the following message:

Picture 1: Error message from VS 2010

Failure to open Project

Because the error message from ISC mentioned the ZappySys PowerPack I then contacted ZappySys, who responded quickly and suggested upgrading to the latest version of the software. This was a painless experience but changed nothing – the errors were the same.

Installing Data Tools

Searching for related articles found this by Koen Verbeeck (t|b) and details the versions of SQL Server Developer Tools required for various versions of SQL Server.

From this article I downloaded SQL Server Data Tools 2015 on the appropriate server. Initially this installation failed and the log file produced contained a series of similar messages:
Error 0x80072f08: Failed to send request to URL: https://go.microsoft.com/fwlink/?LinkId=715685&clcid=0x409

When I tried one of these links within Internet Explorer a security alert appeared, which I assumed was blocking the installation process:

Picture 2: IE message

IE message box


Checking the message box to prevent this message appearing again allowed the installation to complete.

Using SQL Data Tools 2015

With the correct version of Data Tools installed for SQL Server 2016 I can now open the SSIS project, although there are still errors relating to the ZappySys controls:

Picture 3: Data Tools Error Message

At least I can edit the project

Removing the ‘corrupt’ ZappySys controls and adding them back in corrects these issues, as well as creating a ZappySys connection which had disappeared completely.

Picture 5: The re-inserted control

Controls back in place

Another way of Copying the Project Between Instances

Having now installed the appropriate Data Tools I have discovered that if I import the package into Data Tools 2015, from the SQL Server 2012 instance and then deploy it to SQL Server 2016 (using Data Tools) that the project will also be fine – without any of the issues I had experienced.

Conclusion

Deploying via ISC works perfectly well if the version of SQL Server involved are all the same. However, there appears to be issues for some projects when deploying with different versions. Using the correct version of Data Tools (for the higher version of SQL) appears to cater for this.

References and Further Reading
ZappySys PowerPack
Copying a package between servers
Importing a package into SQL Server Data Tools
SSIS Versions and Tools
Download SQL Server Data Tools
What’s new in Integration Services 2016

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: