Obscure Problems and Gotchas

Introduction

I usually like to keep any explanation I write to be straight to the point, but informational and no fluff. There are times where this is not possible – this is one of those times. This explanation cannot be short as it requires an understanding of how SSIS works when working with custom DLLs. It’s critical that you understand a few things about SSIS before attempting to do more complex tasks with it. Just remember, when it comes to SSIS don’t fight the pattern, just embrace it.

SSIS quirks

SSIS is great when it works, but when it is having a problem it can become very stressful. Many of these problems arise due to frustrating quirks that exist in SSIS. If you have ever worked with COM then you will understand how fragile SSIS can be sometimes. Just remember that even when you are working with a C# wrapper, you are still working with COM and you have to remember how quirky COM can be.

• Strongly coupled relationship with SQL Server DLLs
  • The version of your SQL server impacts all DLLs involved with SSIS
  • The edition of your SQL server impacts your ability to be able to develop with SSIS
  • When developing with SSIS, you should use the SQL Server Developer edition
    • There are a set of DLLs you MUST install as an option when installing SQL Server
• Windows updates can have negative impacts on your SSIS development
  • SQL server (for Windows) is strongly coupled to Windows.
  • SQL server depends on Windows DLLs in order to function, so if one of those DLLs is changed by a Windows update – all of a sudden a working SSIS project could stop working. Be very careful with SQL Server and Windows updates.
• Every version of Visual Studio impacts your ability to develop with SSIS
  • I strongly advise AGAINST early adoption of a new version of Visual Studio if you are working with SSIS religiously – you will be sorry – don’t do it.
• Microsoft’s releases of SSDT are incredibly delayed, riddled with bugs and have pathetic support at best
  • After every release of Visual Studio, SSDT follows. However, it usually doesn’t show up until months later which is not fair to anyone working on Business Intelligence (SSIS, SSRS and SSAS).
  • Don’t be fooled by the useless templates that are released before SSDT arrives in the Visual Studio plugin store. If you are installing VSIX plugins in order to develop with SSIS, SSRS or SSAS – then something is wrong and it won’t work anymore AFTER SSDT arrives.
  • You MUST uninstall any VSIX plugins before installing SSDT
• SSIS still leans heavily towards x86 – I have to do more research on this because maybe it has x64 support now, but even if it does because COM is involved it will be quirky at best. For all intents and purposes SSIS is x86 and always will be.
  • Just because you can call SSIS packages from C# doesn’t mean you should
  • If you are stubborn and you want to call SSIS packages from C# then you have now doomed your application with two problems:
    • Your application may not work anymore in x64 and if it works in production while you ignore the compiler warnings about x86 DLLs being mixed in with a project marked as Any CPU architecture then you are just lucky for now. You are playing with fire.
    • Additionally those SSIS C# DLLs are notorious for causing memory leaks so get used to recycling your application pools constantly when the package refuses to start anymore. The only thing that fixes this is recycling the application pool. Even if you try to dispose of those C# wrapper assemblies by calling the Dispose method it’s not going to help because once again that C# wrapper is wrapped around x86 COM DLLs. COM is quirky.

C# development in SSIS

If you have carefully considered what you are doing and you realized that you absolutely must use C# in your SSIS package then by all means develop away. For example if you need to use SFTP or call an API there is no native support for that in SSIS so you have no choice, you must use C#. If you are still using VB.NET stop and go learn C#.

Location of your C#

I strongly advise against putting all of your C# directly into your SSIS package. There are situations where this is unavoidable or it just doesn’t make sense to move it to a separate DLL, but it all comes down to good design and separation of concerns. When you put your C# directly into your SSIS package then you are doomed to testing it directly in SSIS which is incredibly annoying and unreliable because the debugger works half the time. The Visual Studio instance you are using to edit Script Task code is at bare minimum usefulness. It will not have any of your usual plugins and at times the code can become corrupted and not compile for unintelligible reasons or worse run at all again for unintelligible reasons.

• Write your code in such a way where only data is being passed between SSIS and your C# code.
• Advisable to implement an interface between your script task and your C# code so you know exactly what is publicly accessible. If you change the contract then you know you might be breaking something that a package is using.
• The best position you can be in is if your C# is unit testable outside of SSIS. Your C# DLLs should have little to no knowledge of what SSIS is and you will be in a good position.
• Avoid introducing complex third party DLLs into your C# – you will pay for it.

Using your C# DLLs with SSIS

