question

Noonies avatar image
Noonies asked

Package script task failing

I'm stumped with a package issues. I have a script task that runs to send XML email. I can run the package from within BIDS using my network account, SQLSupport network account. When I use SQL.Service network account, which is what SQL Agent uses, the package fails on the Script Task. When I look at the Execution Results all i see is ! Error: The script returned a failure result. X Task Script Task failed. But when I run this package under my own user account and the SQL Support account, this works fine. The Script Task is the below: ' Microsoft SQL Server Integration Services Script Task ' Write scripts using Microsoft Visual Basic 2008. ' The ScriptMain is the entry point class of the script. Imports System Imports System.Data Imports System.Math Imports System.Net.Mail Imports Microsoft.SqlServer.Dts.Runtime _ _ 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() Try Dim varHTMLMail As MailMessage Dim varSMTPClient As SmtpClient Dim varMailBody As String = String.Empty Dim varAddresses As String = String.Empty Dim varAddressesBCC As String = String.Empty varMailBody = Dts.Variables("varHTML").Value.ToString varAddresses = Dts.Variables("varMailTo").Value.ToString varAddressesBCC = Dts.Variables("varMailBCC").Value.ToString ' your exchange/email server may require the 'from' address below to be a valid address varHTMLMail = New MailMessage(" SQLSupport@company.com", varAddresses, "Products received in Warehouse today!", varMailBody) varHTMLMail.Bcc.Add(varAddressesBCC) varHTMLMail.IsBodyHtml = True varSMTPClient = New SmtpClient("mail.company.com") varSMTPClient.UseDefaultCredentials = True varSMTPClient.Send(varHTMLMail) Dts.TaskResult = ScriptResults.Success Catch ex As Exception 'MsgBox(ex.Message) Dts.Log("Email Script Error " & ex.Message, 0, Nothing) Dts.TaskResult = ScriptResults.Failure End Try End Sub End Class What am I overlooking?
ssissql-server-agent
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

· Write an Answer
Pavel Pawlowski avatar image
Pavel Pawlowski answered
Take a look into the SQL Server Agent Job History log, Probably you will be able to find more information about concrete error message. As the package runs successfully under your credential and fails when executed from within SQL Agent, it can be, that the user account under which Agent is running does not have access to the mail server for example. Another possibility is, that you can have some sensitive information inside the package (like user names and/or passwords) and have an option to use User Certificate to encrypt sensitive data. In that case the Agent account do not has access to the encrypted data. As mentioned above, try to find out more information about the error in the log. Based on the more detailed information more help can be provided.
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Thanks for the tip on the mail server access. The server admin just checked the account and that was the issue. I cannot thank you enough Pavel! You have helped in on many occassions.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.