DTS Script task Runtime Error During ssis script task Execution

[RAGHU01]

Hi all,

I'm trying to develop an ssis packages which includes for loop & script task in it. I'm trying to look at file file in folder using script task(C#) , if flat file is present in folder , should execute other task. If flat file is not present in folder the process should wait for some minutes and check the folder again for the file. For this i'm using Visual studio 2019, SQL server 2016 as target version, Microsoft Visual C#2015 in script task.

When i'm executing the package i'm getting below error message.

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

below is script code

#region Namespaces
using System;
using System.Data;
using System.IO;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Threading;
#endregion

namespace ST_c937e73bb1724a9bae78f2ac13d34ca3
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{
public void Main()
{
string FileLocation;
string[] Files;
Int32 DelayTimer;
Int32 result;

result = 1;
DelayTimer = (Int32)Dts.Variables["User:: DelayTimerInMS"].Value;
FileLocation = (string)Dts.Variables["User::FileFolder"].Value.ToString();
Files = Directory.GetFiles(FileLocation);
string filepath = Dts.Variables["User::FileFolder"].Value.ToString();
if (
File.Exists(filepath))
{
MessageBox.Show("Data File Name:" + Files[0].ToString());
Dts.Variables["User:FileExist"].Value = true;
Dts.TaskResult = (int)ScriptResults.Success;
}
else
Thread.Sleep(DelayTimer);

Dts.TaskResult = (int)ScriptResults.Success;
}

#region ScriptResults declaration
/// <summary>
/// This enum provides a convenient shorthand within the scope of this class for setting the
/// result of the script.
///
/// This code was generated automatically.
/// </summary>
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

}
}


Thanks

Continue reading...