One of the most rigid things about SSIS is how you use your custom DLLs with SSIS comes down to installing them into the GAC. There are articles out there that will say otherwise, but the solutions they provide are not practical. Therefore, your only option for using your custom DLLs in production is via the Global Assembly Cache (GAC). This is putting us right back into COM world. It’s bad practice to use the GAC, but SSIS is fully invested in having a strongly coupled relationship with:

• Windows
• SQL Server
• GAC

I am going to go on a limb here and say that ALL of the DLLs used by SSIS are located somehow or in someway in the GAC. That may not be 100% true, but just the fact that any of the DLLs it needs are in the GAC is bad enough. This is an old product using old design principals (COM). Therefore to continue a bad tradition your custom DLLs must be installed into the GAC.

The downside of using the GAC

It’s one thing if Microsoft decides to use the GAC, but you are not special and you really should not be using the GAC at all ever. Unfortunately, as it has already been established in the section above you have no choice with SSIS. Therefore, you must be cognizant of what it means to put DLLs in the GAC.

Welcome to DLL Hell

DLL Hell is when you have an application that is using the wrong version of a DLL and you don’t know why. More often than not this problem is caused by the GAC which is a machine specific problem. This is exactly why using the GAC is considered bad practice. When you install things in the GAC any application can use those components because they are visible to all applications for use. When an application runs, it always attempts to find its required DLLs in it’s immediate surroundings (bin folder or installation folder), if it cannot be found then the application will eventually try to find its dependency in the GAC. If it finds it, it will use it.

This problem can also affect your development. While you are working in Visual Studio your code compiles, but then when you run your program it reports failure because the DLLs cannot be found, or it is the wrong version (ImageFormatException) or the debug files (PDB) report a problem because the debug files don’t match the DLL that is currently loaded and running in memory. The only way around this problem is to uninstall those DLLs from the GAC while developing the same DLLs.

All this to say

Examples

• Do not install Entity Framework in the GAC – many applications use this and this could cause some serious issues. Avoid using an ORM for your SSIS packages anyhow – keep it Vanilla and just use manual CRUD. Keep it simple stupid.
• Avoid installing NewtonSoft’s JSON.net in the GAC
  • This is difficult to avoid because if you are working with APIs or you want to just take advantage of the serialization and deserialization.
  • I will admit, I have done this, but I am nervous about it because it’s installed on a production SQL Server. If any other applications that use this DLL are run on that server then we might have a problem.
  • It causes problems for me when I am developing locally sometimes, I have to uninstall the DLLs from my local at times to work unhindered.

SSIS Custom Task XML Corruption

All of the above finally funnels into this deployment issue which is fully acknowledged by Microsoft as a problem. I sincerely doubt they are going to fix it because they have no incentive to fix it. Microsoft hardly supports SSIS as it is because I am sure they would rather you use Azure Data Factory instead. Not cool.

https://docs.microsoft.com/en-us/sql/integration-services/devops/ssis-devops-overview?view=sql-server-ver15

The above is an excerpt directly from Microsoft’s website where it clearly states:

To build SSIS projects using any out-of-box components (including SSIS Azure feature pack, and other third-party components), those out-of-box components must be installed on the machine where the pipeline agent is running.

In this case “out-of-box components” refers to any custom SSIS tasks you yourself create. If it didn’t ship with SSIS it’s third party or non-standard.

Discovery of this issue

This is what I would refer to as the, “Ultimate case of it works on my machine“. This was a mind boggling issue that wasted literally about 20-30 hours of my life on when I didn’t know what the problem was and SSIS’s error reporting really really sucks ass sometimes. It operates in two modes:

1. Tells you exactly what the problem is in excellent detail
2. Tells you absolutely nothing, not even a hint

Researching this issue was particularly difficult when you don’t have a clue what the problem is, event viewer tells you nothing, SSIS tells you nothing, google doesn’t help because you don’t know how to phrase the problem with the exception of this meaningless crap here:

The package cannot execute because it contains tasks that failed to load

The first hint that you have run into this problem after a deployment is you will see this dreaded error:

YourPackageNameHere:Error: The package cannot execute because it contains tasks that failed to load.

The first time I saw this error, I could not understand what it was referring to because it contains virtually no information about the nature of the problem.

error 0x80070057

Digging further into the error log we find this gem:

OnError:Error: Cannot create a task from XML for task “Control flow task name here”, type “SSIS.ReplacementTask, {8B8C4D3B-F53C-4DCC-8D7D-6E197EE31125}” due to error 0x80070057 “The parameter is incorrect.”.

