Tuesday 15 July 2014

Download Email Attachment using SSIS Script Task

Below are the 2 different methods that can be used to download an Email Attachment using SSIS Script Task.

Method1: Using Exchange Serivce API

  • If there is no exchange service API installed, download and install it from http://www.microsoft.com/en-in/download/details.aspx?id=42951
  • The connection to the Exchange Service will point to your MailBox (the credential used to run the script) by default

the below code identifies the latest mail with Attachment from inbox by checking for a specific Subject and saves the attachment.

            string filePath = "";
            string fileName = "";
            DateTime latestReceivedtime = DateTime.MinValue;
            EmailMessage latestEmail = null;
            try
            {
                //Connect to ExchangeService
                ExchangeService es = new ExchangeService(ExchangeVersion.Exchange2013);
                //es.Credentials = new WebCredentials("User", "Password", "domain");
                //es.AutodiscoverUrl("URL");
                es.Url = new Uri("https://domain/EWS/Exchange.asmx"); //mail.company.com
             
                /* bind the others mailbox looking for using service instance, provided you have access to                                others mail box
                Mailbox mb = new Mailbox(@"firstname.lastname@company.com");
                FolderId fid = new FolderId(WellKnownFolderName.Inbox, mb);
                Folder inbox = Folder.Bind(es, fid);
                 */

                 //  10 mails per page in DESC order
                 ItemView view = new ItemView(10);
                 view.OrderBy.Add(ItemSchema.DateTimeReceived, SortDirection.Descending);
             
                //Find mails
                 FindItemsResults<Item> fir = es.FindItems(WellKnownFolderName.Inbox, "Subject", view);

                 foreach (Item item in fir.Items)
                 {
                   item.Load(); //Load the entire message with attachment
                   EmailMessage email = item as EmailMessage;
                   if (email != null)
                   {
                    if (email.HasAttachments == true && email.Attachments.Count == 1)
                    {
                       if (email.Subject.StartsWith("Subject") == true)
                       {                                                  
                            if (email.DateTimeReceived > latestReceivedtime) //get latest email
                            {
                                latestReceivedtime = email.DateTimeReceived;
                                filePath = Path.Combine(Dts.Variables["User::SourceFolderPath"].Value.ToString()
                                                        , email.DateTimeReceived.Date.ToString("MM.dd.yyyy") + "_" + email.Attachments[0].Name);
                                fileName = email.DateTimeReceived.Date.ToString("MM.dd.yyyy") + "_" + email.Attachments[0].Name.ToString();
                                latestEmail = email;
                            }
                         }
                      }
                   }
                 }

                 //Save attachment
                 if (File.Exists(filePath) == false && filePath != "")
                 {                
                      FileAttachment fileAttachment = latestEmail.Attachments[0] as FileAttachment;
                      fileAttachment.Load(filePath);
                  }                
               

                 Dts.Variables["User::SourceFileName"].Value = fileName;

                 Dts.TaskResult = (int)ScriptResults.Success;
             
            }
            catch (System.Runtime.InteropServices.COMException ex)
            {
                //Unlock variables
                if (Dts.Variables.Locked == true)
                {
                    Dts.Variables.Unlock();
                }
                //An error occurred.
                Dts.Events.FireError(0, "Error occured", ex.Message, String.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;

            }

Method2: Using Outlook API

  • Microsoft Office Outlook need to be installed
  • By default, it points to outlook that is installed on the machine and uses the same credentials as Outlook mail by default does.
below is the sample code that identifies latest Email from a sender and the subject which has an attachment and saves the attachment in a folder.
            Microsoft.Office.Interop.Outlook.Application app = null;
            Microsoft.Office.Interop.Outlook._NameSpace ns = null;
            Microsoft.Office.Interop.Outlook.MAPIFolder inbox = null;
            string filePath = "";
            string fileName = "";
            DateTime latestReceivedtime = DateTime.MinValue;

            try
            {
              app = new Microsoft.Office.Interop.Outlook.Application();
              ns = app.GetNamespace("MAPI");
              //ns.Logon(null, null, false, false);
              inbox = ns.GetDefaultFolder(Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderInbox);
              Microsoft.Office.Interop.Outlook.Items inBoxItems = inbox.Items;
              Microsoft.Office.Interop.Outlook.MailItem newEmail = null;
              // inBoxItems = inBoxItems.Restrict("[Unread] = true");
              Microsoft.Office.Interop.Outlook.MailItem latestEmail = null;

              foreach (object collectionItem in inBoxItems)
              {
                 newEmail = collectionItem as Microsoft.Office.Interop.Outlook.MailItem;
                 if (newEmail != null && newEmail.SenderEmailAddress == "SenderEmail") //newEmail.SenderName == "senderName")  
                 {
                    if (newEmail.Attachments.Count == 1)// && newEmail.ReceivedTime.Date == DateTime.Now.Date)
                    {
                        if(newEmail.Subject.StartsWith("Subject") == true && newEmail.Attachments[1].FileName.StartsWith("FileName") == true)
                        {
                            if (newEmail.ReceivedTime > latestReceivedtime) //get latest email
                            {
                                latestReceivedtime = newEmail.ReceivedTime;
                                filePath = Path.Combine(Dts.Variables["User::SourceFolderPath"].Value.ToString(), newEmail.ReceivedTime.Date.ToString("MM.dd.yyyy") + "_" + newEmail.Attachments[1].FileName);
                                fileName = newEmail.ReceivedTime.Date.ToString("MM.dd.yyyy") + "_" + newEmail.Attachments[1].FileName.ToString();
                                latestEmail = newEmail;                                
                            }
                        }
                    }
                 }
              }
              if (File.Exists(filePath) == false)
              {
                  latestEmail.Attachments[1].SaveAsFile(filePath);
              }
              Dts.Variables["User::SourceFileName"].Value = fileName;

              Dts.TaskResult = (int)ScriptResults.Success;
            }
            catch (System.Runtime.InteropServices.COMException ex)
            {
                //Unlock variables
                if (Dts.Variables.Locked == true)
                {
                    Dts.Variables.Unlock();
                }
                //An error occurred.
                Dts.Events.FireError(0, "Error occured", ex.Message, String.Empty, 0);
                Dts.TaskResult = (int)ScriptResults.Failure;
            }

12 comments:

  1. What namespaces are required?

    ReplyDelete
  2. using System;
    using System.IO;
    using System.Data;
    using Microsoft.SqlServer.Dts.Runtime;
    using System.Windows.Forms;
    using Microsoft.Exchange.WebServices.Data;

    ReplyDelete
  3. Hi,

    In my case email is already saved in a folder and I wanted to get the attachment from email and save it to a folder. Please let me know how can I do that.

    Thanks

    ReplyDelete
    Replies
    1. Hi,

      For my scenario I have to download attachment from webmail account and save it into shared drive location. But the attachment is a hyperlink in the email body says "Download Report". Can you please tell me how to handle this using SSIS. Thanks in advance.

      Delete
    2. same issue did you any solution. please share thank you

      Delete
  4. Hello,
    I'm trying to download a file from the OUTLOOK API I found this code but when I run it I get this error:
    at System.RuntimeMethodHandle.InvokeMethod(Object target, Object[] arguments, Signature sig, Boolean constructor)
    at System.Reflection.RuntimeMethodInfo.UnsafeInvokeInternal(Object obj, Object[] parameters, Object[] arguments)
    at System.Reflection.RuntimeMethodInfo.Invoke(Object obj, BindingFlags invokeAttr, Binder binder, Object[] parameters, CultureInfo culture)
    at System.RuntimeType.InvokeMember(String name, BindingFlags bindingFlags, Binder binder, Object target, Object[] providedArgs, ParameterModifier[] modifiers, CultureInfo culture, String[] namedParams)
    at Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTATaskScriptingEngine.ExecuteScript()
    CODE:
    namespace ST_e501f03a8fba42ee9c07d44533716d5d.csproject
    {

    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {

    public void Main()
    {
    Microsoft.Office.Interop.Outlook.Application app = null;
    Microsoft.Office.Interop.Outlook._NameSpace ns = null;
    Microsoft.Office.Interop.Outlook.MAPIFolder inbox = null;
    string filePath = @"C:\Users\PCT\Desktop\Pruebas\";
    string fileName = "Report.csv";
    DateTime latestReceivedtime = DateTime.MinValue;

    try
    {
    app = new Microsoft.Office.Interop.Outlook.Application();
    ns = app.GetNamespace("MAPI");
    //ns.Logon(null, null, false, false);
    inbox = ns.GetDefaultFolder(Microsoft.Office.Interop.Outlook.OlDefaultFolders.olFolderInbox);
    Microsoft.Office.Interop.Outlook.Items inBoxItems = inbox.Items;
    Microsoft.Office.Interop.Outlook.MailItem newEmail = null;
    // inBoxItems = inBoxItems.Restrict("[Unread] = true");
    Microsoft.Office.Interop.Outlook.MailItem latestEmail = null;

    foreach (object collectionItem in inBoxItems)
    {
    newEmail = collectionItem as Microsoft.Office.Interop.Outlook.MailItem;
    if (newEmail != null && newEmail.SenderEmailAddress == "SenderEmail") //newEmail.SenderName == "senderName")
    {
    if (newEmail.Attachments.Count == 1)// && newEmail.ReceivedTime.Date == DateTime.Now.Date)
    {
    if (newEmail.Subject.StartsWith("Subject") == true && newEmail.Attachments[1].FileName.StartsWith("FileName") == true)
    {
    if (newEmail.ReceivedTime > latestReceivedtime) //get latest email
    {
    latestReceivedtime = newEmail.ReceivedTime;
    filePath = Path.Combine(Dts.Variables["User::SourceFolderPath"].Value.ToString(), newEmail.ReceivedTime.Date.ToString("MM.dd.yyyy") + "_" + newEmail.Attachments[1].FileName);
    fileName = newEmail.ReceivedTime.Date.ToString("MM.dd.yyyy") + "_" + newEmail.Attachments[1].FileName.ToString();
    latestEmail = newEmail;
    }
    }
    }
    }
    }
    if (File.Exists(filePath) == false)
    {
    latestEmail.Attachments[1].SaveAsFile(filePath);
    }
    Dts.Variables["User::SourceFileName"].Value = fileName;

    Dts.TaskResult = (int)ScriptResults.Success;
    }
    catch (System.Runtime.InteropServices.COMException ex)
    .....

    ReplyDelete
  5. Will this approach still work if the user changes his outlook password?

    ReplyDelete
  6. your Email Attachment articale is very informative and Useful,Thanks for sharing such nice article.hop keep sharing Gmail email attachment Extractor

    ReplyDelete
  7. It is very informative blog.Thanks for providing this blog. We have got so much information.
    Gmail Email Attachment Downloader

    ReplyDelete

  8. Really appreciate this wonderful post that you have provided for us.Great site and a great topic as well I really get amazed to read this. It's really good.
    I like viewing web sites which comprehend the price of delivering the excellent useful resource free of charge. I truly adored reading your posting. Thank you!.






    mobile phone repair in Fredericksburg
    iphone repair in Fredericksburg
    cell phone repair in Fredericksburg
    phone repair in Fredericksburg
    tablet repair in Fredericksburg
    mobile phone repair in Fredericksburg
    mobile phone repair Fredericksburg
    iphone repair Fredericksburg
    cell phone repair Fredericksburg
    phone repair Fredericksburg

    ReplyDelete