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 x64Microsoft Visual Studio 2015 Shell (integrated): 14.0.23107.0Microsoft .NET Framework: 4.7.03056BimlExpress: 1.0SQL Server Data Tools: 14.0.61705.170SQL Server 2016 (SP1-GDR): 13.0.4224.16(x64)
