none
In SSIS,How to read the data from Object and mail in the form of Table RRS feed

  • Question

  • HI,

    My main objective is:

    1. To read the object which I get through Execite Sql task

    2. Then  Using Script task I want to mail it ,but I need to format the values in object in form of table as the body of mail.

    3. And IF Time TimeDifference Column had value =<100 Then Row should be Green Else Red.


    So I have an Object called "ResultSet",I pass to Script Task, I convert it into c# table structure and place it in a variable called "ApplicationTotal". Below is the code for that which works fine.

    public void Main()
    {
                DataTable dtTotal = new DataTable();
                OleDbDataAdapter adapter = new OleDbDataAdapter();
                DataTable dt = new DataTable();

                adapter.Fill(dt, Dts.Variables["InactiveSet"].Value);

                // In the first Run dtTotal is created
                if (Convert.ToInt32(Dts.Variables["InsertedRowCountTotal"].Value) == 0)
                {
                    foreach (DataColumn dc in dt.Columns)
                    {
                       dtTotal.Columns.Add(dc.ColumnName,dc.DataType);
                    }
                }
                else // In the next runs dtTotal is retrieved from variable
                {
                    dtTotal = (DataTable)Dts.Variables["InactiveSetTotal"].Value;
                }

                foreach (DataRow dr in dt.Rows)
                {
                    DataRow newDR = dtTotal.NewRow();
                    
                    foreach( DataColumn dc in dt.Columns)
                    {
                        newDR[dc.ColumnName] = dr[dc.ColumnName];
                    }

                    dtTotal.Rows.Add(newDR);
                }

                Dts.Variables["InactiveSetTotal"].Value = dtTotal;
                Dts.Variables["InsertedRowCountTotal"].Value = Convert.ToInt32(Dts.Variables["InsertedRowCountTotal"].Value)  + Convert.ToInt32(Dts.Variables["InsertedRowCount"].Value);

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

    Then I pass ApplicationTotal object to script task where i have to read the object and mail the details in form of table.I am successful in sending mail but I am not able to format the data in table and change the colour

    I get the output in mail as 

    enter image description here

     if (Convert.ToInt32(Dts.Variables["InsertedRowCount"].Value) == 0)
            {
                return;
            }
    
    
            #region BuildingEmailBody
    
            StringBuilder sb = new StringBuilder();
            sb.AppendLine(string.Format("Monitor Application Report"));
            sb.AppendLine();
            sb.AppendLine();
            //sb.AppendLine(string.Format("Following are the Details:\n\n<TABLE><TR>\n<TH>{0}</TH><TH>{1}</TH><TH>{2}</TH> <TH>{3}</TH> <TH>{4}</TH>\n</TR>\n", "TimeDifferences(Minutes) ", "UpdateTime", "ApplicationName", "ServerName", "DatabaseName"));
    
            OleDbDataAdapter adapter = new OleDbDataAdapter();
    
            if (Convert.ToInt32(Dts.Variables["InsertedRowCount"].Value) > 0)
            {
                sb.AppendLine();
                DataTable ApplicationTotal = new DataTable();
                ApplicationTotal = (DataTable)Dts.Variables["ApplicationTotal"].Value;
    
                foreach (DataRow dr in ApplicationTotal.Rows)
                {
                    sb.AppendLine(string.Format("{0}     {1}      {2}      {3}        {4}  ", dr[0], dr[1], dr[2], dr[3], dr[4]));
                }
    
    
    
    
                sb.AppendLine();
            }
    
    
            # endregion


    Thursday, October 20, 2016 1:04 AM

Answers

  •         public void Main()
            {
                string html = string.Empty;
               if (Convert.ToInt32(Dts.Variables["InsertedRowCount"].Value) == 0)
                {
                    return;
                }
    
    
                #region BuildingEmailBody
              
    
                OleDbDataAdapter adapter = new OleDbDataAdapter();
    
                if (Convert.ToInt32(Dts.Variables["InsertedRowCount"].Value) > 0)
                {
                    DataTable ApplicationTotal = new DataTable();
                    ApplicationTotal = (DataTable)Dts.Variables["ApplicationTotal"].Value;
    
    
                    html = "Monitor Application Report <br></br>  <style type='text/css'>td.datacellone {	background-color: #FF0000; color: black;}td.datacelltwo {	background-color: 	#00FF00; color: black;}</style> <table border=1>";
                    //add header row
                    html += "<tr>";
                    for (int i = 0; i < ApplicationTotal.Columns.Count; i++)
                    {
                        html += "<th>" + ApplicationTotal.Columns[i].ColumnName + "</th>";
                    }
                    html += "</tr>";
                    //add rows
                    for (int i = 0; i < ApplicationTotal.Rows.Count; i++)
                    {
                        html += "<tr>";
                        for (int j = 0; j < ApplicationTotal.Columns.Count; j++)
                            if (Convert.ToInt32(ApplicationTotal.Rows[i][0].ToString()) > Convert.ToInt32(Dts.Variables["TimeDifference"].Value.ToString()))
                               html += "<td class='datacellone'>" + ApplicationTotal.Rows[i][j].ToString() + "</td>";
                          else
                                html += "<td class='datacelltwo'>" + ApplicationTotal.Rows[i][j].ToString() + "</td>";
                        
    
                        html += "</tr>";
                    }
                    html += "</table>";
    
                            
                }
    
                //MessageBox.Show(Dts.Variables["TimeDifference"].Value.ToString());
                # endregion
    
                #region SendingEmail
    
                string sendTo = Dts.Variables["AlarmOperator"].Value.ToString();
                string from = "pemsadmin@pemsportal.com.au";
                string subject = "Monitor Application Status";
    
                string server = "192.168.240.171";
                string user = "pemsadmin@pemsportal.com.au";
                string password = "Sawu7619";
                string domain = "pemsportal.com.au";
                int port = 25;
               
                System.Net.Mail.MailMessage message = new System.Net.Mail.MailMessage(from, sendTo, subject, html.ToString());
                
                message.IsBodyHtml = true;
                message.Body = html.ToString();
                System.Net.Mail.SmtpClient smpt = new System.Net.Mail.SmtpClient(server, port);
                smpt.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
                smpt.Credentials = new System.Net.NetworkCredential(user, password, domain);
                            
                smpt.Send(message);
                
                #endregion
    
    
                Dts.TaskResult = (int)ScriptResults.Success;
            }

    Friday, October 21, 2016 3:36 AM
  • In the above code  ,

    1. I have taken a variable html in which I create table and load the data in the table.

    2. I add the color coding to the table's rows based on condition 

    3.Make IsBodyHtml =true

    4.Pass html variable to smpt.Send(message)

    Friday, October 21, 2016 3:39 AM

All replies

  • I didn't get it ,Can you give an example that how I can write in my code
    Thursday, October 20, 2016 5:04 AM
  • There is not direct way of getting HTML formatted email from SQL Server. T-SQL support HTML codding. You can have script task and in that you can provide T-SQL script for HTML formatted report.

    Refer - https://www.mssqltips.com/sqlservertip/4032/generate-html-formatted-emails-from-sql-server/


    Regards,
    Rohit Garg
    (My Blog)
    This posting is provided with no warranties and confers no rights.
    Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.

    Thursday, October 20, 2016 8:57 AM
  • But I want to use script task where I pass My object which contain data 
    Thursday, October 20, 2016 10:07 PM
  •         public void Main()
            {
                string html = string.Empty;
               if (Convert.ToInt32(Dts.Variables["InsertedRowCount"].Value) == 0)
                {
                    return;
                }
    
    
                #region BuildingEmailBody
              
    
                OleDbDataAdapter adapter = new OleDbDataAdapter();
    
                if (Convert.ToInt32(Dts.Variables["InsertedRowCount"].Value) > 0)
                {
                    DataTable ApplicationTotal = new DataTable();
                    ApplicationTotal = (DataTable)Dts.Variables["ApplicationTotal"].Value;
    
    
                    html = "Monitor Application Report <br></br>  <style type='text/css'>td.datacellone {	background-color: #FF0000; color: black;}td.datacelltwo {	background-color: 	#00FF00; color: black;}</style> <table border=1>";
                    //add header row
                    html += "<tr>";
                    for (int i = 0; i < ApplicationTotal.Columns.Count; i++)
                    {
                        html += "<th>" + ApplicationTotal.Columns[i].ColumnName + "</th>";
                    }
                    html += "</tr>";
                    //add rows
                    for (int i = 0; i < ApplicationTotal.Rows.Count; i++)
                    {
                        html += "<tr>";
                        for (int j = 0; j < ApplicationTotal.Columns.Count; j++)
                            if (Convert.ToInt32(ApplicationTotal.Rows[i][0].ToString()) > Convert.ToInt32(Dts.Variables["TimeDifference"].Value.ToString()))
                               html += "<td class='datacellone'>" + ApplicationTotal.Rows[i][j].ToString() + "</td>";
                          else
                                html += "<td class='datacelltwo'>" + ApplicationTotal.Rows[i][j].ToString() + "</td>";
                        
    
                        html += "</tr>";
                    }
                    html += "</table>";
    
                            
                }
    
                //MessageBox.Show(Dts.Variables["TimeDifference"].Value.ToString());
                # endregion
    
                #region SendingEmail
    
                string sendTo = Dts.Variables["AlarmOperator"].Value.ToString();
                string from = "pemsadmin@pemsportal.com.au";
                string subject = "Monitor Application Status";
    
                string server = "192.168.240.171";
                string user = "pemsadmin@pemsportal.com.au";
                string password = "Sawu7619";
                string domain = "pemsportal.com.au";
                int port = 25;
               
                System.Net.Mail.MailMessage message = new System.Net.Mail.MailMessage(from, sendTo, subject, html.ToString());
                
                message.IsBodyHtml = true;
                message.Body = html.ToString();
                System.Net.Mail.SmtpClient smpt = new System.Net.Mail.SmtpClient(server, port);
                smpt.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
                smpt.Credentials = new System.Net.NetworkCredential(user, password, domain);
                            
                smpt.Send(message);
                
                #endregion
    
    
                Dts.TaskResult = (int)ScriptResults.Success;
            }

    Friday, October 21, 2016 3:36 AM
  • In the above code  ,

    1. I have taken a variable html in which I create table and load the data in the table.

    2. I add the color coding to the table's rows based on condition 

    3.Make IsBodyHtml =true

    4.Pass html variable to smpt.Send(message)

    Friday, October 21, 2016 3:39 AM