Tallan's Technology Blog

Tallan's Top Technologists Share Their Thoughts on Today's Technology Challenges

Fixing SQL Server 2012 Support for Custom SSIS Objects in Visual Studio 2015+

Matthew Gajdosik

You’re using the new One Designer cross-versioning in SQL Server Integration Services, and everything breaks when you try to downgrade to SQL Server 2012. The little icon that indicates that everything has gone wrong shows up,

missing_task_iconor when you try to interact with any custom components or tasks you get the following error, or something similar:

warehouse_13_error

 

Now, there are three things worth checking:

  1. Are your UpgradeMapping files set up correctly? They should point to a valid strong-named assembly, and use the same alias, for both versions of SQL Server that you’re attempting to deploy to. If not, fix this issue first and try again.
  2. After migrating your custom objects, navigate to the UserComponentTypeName property (for PipelineComponents) or to the CreationName field of the corresponding DTS:Executable in the package XML.
    • These should contain either the alias (typically the qualified name of the class, i.e. Sample.SSIS.CustomTask),
    • or the strong-name associated with the specific version you’re configured for (i.e. Sample.SSIS.CustomTask, Sample.SSIS, Version=11.0.0.0, Culture=neutral, PublicKeyToken=1234567890abcdef).
    • If not, then chances are it failed to migrate because it could not load the class correctly. Try reading further.
  3. Try to create a new instance of the custom object. Depending on where the reference in the error message is located, the items may not actually be in the toolbox. If they are, you should be able to add a fresh copy, and see that it is correctly created. However, trying to open the editing interface should present the error above (since the most common cause is an interface reference).

Why?

The biggest reason is that the assemblies backing SSIS were extensively reorganized in between SQL Server 2012 and SQL Server 2014. For IErrorReportingService, which is a nice-to-have interface for providing exception popups, this moved from Microsoft.DataWarehouse.dll (2012/11.0) to Microsoft.DataWarehouse.Interfaces.dll (2014/12.0). In fact, Microsoft has added this as a “Common Error” under their page on multi-targeting for custom objects:

microsoft_tip_for_warehouse_13_error

While the exact nature of the virtual hosting process used for One Designer support is not published anywhere (please let me know if it is!), this is enough to break the assembly loading, and appears to be a recent change to the hosting process. This gives me the impression that any custom code will now need to be able to work with exactly the same assemblies as the ‘host’ version used by Visual Studio 2015 (2016/13.0), but since all of the required interfaces have been pretty stable since SQL Server 2012, this shouldn’t be a huge problem.

Sample Solution

The work-around is simple enough; either remove the offending code completely, or make sure that you only use assemblies as they were written for SQL Server 2016, down to the .dll they were packaged in. The following snippet tries to handle the latter approach for the IErrorReportingService, so you’d just need to replace any references to the raw interface.

I tend to use wrappers, preprocessor directives, and configurations to automate anything related to versioning or finicky references, so you may want to adapt the following code to match your own approaches. You can assume that each version (2012/2014/etc) is produced by a new build configuration, and the 2012 configuration includes the conditional compilation symbol “DTS110″.

using Microsoft.SqlServer.MessageBox;
using System;
using System.Windows.Forms;

// Detect the conditional compilation symbol unique to our SQL Server 2012 build
#if DTS110
// This works differently in 2012 because of assembly reorganizations,
// and something that Microsoft changed since late 2016.
namespace Sample.SSIS.UI.Shared
{
    // Recreate the function of the original service
    public class ErrorReportingServiceWrapper
    {
        private IWin32Window _owner;
        public ErrorReportingServiceWrapper(IServiceProvider serviceProvider, IWin32Window owner)
        {
            _owner = owner;
        }
        public void ReportError(Exception e)
        {
            (new ExceptionMessageBox(e)).Show(_owner);
        }
        public void ReportError(String description)
        {
            MessageBox.Show(_owner, description, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
        public void ReportError(String description, Exception e)
        {
            (new ExceptionMessageBox(e) { Text = description }).Show(_owner);
        }
        public void ReportError(String description, Exception e, String caption)
        {
            (new ExceptionMessageBox(e) { Text = description, Caption = caption }).Show(_owner);
        }
        public void ShowPendingErrors() { } // Add code to this when needed; it is not often used.
    }
#else
using Microsoft.DataWarehouse.Design; // Include the reference when safe to do so; 2014+

namespace Sample.SSIS.UI.Shared
{
    // Mirror the original interface exactly, since this is just a wrapper in 2014+
    public class ErrorReportingServiceWrapper
    {
        private IErrorReportingService _errorReporting;
        public ErrorReportingServiceWrapper(IServiceProvider serviceProvider, IWin32Window owner)
        {
            _errorReporting = serviceProvider.GetService(typeof(IErrorReportingService)) as IErrorReportingService;
        }
        public void ReportError(Exception e)
        {
            _errorReporting.ReportError(e);
        }
        public void ReportError(String description)
        {
            _errorReporting.ReportError(description);
        }
        public void ReportError(String description, Exception e)
        {
            _errorReporting.ReportError(description, e);
        }
        public void ReportError(String description, Exception e, String caption)
        {
            _errorReporting.ReportError(description, e, caption);
        }
        public void ShowPendingErrors()
        {
            _errorReporting.ShowPendingErrors();
        }
    }
#endif
}

(Note that I’ve used the Microsoft.SqlServer.MessageBox.ExecutionMessageBox in Microsoft.ExceptionMessageBox.dll, since it provides a similar form to the one used by IErrorReportingService. If you don’t wish to add this dependency, you’ll need to work out your own version of a MessageBox.)

Once added to my own libraries, this cleared up the problem immediately, since this was the only interface I used that had changed assemblies. At the very least, it should expose the next error you might find, using the first three checklist steps from the introduction.

If you’ve got packages that are still broken in SQL 2012 compatibility mode, try to upgrade them back to the original version, then repeat. This should help the process “catch” any stragglers that need to be updated, since part of the problem is that the conversion process itself breaks when trying to process the broken reference, and leaves the qualified reference from the original version.

If you’re using third-party components/tasks, you’ll need to reach out their support, but if you have access to their source code you can always try recompiling with this fix in place.

Summary

Since the hosting process for supporting SQL Server 2012-2016 in Visual Studio 2015+ has some silent requirements for assemblies, you might run into issues where those assemblies have changed between SQL Server 2012 and SQL Server 2016. If you can find them early, you can skip the code entirely or provide a version-sensitive wrapper to make sure only safe references are ever used.

Hopefully this fixes any code you’ve been using, but if not, hopefully it points you in the right direction for any code that no longer works in SQL Server 2012 deployment mode. Are there any other issues you’ve run into when trying to support SQL Server 2016 downgrades, or assembly references that may have changed? If so, please feel free to comment below!

_________________________________________________________________________________________

To learn more about Tallan’s Data & Analytics solutions, and how they can transform your organization’s data into business intelligence and action, CLICK HERE.

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>