Send Email with SSIS/DATABASE from gmail account

You had noticed That the Send Email task from the SSIS package does not have the option of indicating a user name and password, it will only authenticate using Windows Authentication.

This SSIS email task does not support Gmail SMTP server. Even it does not support any smtp server with username and password.
Here is one SSIS Script executor package example to send email from gmail account. This is a Substitute of SSIS email Send task.
Steps To Send Email with gmail.

Step1: Create Global variables for SMTP server Configurations.

Step2: Drag and Drop Script Task

Step3: Add variables in ReadOnlyVariables

Step4: Click Edit to Write Script and Select Script Language “Microsoft Visual C# 2008”
So this will be our solution developing our own Send Email Function with option for User Credentials.
Now let’s start.

Step5: Write C# script to send email


Here the script to write:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
using System.Text.RegularExpressions;
using System.Net.Mail;

namespace ST_f36d382ef89848a894adeac409e3a7f6.csproj
{
[System.AddIn.AddIn("ScriptMain", Version = "1.0", Publisher = "", Description = "")]
public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
{

#region VSTA generated code
enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};
#endregion

public void Main()
{
string sSubject = “[Kalyan]:SSIS Test Mail From Gmail”;
string sBody = “Test Message Sent Through SSIS”;
int iPriority = 2;

if (SendMail(sSubject, sBody, iPriority))
{
Dts.TaskResult = (int)ScriptResults.Success;
}
else
{
//Fails the Task
Dts.TaskResult = (int)ScriptResults.Failure;
}
}

public bool SendMail(string sSubject, string sMessage, int iPriority)
{
try
{
string sEmailServer = Dts.Variables["sEmailServer"].Value.ToString();
string sEmailPort = Dts.Variables["sEmailPort"].Value.ToString();
string sEmailUser = Dts.Variables["sEmailUser"].Value.ToString();
string sEmailPassword = Dts.Variables["sEmailPassword"].Value.ToString();
string sEmailSendTo = Dts.Variables["sEmailSendTo"].Value.ToString();
string sEmailSendCC = Dts.Variables["sEmailSendCC"].Value.ToString();
string sEmailSendFrom = Dts.Variables["sEmailSendFrom"].Value.ToString();
string sEmailSendFromName = Dts.Variables["sEmailSendFromName"].Value.ToString();

SmtpClient smtpClient = new SmtpClient();
MailMessage message = new MailMessage();

MailAddress fromAddress = new MailAddress(sEmailSendFrom, sEmailSendFromName);

//You can have multiple emails separated by ;
string[] sEmailTo = Regex.Split(sEmailSendTo, “;”);
string[] sEmailCC = Regex.Split(sEmailSendCC, “;”);
int sEmailServerSMTP = int.Parse(sEmailPort);

smtpClient.Host = sEmailServer;
smtpClient.Port = sEmailServerSMTP;
smtpClient.EnableSsl = true;

System.Net.NetworkCredential myCredentials =
new System.Net.NetworkCredential(sEmailUser, sEmailPassword);
smtpClient.Credentials = myCredentials;

message.From = fromAddress;

if (sEmailTo != null)
{
for (int i = 0; i < sEmailTo.Length; ++i)
{
if (sEmailTo[i] != null && sEmailTo[i] != “”)
{
message.To.Add(sEmailTo[i]);
}
}
}

if (sEmailCC != null)
{
for (int i = 0; i < sEmailCC.Length; ++i)
{
if (sEmailCC[i] != null && sEmailCC[i] != “”)
{
message.To.Add(sEmailCC[i]);
}
}
}

switch (iPriority)
{
case 1:
message.Priority = MailPriority.High;
break;
case 3:
message.Priority = MailPriority.Low;
break;
default:
message.Priority = MailPriority.Normal;
break;
}

message.Subject = sSubject;
message.IsBodyHtml = true;
message.Body = sMessage;

smtpClient.Send(message);
return true;
}
catch (Exception ex)
{
return false;
}
}
}
}

Keeping a session alive on ColdFusion using Jquery

Here is a simple solution to keep a session open on forms where users may need an extended period of time for data entry. You can include this code on any page where you need the session extended. You can remove the javascript include if JQuery is included on all of your pages already.
<!— Include this file on any page where users may take a long time filling in text and you want to keep the session open —>
<cfparam name=”variables.refreshrate” default=”60000″>
<cfoutput>
<script type=”text/javascript” src=”/js/jquery-1.3.2.min.js”></script>
<script language=”JavaScript” type=”text/javascript”>
$(document).ready(function(){
setTimeout(“callserver()”,#variables.refreshrate#);
});
function callserver()
{
var remoteURL = ‘/emptypage.cfm’;
$.get(remoteURL, function(data){
setTimeout(“callserver()”,#variables.refreshrate#);
});
}
</script>
</cfoutput>
In the above code the function callserver will get invoked 60 seconds after the data entry page is loaded. The callserver function will keep refreshing the data entry screen at an interval of 60 seconds with all the data that user have entered till the user submits the form.

Determine default and max session/Application timeouts for current ColdFusion server

ColdFusion provides Admin API to work with admin settings. For example setClientStorage, Cookie Management, Datasource Management etc. In one of my old post, I already provide an example how to add/update/delete a DSN from a code base.This API is mainly used to install/Build an application in one request.
In today’s post we determine how to get session and application scope values.
Usage Scenario: For example we have 3 applications in one ColdFusion server and we set different session timeout values for each application. Now if a developer wants to get sessionTimeOut and MaxSessionTimeOut values of each application.
<cftry>
<cfset adminAPI = createObject(“component”, “CFIDE.adminapi.administrator”)>
<cfset adminAPI.login(“password1$”)><!– Add password of CFadmin —>
<cfset runtimeENV = createObject(“component”, “CFIDE.adminapi.runtime”)>
<cfset sessionDefaultTimeout = runtimeENV.getScopeProperty(“sessionScopeTimeout”)>
<cfset sessionMaxTimeout = runtimeENV.getScopeProperty(“sessionScopeMaxTimeout”)>
<cfset ApplicationMaxTimeout = runtimeENV.getScopeProperty(“ApplicationscopeTimeout”)>
<cfoutput>
Default timeout session: #sessionDefaultTimeout# <br>Max value is #sessionMaxTimeout#.
<br>ApplicationMaxTimeout #ApplicationMaxTimeout#
</cfoutput>
<cfcatch>
<cfdump var=”#cfcatch#”/>
</cfcatch>
</cftry>
Note: This adminAPI will fall in CF8 if application name have space in Application.cfm/Application.cfc