I have this BIML Script, which creates a Master and child package. The child package has a ScriptTask in it that maps the value of a parent package variable to a child package variable. When I Generate the packages in SSIS (Visual Studio 2015, target Sql Server 2014) they generate fine. If I run the master package, the child fails with this error:
DTS Script Task has encountered an exception in user code:
Project name: ST_GetJobLogIdHere
Exception has been thrown by the target of an invocation.
at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
When I open the ScriptTask in the child, then close it again, the package is dirty so I save it. After that, running the packages works properly.
Here's the BIML Script:
<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<ScriptProjects>
<ScriptTaskProject ProjectCoreName="ST_GetJobLogId" Name="ST_GetJobLogIdScript" VstaMajorVersion="0">
<ReadOnlyVariables>
<Variable Namespace="User" VariableName="vMasterJobLogId" DataType="Int32" />
</ReadOnlyVariables>
<ReadWriteVariables>
<Variable Namespace="User" VariableName="vJobLogId" DataType="Int32" />
</ReadWriteVariables>
<AssemblyReferences>
<AssemblyReference AssemblyPath="Microsoft.SqlServer.ManagedDTS" />
<AssemblyReference AssemblyPath="Microsoft.SqlServer.ScriptTask" />
<AssemblyReference AssemblyPath="System" />
<AssemblyReference AssemblyPath="System.Data" />
<AssemblyReference AssemblyPath="System.Windows.Forms" />
<AssemblyReference AssemblyPath="System.Xml" />
</AssemblyReferences>
<Files>
<File Path="AssemblyInfo.cs" BuildAction="Compile">
using System.Reflection;
[assembly: AssemblyTitle("ST_GetJobLogId")]
[assembly: AssemblyDescription("")]
[assembly: AssemblyConfiguration("")]
[assembly: AssemblyCompany("")]
[assembly: AssemblyProduct("ST_GetJobLogId")]
[assembly: AssemblyCopyright("Copyright @ BMI 2017")]
[assembly: AssemblyTrademark("")]
[assembly: AssemblyCulture("")]
[assembly: AssemblyVersion("1.0.*")]
</File>
<File Path="ScriptMain.cs" BuildAction="Compile">
using Microsoft.SqlServer.Dts.Runtime;
namespace ST_GetJobLogId
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPoint]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
int jobLogId = (int)Dts.Variables["User::vMasterJobLogId"].Value;
if (jobLogId != 0)
Dts.Variables["User::vJobLogId"].Value = jobLogId;
Dts.TaskResult = (int)ScriptResults.Success;
}
enum ScriptResults
{
Success = DTSExecResult.Success,
Failure = DTSExecResult.Failure
};
}
}
</File>
</Files>
</ScriptTaskProject>
</ScriptProjects>
<Packages>
<Package Name="Master" ProtectionLevel="EncryptSensitiveWithUserKey">
<Variables>
<Variable Name="vMasterJobLogId" DataType="Int32">1</Variable>
</Variables>
<Tasks>
<ExecutePackage Name="PKG - Execute FixMe">
<ExternalProjectPackage Package="FixMe.dtsx" />
</ExecutePackage>
</Tasks>
</Package>
<Package Name="FixMe" ProtectionLevel="EncryptSensitiveWithUserKey">
<Variables>
<Variable Name="vJobLogId" DataType="Int32">0</Variable>
</Variables>
<Tasks>
<Script Name="SCR - Get vJobLogId from parent vMasterJobLogId" ProjectCoreName="ST_GetJobLogIdHere" >
<ScriptTaskProjectReference ScriptTaskProjectName="ST_GetJobLogIdScript" />
</Script>
</Tasks>
</Package>
</Packages>
</Biml>
What am I missing? Why is the package failing until I open the code window manually? Thanks in advance for your help, Simon
Read the comments above for clarification of the question.
It turns out that BIML Express/BidsHelper in Visual Studio 2015 does not know which target version of SQL Server is set in the Project properties.
The solution was to install VS2013 / SSDT 2014, and BIML Express for VS2013 to be able to generate the SSIS 2014 packages properly.
VS 2015 / SSDT / BIML Express will only generate Script Tasks for SQL Server SSIS 2016 packages.
Thanks to Bill for the answer.