CODEWITHSUNDEEP
sql-servervb.netssis
user3532499
user3532499

Reputation: 11

SSIS(VS 2012) Issue with VB script compounent

I am facing an issue in SSIS. I just upgraded my project from VS 2005 to VS 2012 and all of my packages were upgraded at the same time. All of them work well, except the one where I use script task to check if a file exists in a folder before working on the data from the flat file.

Since this upgrade, when I launch package with this script task:

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

And then it crashes on the end sub with the following error message:

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

If I disable the Dts.Events.FireError I still got the same error on the end sub

Here is my code(Visual Basic 2012):

Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime

<Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase

    Enum ScriptResults
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    End Enum

    Public Sub Main()

        Dim filepath As String
        filepath = "\\localhost\MYPATH"

            If My.Computer.FileSystem.FileExists(filepath) Then
        If FileLen(filepath) > 0 Then
            Dts.TaskResult = ScriptResults.Success
        Else
            Dts.Events.FireError(0, "FlatFile", "Error loading '" + filepath + "', flat file not found", String.Empty, 0)
            Dts.TaskResult = ScriptResults.Failure

        End If
    Else
        Dts.Events.FireError(0, "FlatFile", "Error loading '" + filepath + "', flat file not found", String.Empty, 0)
        Dts.TaskResult = ScriptResults.Failure

    End If

End Sub

End Class

Upvotes: 1

Views: 6557

Answers (1)

billinkc
billinkc

Reputation: 61221

I expect the issue is that you are not providing a return status for the subroutine

    If My.Computer.FileSystem.FileExists(filepath) And FileLen(filepath) > 0 Then
        Dts.TaskResult = ScriptResults.Success
    Else
        Dts.Events.FireError(0, "FlatFile", "Error loading '" + filepath + "', flat file not found", String.Empty, 0)
        Dts.TaskResult = ScriptResults.Failure
    End If

Edit

Expectations are one thing but actually debugging is another. I set a break point at the step where you assigned filepath. The root problem is that the FileLen operation is trying to check the size of a file that doesn't exist. Here you can see that a FileNotFoundException is thrown and unhandled.

enter image description here

Upvotes: 2

Related Questions