How to fix a Failed Task for a Biml-Built SSIS Script Task: No such interface supported

I've been testing out creating script tasks through Biml for SSIS packages. I want to be able to locally execute/test packages successfully.

I was unable to execute the packages for a project from my local development environment, as they all error with the same error below.

Issue: Error: 0x0 at ScriptTask 1, Script Task : Unable to cast COM object of type 'System.__ComObject' to interface type 'Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariables100'. This operation failed because the QueryInterface call on the COM component for the interface with IID '{2CD38B23-6C17-4025-A8B6-D2E497DD1DDC}' failed due to the following error: No such interface supported (Exception from HRESULT: 0x80004002 (E_NOINTERFACE)). at Microsoft.SqlServer.Dts.Runtime.Variables.get_Item(Object index) at ScriptMain.Main() Error: 0x6 at ScriptTask 1: The script returned a failure result. Task failed: ScriptTask 1

I was able to execute these packages successfully from another server after doing a project deployment from Visual Studio to the SSIS catalog (SSISDB) on that server (SQL Server 2016).

I used the following references for the AssemblyInfo and ScriptMain:

• Varigence Documentation code sample:
  https://www.varigence.com/Documentation/Samples/Biml/Script+Task+Project
• BimlScript code sample: http://bimlscript.com/Snippet/Details/74

<Script ProjectCoreName="ST_232fecafb70a4e8a904cc21f8870eed0" Name="ScriptTask 1">
    <ScriptTaskProject>
        <ScriptTaskProject ProjectCoreName="ST_c41ad4bf47544c49ad46f4440163feae" Name="TaskScriptProject1">
            <AssemblyReferences>
                <AssemblyReference AssemblyPath="Microsoft.SqlServer.ManagedDTS.dll" />
                <AssemblyReference AssemblyPath="Microsoft.SqlServer.ScriptTask.dll" />
                <AssemblyReference AssemblyPath="System.dll" />
                <AssemblyReference AssemblyPath="System.AddIn.dll" />
                <AssemblyReference AssemblyPath="System.Data.dll" />
                <AssemblyReference AssemblyPath="System.Windows.Forms.dll" />
                <AssemblyReference AssemblyPath="System.Xml.dll" />
            </AssemblyReferences>
            <Files>
                <File Path="AssemblyInfo.cs">
                    using System.Reflection;
                    using System.Runtime.CompilerServices;

                    //
                    // General Information about an assembly is controlled through the following
                    // set of attributes. Change these attribute values to modify the information
                    // associated with an assembly.
                    //
                    [assembly: AssemblyTitle("ST_c41ad4bf47544c49ad46f4440163feae.csproj")]
                    [assembly: AssemblyDescription("")]
                    [assembly: AssemblyConfiguration("")]
                    [assembly: AssemblyCompany("Varigence")]
                    [assembly: AssemblyProduct("ST_c41ad4bf47544c49ad46f4440163feae.csproj")]
                    [assembly: AssemblyCopyright("Copyright @ Varigence 2013")]
                    [assembly: AssemblyTrademark("")]
                    [assembly: AssemblyCulture("")]
                    //
                    // Version information for an assembly consists of the following four values:
                    //
                    //      Major Version
                    //      Minor Version
                    //      Build Number
                    //      Revision
                    //
                    // You can specify all the values or you can default the Revision and Build Numbers
                    // by using the '*' as shown below:

                    [assembly: AssemblyVersion("1.0.*")]
                </File>
                <File Path="ScriptMain.cs">
                    using System;
                    using System.Data;
                    using Microsoft.SqlServer.Dts.Runtime;
                    using System.Windows.Forms;

                    // if SSIS2012, use the following line:
                    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]

                    // if earlier version, use the next line instead of the above line:
                    // [System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
                    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
                    {
                        enum ScriptResults
                        {
                            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
                            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
                        };

                        public void Main()
                        {
                            try
                            {
                                int totalInsertedRowsToDestination = (int)Dts.Variables["User::TotalInsertedRowsToDestination"].Value;
                                int rowCountNew = (int)Dts.Variables["User::RowCountNew"].Value;
                                int totalUpdatedRowsToDestination = (int)Dts.Variables["User::TotalUpdatedRowsToDestination"].Value;
                                int rowCountChanged = (int)Dts.Variables["User::RowCountChanged"].Value;
                                int totalUnChangedRowsToDestination = (int)Dts.Variables["User::TotalUnChangedRowsToDestination"].Value;
                                int rowCountUnchanged = (int)Dts.Variables["User::RowCountUnchanged"].Value;

                                totalInsertedRowsToDestination += rowCountNew;
                                totalUpdatedRowsToDestination += rowCountChanged;
                                totalUnChangedRowsToDestination += rowCountUnchanged;

                                Dts.Variables["User::TotalInsertedRowsToDestination"].Value = totalInsertedRowsToDestination;
                                Dts.Variables["User::TotalUpdatedRowsToDestination"].Value = totalUpdatedRowsToDestination;
                                Dts.Variables["User::TotalUnChangedRowsToDestination"].Value = totalUnChangedRowsToDestination;

                                Dts.TaskResult = (int)ScriptResults.Success;
                            }
                            catch (Exception ex)
                            {
                                Dts.Events.FireError(0, "Script Task ", ex.Message + "\r" + ex.StackTrace, String.Empty, 0);
                                Dts.TaskResult = (int)ScriptResults.Failure;
                            }
                        }                                   
                    }
                </File>
            </Files>
            <ReadOnlyVariables>
                <Variable Namespace="User" DataType="Int32" VariableName="RowCountNew" />
                <Variable Namespace="User" DataType="Int32" VariableName="RowCountChanged" />
                <Variable Namespace="User" DataType="Int32" VariableName="RowCountUnchanged" />
            </ReadOnlyVariables>
            <ReadWriteVariables>
                <Variable Namespace="User" DataType="Int32" VariableName="TotalInsertedRowsToDestination" />
                <Variable Namespace="User" DataType="Int32" VariableName="TotalUpdatedRowsToDestination" />
                <Variable Namespace="User" DataType="Int32" VariableName="TotalUnChangedRowsToDestination" />
            </ReadWriteVariables>
        </ScriptTaskProject>
    </ScriptTaskProject>
    <PrecedenceConstraints>
        <Inputs>
          <Input OutputPathName="SQL Update <#=dstTableName#>.Output" />
        </Inputs>
    </PrecedenceConstraints>
