SSIS - REST API用于Freshdesk

Question

我试着在这里搜索一些东西，但似乎没有任何东西适合我的需要。我创建了一个SSIS包，运行一个报表，->将它附加到一封电子邮件中，并多次发送给一群人(不同的收件人，不同的文件)。

由于Zapier的限制，我不能创建带有附件的FreshDesk票据，这对我来说是必须的，所以我正在探索FreshDesk API，但我不是c#开发人员。我在网上找到了一些例子，现在我正在尝试在我现有的代码中加入这个代码：FreshSamples C-夏普创建带有附件的票据，希望将我的所有变量作为票证字段来传递

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


    public void Main()
    {

        // initialize StreamReader class for text file
        StreamReader streamReader = new StreamReader(Dts.Variables["User::MHTMailPath"].Value.ToString());
        // Read the StreamReader To End and assign to local variable
        string StreamText = streamReader.ReadToEnd();
        // assign SSIS variable with value of StreamText local variable.
        this.Dts.Variables["User::HTMLMail"].Value = StreamText;

        // TODO: Add your code here
        MailMessage email = new MailMessage();



        if ((Dts.Variables["User::MHTEmail1"].Value.ToString() != null) && (Dts.Variables["User::MHTEmail1"].Value.ToString() != string.Empty))
        {
            email.To.Add(Dts.Variables["User::MHTEmail1"].Value.ToString());
        }
        if ((Dts.Variables["User::MHTEmail2"].Value.ToString() != null) && (Dts.Variables["User::MHTEmail2"].Value.ToString() != string.Empty))
        {
            email.To.Add(Dts.Variables["User::MHTEmail2"].Value.ToString());
        }
        if ((Dts.Variables["User::MHTEmail3"].Value.ToString() != null) && (Dts.Variables["User::MHTEmail3"].Value.ToString() != string.Empty))
        {
            email.To.Add(Dts.Variables["User::MHTEmail3"].Value.ToString());
        }
        if ((Dts.Variables["User::MHTEmail4"].Value.ToString() != null) && (Dts.Variables["User::MHTEmail4"].Value.ToString() != string.Empty))
        {
            email.To.Add(Dts.Variables["User::MHTEmail4"].Value.ToString());
        }

        //email.CC.Add(CCAddresses);
        email.From = new MailAddress("xxx@xxx.com");
        email.Subject = Dts.Variables["User::MHTCd"].Value.ToString() + " - " + Dts.Variables["User::MHTCustomerDS"].Value.ToString() + " R" + Dts.Variables["User::Period"].Value.ToString().Trim().Substring(Dts.Variables["User::Period"].Value.ToString().Trim().Length - 2, 2);
        email.IsBodyHtml = true;
        email.Body = Dts.Variables["User::HTMLMail"].Value.ToString(); 
        string reportFile = Dts.Variables["User::ReportFile"].Value.ToString();
        try
        {
            //For MHT file. Decode MHTML to HTML and embed in email body
            if (Path.GetExtension(reportFile) == ".mht")
            {
                var decodedHtml = new StringBuilder();
                using (var reader = new StreamReader(reportFile))
                {
                    while (!reader.EndOfStream)
                    {
                        var line = reader.ReadLine();
                        if (line != "Content-Transfer-Encoding: base64") continue;

                        reader.ReadLine();
                        while ((line = reader.ReadLine()) != String.Empty)
                            if (line != null)
                                decodedHtml.Append(
                                    Encoding.UTF8.GetString(
                                        Convert.FromBase64String(line)));
                        break;
                    }
                }
                email.Body = email.Body + Environment.NewLine + decodedHtml.ToString();
                email.IsBodyHtml = true;
            }

            else
            {
                //Attach the file
                Attachment attachmentFile = new Attachment(reportFile);
                email.Attachments.Add(attachmentFile);
            }
        }
        catch (Exception e)
        {
            Dts.Events.FireError(0, "create email message", e.Message, String.Empty, 0);
            Dts.TaskResult = (int)ScriptResults.Failure;
        }

        //System.Diagnostics.Process proc = new System.Diagnostics.Process();
        SmtpClient smtpClient = new SmtpClient("smtp.gmail.com", 587);
        smtpClient.EnableSsl = true;
        smtpClient.Credentials = new System.Net.NetworkCredential("xxx@xxx.com", "xxxxxxx");
        System.Diagnostics.Process proc = new System.Diagnostics.Process();
        try
        {
            smtpClient.Send(email);
            //email.Attachments.Dispose();
            //File.Delete(reportFile);
            Dts.TaskResult = (int)ScriptResults.Success;
        }
        catch (Exception e)
        {
            Dts.Events.FireError(0, "smtp emailing", e.Message, String.Empty, 0);
            Dts.TaskResult = (int)ScriptResults.Failure;
        }
        finally
        {
            if (email != null)
            {
                email.Dispose();
            }
            if (smtpClient != null)
            {
                //smtpClient.Dispose();
            }
        }
        Dts.TaskResult = (int)ScriptResults.Success;
    }

}

但是，我正在失去理智，试图让我的reportFile被FreshDesk示例正确地“获取”，该示例期望文件系统上有一个文件。此外，我需要附加更多的文件，所以我想知道是否有任何好的撒马利亚人会给我指明正确的方向。

提前感谢

任

Answer 1

可以多次调用email.Attachments.Add()添加多个附件：

关键部分是：

若要将现有文件转换为附件：

    Attachment attachmentFile = new Attachment(reportFile);
    email.Attachments.Add(attachmentFile);

要将字符串块转换为附件，请执行以下操作：

using (var stream = new MemoryStream())
using (var writer = new StreamWriter(stream))
{
    writer.Write(reportFile);
    writer.Flush();
    stream.Position = 0;

    email.Attachments.Add(new Attachment(stream, "myreport-xyz.txt", "text/plain"));
}