1

I am trying to send an email of a SQL table out in email using a script task in SSIS.

I got everything working but the rows are not aligned properly when the email succeeds. I see that "\t" creates a tab in C # but this is relative to where the last row ended.

So I am looking to see what I need to do so that everything in my second and 3rd row start from the same line regardless of the length of the 1st row. .

End Result looks like this

Failed Results

if (varCollection["User::EmailMessageDEV"].Value == string.Empty)
{
    header = "Load Status Report Dev\n\n";
    header += string.Format("{0}\t\t\t\t{1}\t\t{2}\t\n", "Pkg_Name", "DateRan","Server");
    varCollection["User::EmailMessageDEV"].Value = header;
}

//"{0}\t{1}\t{2}"
//Format the query result with tab delimiters
message = string.Format("{0}\t\t\t\t{1}\t\t{2}\t\n", 
    varCollection["User::Pkg_Name"].Value, 

    varCollection["User::DateRan"].Value, 

    varCollection["User::Server"].Value);

//varCollection["User::ENDDATE"].Value),
//varCollection["User::COMPLETED"].Value);

varCollection["User::EmailMessageDEV"].Value = varCollection["User::EmailMessageDEV"].Value + message;
Filburt
  • 17,626
  • 12
  • 64
  • 115
Centuori
  • 11
  • 1
  • I see two options; First option, build the email as HTML and wrap a table element around your output. Second option will fix what you are currently trying to do, before you build your output you get the Pkg_Name row value that is the longest value in any row, then you use the longest length value to right pad your output values so they are all the same length as the longest, this will then allow your tabbed rows to line up. – quaabaam Jul 29 '21 at 18:16
  • Also, if getting the name with the longest length is not simple (it would depend how your SSIS package works) you can always set it to a hard coded value that is always longer than the longest possible name. Then you ensure all names are right padded with to this length. – quaabaam Jul 29 '21 at 18:29
  • HTML table would probably be your best bet. Otherwise, unless you're using a fixed-width font, it will be tricky to try to pad your columns so they all line up correctly. – Rufus L Jul 29 '21 at 18:38

1 Answers1

2

In order to do what you're asking, you'd need to wrap your entire email's contents in a <pre> HTML tag so that all the characters are the same width, otherwise the varying width of different characters will make it impossible to line things up. Then you'd need to loop over your entire result set and calculate the max string length of each "column". Then you'd need to loop over the entire result set again, outputting the string and the correct number of spaces, determined by subtracting the string's length from the max length string for that column in order to keep things lined up. It'll be a big mess of logic just to do a "simple" thing.

Alternatively, I would save yourself the inefficiency of looping over your data twice and the headache of calculating string lengths in order to get everything to line up, and just use an HTML table in your email. If the data you want to output is tabular, then it should go in a table. I've modified your code as best I could to do this. It's unclear how you're looping over your data, so you'll need to be sure to close the tbody and table elements after the loop is done (see comment in code).

if (varCollection["User::EmailMessageDEV"].Value == String.Empty)
{
    string header = "<h1>Load Status Report Dev</h1>";
    header += String.Format("<table><thead><tr><td>{0}</td><td>{1}</td><td>{2}</td></tr></thead><tbody>", "Pkg_Name", "DateRan", "Server");
    varCollection["User::EmailMessageDEV"].Value = header;
}

// Format the query result into a table row
string message = "<tr>";
string cellFormat = "<td>{0}</td>";
message += String.Format(cellFormat, varCollection["User::Pkg_Name"].Value);
message += String.Format(cellFormat, varCollection["User::DateRan"].Value);
message += String.Format(cellFormat, varCollection["User::Server"].Value);
message += "</tr>";

varCollection["User::EmailMessageDEV"].Value += message;

// ...

// When all rows have been added, close the tbody and table elements
varCollection["User::EmailMessageDEV"].Value += "</tbody></table>";
voltaek
  • 56
  • 3