c# - 如何使用 ssis 发送表格式的 sql 输出

Question

我需要运行 sql 查询并使用 SSIS 2008 以表格格式在 Outlook 邮件中获取输出。

我设计了 SSIS 包并在 C# 中编写了以下代码以获取查询输出，但我无法以表格格式对齐数据。

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;

namespace ST_7f59d09774914001b60a99a90809d5c5.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()
        {

            Variables varCollection = null;
            string header = string.Empty;
            string message = string.Empty;
            var colSpace = 4;

            Dts.VariableDispenser.LockForWrite("User::EmailMessage");
            Dts.VariableDispenser.LockForWrite("User::Job_Name");
            Dts.VariableDispenser.LockForWrite("User::Start_DateTime");
            Dts.VariableDispenser.LockForWrite("User::End_DateTime");
            Dts.VariableDispenser.LockForWrite("User::Run_Duration");
            Dts.VariableDispenser.LockForWrite("User::Job_Status");
            Dts.VariableDispenser.GetVariables(ref varCollection);

            //Set the header message for the query result
            if (varCollection["User::EmailMessage"].Value == string.Empty)
            {
                header = "Execute SQL task output sent using Send Email Task in SSIS:\n\n";
                header += string.Concat("Job_Name".PadRight(40 + colSpace), "Start_DateTime".PadRight(15 + colSpace), "End_DateTime".PadRight(15 + colSpace), "Run_Duration".PadRight(15 + colSpace), "Job_Status".PadRight(100 + colSpace));
                varCollection["User::EmailMessage"].Value = header;
            }

            //Format the query result with tab delimiters
            message = string.Format("{0}\t{1}\t{2}",
                                        varCollection["User::Job_Name"].Value,
                                        varCollection["User::Start_DateTime"].Value,
                                        varCollection["User::End_DateTime"].Value,
                                        varCollection["User::Run_Duration"].Value,
                                        varCollection["User::Job_Status"].Value);

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

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

我需要邮件中表格格式的四列输出表。

标签： c#htmlsql-serveremailssis