SSIS - DTS Script Task has encountered an exception in user code

Question

I've had about 10 packages that have been running pretty much flawlessly for months. These packages utilize the same script task that I've basically copied across all the packages. All this script task does is send an email. I came in to work on Monday and all of a sudden I'm getting this DTS Script Task error on all the packages as soon as it hits the email script task. Literally nothing has changed with any of these packages and I'm unsure of what steps to take to fix it.

#Region "Imports"
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
#End Region

Public Sub Main()

    'send email notification
    Dim message As String = "message goes here"
    sendEmail("[email protected]", "Subject is here", message)



    Dts.TaskResult = ScriptResults.Success
End Sub


Sub sendEmail(ByRef toaddr As String, ByRef sbj As String, ByRef msg As String)
    Dim objOutlook As Object
    Dim objOutlookMsg As Object
    objOutlook = CreateObject("Outlook.Application")
    objOutlookMsg = objOutlook.CreateItem(0)
    With objOutlookMsg
        .To = toaddr
        .Subject = sbj
        .Body = msg
        .sentonbehalfofname = "[email protected]"
        .Send()
    End With
    objOutlookMsg = Nothing
    objOutlook = Nothing
End Sub

When I step through the code, it seems to happen here:

Any help you can provide would be greatly appreciated.

BIDeveloper · Accepted Answer · 2016-04-29 09:19:32Z

1

Wherever you are running your package (locally? on a server?) the Outlook object no longer exists (or there is a permission issue - but this is unlikely)

Dependencies like this are always difficult to manage when systems change - and they always will! It's difficult to be 100% sure because I can't see from your example what you are sending in your email - but I would recommend you ditch the script altogether and use the Send Mail Task task.

answered Apr 29, 2016 at 9:19

BIDeveloper

2,6385 gold badges36 silver badges51 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

SebTHU Over a year ago

+1. I used to have nightmares about "Cannot create ActiveX component". CreateObject is not a reliable way to do things. With email, there's the Send Mail task and making a direct SMTP connection in code as much better alternatives.

Jon Over a year ago

I can't use the send mail task because of my company's email setup...can't remember why exactly, but I did a bunch of research because I couldn't get that to work and ended up settling on the script task..

Jon Over a year ago

I'm currently running the packages locally

BIDeveloper Over a year ago

I suggest you write a VB Script which attempts to simulate the script - once you have that working you can paste it into your package. I do strongly recommend you follow the answer posted instead though!

Collectives™ on Stack Overflow

SSIS - DTS Script Task has encountered an exception in user code

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related