</Script>

I expect the output to be: SSIS package finished: Success without errors in the script task.

My environment:

• Windows 10 Enterprise 6.3 x64
• Microsoft Visual Studio 2015 Shell (integrated): 14.0.23107.0
• Microsoft .NET Framework: 4.7.03056
• BimlExpress: 1.0
• SQL Server Data Tools: 14.0.61705.170
• SQL Server 2016 (SP1-GDR): 13.0.4224.16(x64)

I reproduced the error locally on multiple different environments/machines and identified a fix.

Resolution: Change the SSIS project's TargetServerVersion from SQL Server 2014 to SQL Server 2016. The resulting message after running the package is SSIS package finished: Success.

Why:

Something in these environments is missing needed pieces to work with a TargetServerVersion of SQL Server 2014 related to the IDTSVariables100 Interface. That interface relates to SQL Server .NET SDK 2017 2016. https://docs.microsoft.com/en-us/dotnet/api/microsoft.sqlserver.dts.runtime.wrapper.idtsvariables100?view=sqlserver-2017

About TargetServerVersion and ProjectVersion, Andy Leonard explains in his blog post,

"the TargetServerVersion property can be used to maintain the current Production version of an SSIS project using the latest tools, as long as the current Production version is SSIS 2012+. And the TargetServerVersion property can be updated to the latest version available by simply changing the value in a dropdown". https://andyleonard.blog/2018/08/a-tale-of-two-properties-ssis-projectversion-and-targetserverversion/

<ProductVersion>14.0.600.250</ProductVersion>

More thoughts are below in the Additional References for SSDT section.

How-to:

1. Right click on the SSIS project [MySsisProject (SQL Server 2014)] in Visual Studio and select Properties.

2. Within the newly opened Property Page, expand the Configuration Properties group and select General. Then select the appropriate TargetServerVersion (SQL Server 2016 in my situation)

3. Read the warning, mentioning possible issues with extensions and determine if you want to proceed.

Now the the SSIS project has SQL Server 2016 in parentheses after the project name, MySsisProject (SQL Server 2016). This resolves the issue. Next, execute the package locally to verify the successful package completion.

Tested Environments/Machines:

1. Environment :

   • Windows 10 Enterprise 6.3 x64
   • Microsoft Visual Studio 2015 Shell (integrated): 14.0.23107.0
   • Microsoft .NET Framework: 4.7.03056
   • BimlExpress: 1.0
   • SQL Server Data Tools: 14.0.61705.170
   • SQL Server 2016 (SP1-GDR): 13.0.4224.16(x64)

2. Environment :

   • Windows 10 Enterprise 6.3 x64
   • Microsoft Visual Studio Enterprise 2017: 15.9.8
   • Microsoft .NET Framework: 4.7.03056
   • BimlExpress: 1.0
   • SQL Server Data Tools: 15.1.61902.21100
   • SQL Server 2016 (SP1-GDR): 13.0.4224.16(x64)

3. Environment :

   • Windows Server 2012 R2 Datacenter 6.3 x64
   • Microsoft Visual Studio Professional 2015: 14.0.25431.01 Update 3
   • Microsoft .NET Framework: 4.7.02053
   • BimlExpress: 1.0
   • SQL Server Data Tools: 14.0.61705.170
   • SQL Server 2016 (SP1): 13.0.4001.0(x64)

Additional Reference for SSDT (SQL Server Data Tools):

• SSDT is designed to be backwards compatible and Information on installing SSDT: https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017
• Examples of Script Tasks failing and then an SSDT release (15.9.0 tied to VS 2017) fixing the issue: https://feedback.azure.com/forums/908035-sql-server/suggestions/32896399-script-tasks-losing-code-in-ssdt-17-1-for-visual-s https://docs.microsoft.com/en-us/sql/ssdt/release-notes-ssdt?view=sql-server-2017#ssdt-for-visual-studio-2017-1570
• I found a similar issue related to "SSIS Script Task - No Interface Supported" relating to a version difference, with a fix of loading an older version; however, the answer was limited and did not describe the needed resolution of the identified issue for the above question: SSIS Script Task - No Interface Supported