“Control flow task name here” refers to the literal name of your control flow task in your package.

I had no idea what “SSIS.ReplacementTask” was – because that’s not a thing! I did not create that! In hindsight the problem was staring me right in the face the whole time, but it wasn’t obvious at the time. Now had that error said the name of the actual DLL that could not be loaded, that would be much more helpful. The reason this is occurring is because at the time of deployment, if the deployment method cannot locate the DLL referenced in the XML in the GAC, the corresponding action is to replace it with “SSIS.ReplacementTask”.

This is what I refer to as overcompensation on behalf of the user. The developer(s) of the SSIS deployment process has decided on my behalf the behavior, when instead they should have asked for my input. I cannot conceive how this behavior is useful to anyone; unless it is to make them go bald with anger as they are ripping their hair out trying to understand why “But why does it work on my machine?!”.

How to reproduce this issue

Reproducing this issue is easy, but it has some prerequisites:

• Your SSIS package uses a custom built task
  • This requires DLLs be installed to the GAC – this is key
• The SSIS package functions properly on your local machine.
• When you deploy the package from your local machine using Visual Studio the deployment is successful
• When you deploy the package from an *.ispac file from your local machine deployment is successful

How to make it fail

1. Produce an *.ispac file.
2. Give the *.ispac file to someone who does not have the required DLLs installed in the GAC or copy the *.ispac file to a machine that does not have the DLLs installed in the GAC.
3. Run the *.ispac file and install as you would normally.
   1. Notice you will not receive even one warning about a failure to install the SSIS package. Microsoft… shame on you.
4. Attempt to run the SSIS package
5. You will get the errors shown above as explained already.

Why did it fail?

Assuming you read and understood everything from the beginning of this post the reason the installation failed is because the required DLLs were NOT installed in the GAC on the machine where the package was being installed from. This is the core of “It works on my machine“. Literally, the developer machine and the machine where the installation is occurring from are not setup identically, hence the failure.

When the *.ispac file is performing the installation it is searching for the required DLLs in the GAC, when it does not find those DLLs instead of doing the logical thing and LEAVING THE XML ALONE it replaces the XML with “SSIS.ReplacementTask” which is incredibly unhelpful. Therefore your package is now corrupted, on purpose, by Microsoft. Thanks guys!

What does this look like?

Everything is always better with an example. Compare these two XML code examples. They were abstracted to conceal the source code.

What the original uncorrupted XML is supposed to look like:

<DTS:Executable DTS:refId="Package\Send Success Email"
    DTS:CreationName="CustomPackageTask.ExecuteCustomScriptTask, Custom.Component.ExecuteCustomPackageTask, Version=1.0.0.0, Culture=neutral, PublicKeyToken=e5a93ea6490c778b"
    DTS:Description="Execute Custom Script Task"
    DTS:DTSID="{D7F61289-CB35-441C-B1D8-1DB404F3E2E3}"    DTS:ExecutableType="CustomPackageTask.ExecuteCustomScriptTask, Custom.Component.ExecuteCustomPackageTask, Version=1.0.0.0, Culture=neutral, PublicKeyToken=e5a93ea6490c778b"
    DTS:FailPackageOnFailure="True" DTS:LocaleID="-1"
    DTS:ObjectName="Send Success Email"
    DTS:TaskContact="Execute Custom Script Task; Company Name; All Rights Reserved"
    DTS:ThreadHint="0">

What the corrupted XML looks like post deployment:

<DTS:Executable DTS:refId="Package\Send Success Email"
    DTS:CreationName="SSIS.ReplacementTask"
    DTS:Description="Execute Custom Script Task"
    DTS:DTSID="{D7F61289-CB35-441C-B1D8-1DB404F3E2E3}"
    DTS:ExecutableType="SSIS.ReplacementTask"
    DTS:FailPackageOnFailure="True" DTS:LocaleID="-1"
    DTS:ObjectName="Send Success Email"
    DTS:TaskContact="Execute Custom Script Task; Company Name; All Rights Reserved">

Wow… how frustrating and completely unnecessary. Self-inflicted, put your own finger in your own eye stupid. Microsoft really screwed the pooch on this and I am not going to hold my breath on them fixing it. I have already asked them to fix other simple things and they flat out refuse to care. So like I said, I am pretty sure they would rather we use Azure Data Factory instead of SSIS. The next headache is – are any of the custom tasks that have been created compatible with Azure Data Factory. Honestly I am afraid to find out so I haven’t looked yet. That’s a problem for the future me.