Running SSIS package on SQL Server Agent

A SSIS package can be executed using SQL Server agent.

To execute the package a Proxy Account and a credential must be configured in SQL Server.

Problems:

1) Executed as user: DOMAIN\user. The process could not be created for step 1 of job 0xB013D0354C8CBD46B79E948740EF5441 (reason: 1314).  The step failed.

The error 1314 is "A required privilege is not held by the client".

This message indicates that the SQL Server Service account doesn't have the required rights to switch the security context to the proxy account.

To fix it verify:

1) The proxy account have the "Act as part of the operating system" privilege.

2) The SQL Server Account is in the group

SQLServer2005MSSQLUser$<server>$<instance>

3) The Proxy account is in the group

SQLServer2005DTSLUser$<server>$<instance>

SSIS - Send mail from script task

Sending a mail form an integration service package (SSIS) is implemented using the .Net framework classes.

The only question is how to use a SMTP Connection Manager task to configure the SMTP Server.

Public Sub Main()

    Dim mailMessage As MailMessage

    Dim smtpClient As SmtpClient

    Dim smtpConnectionString As String = DirectCast(Dts.Connections("SMTP Connection Manager").AcquireConnection(Dts.Transaction), String)

    Dim smtpServer As String = smtpConnectionString.Split(New Char() {"="c, ";"c})(1)

    mailMessage = New MailMessage("from@myMail.net", "to@myMail.net")

    mailMessage.Body = "myMessage"

    mailMessage.Subject = "mySubject"

    smtpClient = New SmtpClient(smtpServer)

    smtpClient.Credentials = CredentialCache.DefaultNetworkCredentials

    mailMessage.IsBodyHtml = True

    smtpClient.Send(mailMessage)

    Dts.TaskResult = Dts.Results.Success

End Sub