2
votes

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:

<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)
2

2 Answers

3
votes

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

"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.

select Properties of SSIS project

  1. 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)

select TargetServerVersion

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

TargetServerVersion Change Warning

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):

0
votes

We had some environment issues as well after we decided to remove the different paths. As some had a dll in GAC and others only in Visual Studio PublicAssemblies directory

We still wanted to remove the path, but still needed to give a hint about the target version.

We now fixed it within Biml doing the assembly references like this:

<AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSRuntimeWrap, Version=14.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91, processorArchitecture=x86, Custom=null" />

And to make it more re-usable for other dll's like this:

<# int MajorSQLVersion  = 13; #>
<AssemblyReference AssemblyPath="Microsoft.SqlServer.DTSRuntimeWrap, Version=<#= MajorSQLVersion.ToString() #>.0.0.0" />