DTS Script Task Runtime Error: Exception has been thrown by the target of an invocation

Question

I have a ssis project developed in vs 2012 Ultimate. I use project deployment model and the project is set to run in 32 bit mode. The package executes without error in my development environment but when I deploy it to the ssis catalog and attempt to run it there, I get the following error:

I deploy the project to a Windows Server 2012 R2 and the database is also 2012 with an SSIS catalog.

I have opened the project on the server and attempted to debug the script task on the server with a break point but the error occurs prior to getting to the break point.

If I disable all of my script tasks, the package executes without error on the server.

I have also updated the servers local security options by disabling System Cryptography: Use FIPS Compliant Algorithms as a possible solution posted on Microsoft Support.

I have also attempted to run as a file system package on the server and I get the same error.

I have also triple checked that my variables are correct and even set them as read write variables.

The first script task code is below (but all scrip tasks fail / three in this package). The script task references a dll called UHS.IntegrationServices.CommonUtils that has been GACed and the dll was built in VS 2005 with framwork 2.0.50727.

Public Sub Main()

    Dim packageID As String = Dts.Variables("System::PackageID").Value.ToString()
    Dim strInterfaceName As String = Dts.Variables("System::PackageName").Value.ToString()
    Dim strConfigConnectionString1 As String = Dts.Variables("User::UHS_SSIS_CNXN_STR_CONFIG").Value.ToString()
    Dim myConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection
    Dim mySqlCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand
    Dim myReader As OleDb.OleDbDataReader = Nothing
    Dim rowsProcessed As Integer = 0

    Try
        myConnection.ConnectionString = strConfigConnectionString1
        myConnection.Open()

        Dim getEmailQuery As String = "SELECT Email FROM EmailMaster" & _
                                      " where InterfaceName='" & strInterfaceName & "'" & _
                                      " and Active = 'Y' "

        Dim NotifyInterface As SMTPNotifyAlerts
        NotifyListenerManager.GetNotifierByType(packageID, NotifyInterface, CommonNotifierAlerts.AlertInfo.AlertTypes.alertEmail)


        mySqlCommand = New OleDb.OleDbCommand(getEmailQuery, myConnection)
        mySqlCommand.CommandTimeout = 0
        myReader = mySqlCommand.ExecuteReader

        If (myReader.HasRows()) Then
            While (myReader.Read())
                NotifyInterface.alertDest.Add(myReader("Email").ToString().Trim())
                rowsProcessed = rowsProcessed + 1
            End While
        End If

        NotifyListenerManager.BroadcastMessage(CommonNotifierAlerts.BasicAlerts, 0, Nothing, "Startup logfile")
        Dts.TaskResult = ScriptResults.Success
    Catch ex As Exception
        Dts.Events.FireError(0, "Init Email Master", ex.Message, "", 0)
        Dts.TaskResult = ScriptResults.Failure
    End Try

End Sub

The problem appears to be in the reference to the dll. When I comment out the NotifyInterface and NotifyListener lines the script executes without error but the dll is in the GAC and I can view it in C:\Windows\assembly.

Why would this work on my local but not the Server? Is the 2.0 framework needed on the server? If the reference to the dll is broken, shouldn't I get a more descriptive error?

Any assistance in finding trouble shooting ideas would be greatly appreciated.

Answer 1

You need to set a breakpoint at the beginning of the FIRST script task, just as you would do in any VB debugging situation. Then F8 through each line of code until an error is thrown.

Answer 2

My issue was solved. "The problem that we had was that our .net reference for Analysis Management Objects was 11.0 (2012) and it needed to be 12.0 (2014). "

Link: https://mitchellpearson.com/2015/04/13/upgrading-script-tasks-in-ssis-target-of-invocation-on-script-task/