ssis_send_email_from_email_tool_table.cs

using System;
using ADODB; //you wil need to go to the references and add ado
namespace ST_a3f1080b1dda4f098e33c979fc1015cf
{
    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
        #region vdbc#
        public const string postgresConnStringTest = "DRIVER={PostgreSQL ANSI};DATABASE=ERP_TEST;SERVER=10.1.202.88;UID=pgservice;PWD=my_pa$$w0RD";  // test
        public const string postgresConnString     = "PostgreSQL30";                                                                                     // live
        public const string SQLServerProd2016      = "Driver={SQL Server};Server=127.0.0.1;Database=DB;Trusted_Connection=True;";                 // sql 16
        public object           nRecordsAffected   = Type.DefaultBinder;
        public object           oParams            = Type.Missing;
        public ADODB.Connection ADOConn            = new ADODB.Con
        nection();
        public ADODB.Recordset  ADOrec             = new ADODB.Recordset();
        public ADODB.Recordset  rs                 = new ADODB.Recordset();
        public ADODB.Command    ADOcom             = new ADODB.Command();
        public object rc;

        public void open_ADOConn(string connection_String = postgresConnString)
        {
            ADODB.Connection conn;
            conn = new ADODB.Connection();
            conn.ConnectionString = connection_String;
            conn.Open();
            ADOConn = conn;
        }

        public void open_ADORec(string Native_SQL)
        {
            ADODB.Recordset rec = new ADODB.Recordset();
            rec.LockType         = LockTypeEnum.adLockReadOnly;
            rec.CursorType       = CursorTypeEnum.adOpenKeyset; // adOpenKeyset
            rec.ActiveConnection = ADOConn;
          //  rec.Source           = Native_SQL;
            
            rec.Open(Native_SQL,ADOConn, CursorTypeEnum.adOpenKeyset, LockTypeEnum.adLockReadOnly,-1);


            ADOrec = rec;
            rs = ADOConn.Execute(Native_SQL, out rc, (int)ADODB.CommandTypeEnum.adCmdText);
        }
        public void adoCommand(string command_text, string connection_String = postgresConnString)
        {
            ADODB.Command com = new ADODB.Command();
            open_ADOConn(connection_String);
            // ADOcom = CreateObject("ADODB.Command") 'late binding
            com.ActiveConnection = ADOConn;
            com.CommandType      = (CommandTypeEnum)1; // adcmdtext
            com.CommandText      = command_text;
            com.Execute(out nRecordsAffected, ref oParams, (int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
            //  com.Execute();
            close_ADOconn();
        }

        public void adoCommandHoldrec(string command_text, string connection_String = postgresConnString)
        {
            open_ADOConn(connection_String); // we set our connection string incase we arnt using postgres
                                             // ADOcom = CreateObject("ADODB.Command")
            ADOcom.ActiveConnection = ADOConn;
            ADOcom.CommandType      = (CommandTypeEnum)1; // adcmdtext
            ADOcom.CommandText      = command_text;
            ADOrec                  = ADOcom.Execute(out nRecordsAffected, ref oParams, (int)ADODB.ExecuteOptionEnum.adExecuteNoRecords);
        }

        public string adolookup(string Native_SQL, string connection_String = postgresConnString)
        {
            string adolookup;
            open_ADOConn(connection_String); // database connection layer
            open_ADORec(Native_SQL);            // create a recordset for our data
            if (ADOrec.BOF && ADOrec.EOF)
                adolookup = "0";                         // return 0
            else
            // adolookup = ADOrec.;// return the first tuple of the first column
            adolookup = rs.Collect[0].ToString();// return the first tuple of the first column
            close_ADOrec();                                  // close the recordset
            close_ADOconn();                                 // close the connection
            return adolookup;
        }
        public bool this_ado_rec_is_empty(string Native_SQL, string connection_String = postgresConnString)
        {
            bool this_ado_rec_is_empty;
            open_ADOConn(connection_String);
            open_ADORec(Native_SQL);

            if (ADOrec.EOF & ADOrec.BOF)
                this_ado_rec_is_empty = true;
            else
                this_ado_rec_is_empty = false;

            close_ADOrec();
            close_ADOconn();
            return this_ado_rec_is_empty;
        }




        public void close_ADOrec()
        {
            if (!(ADOrec == null))
            {
                ADOrec.Close();
                ADOrec = null;
            }
        }

        public void close_ADOconn()
        {
            if (!(ADOConn == null))
            {
                ADOConn.Close();
                ADOConn = null;
            }
        }
        #endregion
        public void Main()
        {


            open_ADOConn();
            open_ADORec(@"
                        SELECT
                            email_tool.who_from
                          , email_tool.who_to
                          , email_tool.who_cc
                          , email_tool.who_bcc
                          , email_tool.subject
                          , email_tool.body
                          , email_tool.email_sent
                        FROM it.email_tool
                        WHERE email_tool.email_sent = FALSE;
                       ");

            if (ADOrec.EOF == true && ADOrec.BOF == true)
            {

            }
            else
            {
                if (ADOrec.BOF == false)
                {
                    ADOrec.MoveFirst();
                }
                while (ADOrec.EOF == false)
                {
                    SendEmail(ADOrec.Collect[0].ToString(), ADOrec.Collect[1].ToString(), ADOrec.Collect[2].ToString(), ADOrec.Collect[3].ToString(), ADOrec.Collect[4].ToString(), ADOrec.Collect[5].ToString());
                    ADOrec.MoveNext();
                }
                adoCommand("SELECT it.email_tool_sent_update();");
            }






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

        public static void SendEmail(string sFrom = "noreply@my_company.com", string sRecepient = "it@my_company.com", string sCC = "dvandenbosch@my_company.com", string sBCC = "dvandenbosch@my_company.com", string sSubject = "uhhhhh yep", string sBody = "Ryan Ross is a handsome man.")
        {
            System.Net.Mail.MailMessage message = new System.Net.Mail.MailMessage();
            message.To.Add(sRecepient);
            if (sCC != "")  {message.CC.Add(sCC);}
            if (sBCC != "") {message.CC.Add(sBCC);}
            message.Subject = sSubject;
            message.IsBodyHtml = true;
            message.From = new System.Net.Mail.MailAddress(sFrom);
            message.Body = sBody;
            System.Net.Mail.SmtpClient smtp = new System.Net.Mail.SmtpClient("spmx2.my_company.com");
            smtp.Send(message);
        }
    }

    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    }
}