Wednesday, August 10, 2016

Error on Data Director: ConnectServerAndOpenDatabase Can not initialize automation server

I would like to record this error and the solution for my reference in future. We encounter this error when run the scheduler job by schedule, however if we run the scheduler job manually, it works perfectly.

This is the complete error on Data Director:
"PlusCFrontClient: ConnectServerAndOpenDatabase Can not initialize automation server\Program returned error"

Solution:
Choose the lowest notification level on User Account Control Setting (Windows)

SSIS - Use Script Task to send Email (HTML Message Body format)

On this session, I would like to write a simple document about how to send email by using SSIS - Script Task. Initially, I want to use "Send Mail Task" in SSIS, however, the message body is a plain text so that I can't do message body formatting (e.g. give a link on email body, bold text, italic text, etc). I hope in future SSIS version give us an option to use plain text or HTML format.

These are the steps:

1. On SSDT, drag Script Task from toolbox and set the name "Script Task - Send Email"
2. Create new SMTP Connection Manager and set the name "My SMTP Server"

















3. Create 4 new variables
EmailBody value :
Hi All,<br><br><b>This</b> message body is using HTML format.<br><br>Regards,<br>abc<br><i>Generated by using SSIS</i>




















4. Double click Script Task to open Script Task Editor. On "ReadOnlyVariables", input 4 new created variables




















5. Click "Edit Script" on Script Task Editor to bring you to go to Visual Studio




















6. Add the script as following: (I'm using C#)

#region Namespaces
using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Net.Mail;  //Add Mail Reference
#endregion

...

public void Main()
{                             
//Get information from variables
String EmailFrom = Dts.Variables["EmailFrom"].Value.ToString();
String EmailTo = Dts.Variables["EmailTo"].Value.ToString();
String EmailSubject = Dts.Variables["EmailSubject"].Value.ToString();
String EmailBody = Dts.Variables["EmailBody"].Value.ToString();
String mySmtpServer = Dts.Connections["My SMTP Server"].Properties["SmtpServer"].GetValue(Dts.Connections["My SMTP Server"]).ToString();

//Create an email and change the format to HTML
MailMessage myHtmlEmail = new MailMessage(EmailFrom, EmailTo, EmailSubject, EmailBody);
myHtmlEmail.IsBodyHtml = true;

//Create a SMTP client to send the email
SmtpClient mySmtpClient = new SmtpClient(mySmtpServer);
//mySmtpClient.Port = 25; //Default Port
mySmtpClient.Send(myHtmlEmail);

Dts.TaskResult = (int)ScriptResults.Success;
}



7. Close Visual Studio and go back to Script Task Editor. Dont forget to click "OK"
8. It's ready to run the script


















9. The result on Email: