none
SSIS Script Task - C#: System.UnauthorizedAccessException: 'Access to the path 'myfilepath' is denied.' RRS feed

  • Question

  • First off, I am a beginner when it comes to C# and this is my first question in the Microsoft forums, so please forgive me for my ignorance in certain things. 

    I am trying to create a script task in SSIS that will allow me to download an attachment from an email in O365 and place that attachment into a local folder. I feel like I am very close to getting it working, but I am getting an error at the very end of the code that is telling me that access is being denied to the folder. I have granted full read/write permissions to this folder across every possible user, and I am still getting the error.

    Here is my Code:

     
    using System;
    
    using System.Data;
    
    using Microsoft.SqlServer.Dts.Runtime;
    
    using System.Windows.Forms;
    
    using System.IO;
    
    using Microsoft.Exchange.WebServices.Data;
    
     
    
    namespace ST_ca6ba735ebbb448d8911242c01c9404f.csproj
    
    {
    
        [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    
        public partial class CheckEmail : 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()
    
            {
    
                string filePath = "";
    
                string fileName = "";
    
                DateTime latestReceivedtime = DateTime.MinValue;
    
                EmailMessage latestEmail = null;
    
                try
    
                {
    
                    ExchangeService service = new ExchangeService(ExchangeVersion.Exchange2013_SP1);
    
                    service.Credentials = new WebCredentials("myuser@mydomain.com", "mypassword");
    
                    service.AutodiscoverUrl("myuser@mydomain.com",ValidateRedirectionUrlCallback);
    
                    service.Url = new Uri("https://outlook.office365.com/EWS/Exchange.asmx");
    
                    ItemView view = new ItemView(10);
    
                    view.OrderBy.Add(ItemSchema.DateTimeReceived, SortDirection.Descending);
    
                    FindItemsResults<Item> fir = service.FindItems(WellKnownFolderName.Inbox, "emailpreviewname", view);
    
                    foreach (Item item in fir.Items)
    
                    {
    
                        item.Load(); 
    
                        EmailMessage email = item as EmailMessage;
    
                        if (email != null)
    
                        {
    
                            if (email.HasAttachments == true && email.Attachments.Count > 0)
    
                            {
    
                                if (email.Subject.Contains("emailsubjectline") == true)
    
                                {
    
                                    if (email.DateTimeReceived > latestReceivedtime) 
    
                                    {
    
                                        latestReceivedtime = email.DateTimeReceived;
    
                                        filePath = "C:\\myfolder\\";
    
                                        fileName = email.DateTimeReceived.Date.ToString("MM.dd.yyyy") + "_" + email.Attachments[0].Name.ToString();
    
                                        latestEmail = email;
    
                                    }
    
                                }
    
                            }
    
                        }
    
                    }
    
                    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)
    
                {
    
                    if (Dts.Variables.Locked == true)
    
                    {
    
                        Dts.Variables.Unlock();
    
                    }
    
                    Dts.Events.FireError(0, "Error occured", ex.Message, String.Empty, 0);
    
                    Dts.TaskResult = (int)ScriptResults.Failure;
    
                }
    
            }
    
            private bool ValidateRedirectionUrlCallback(string redirectionUrl)
    
            {
    
                bool result = false;
    
                Uri redirectionUri = new Uri(redirectionUrl);
    
                if (redirectionUri.Scheme == "https")
    
                {
    
                    result = true;
    
                }
    
                return result;
    
            }
    
        }
    
    }




    I created a ton of breakpoints and walked through the code step by step. Everything seems to work fine until I get to the line fileAttachment.Load(filePath);. At that point I get this error:

    System.UnauthorizedAccessException
      HResult=0x80070005
      Message=Access to the path 'C:\myfolder' is denied.
      Source=mscorlib
      StackTrace:
       at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
       at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy, Boolean useLongPath, Boolean checkHost)
       at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, FileOptions options, String msgPath, Boolean bFromProxy)
       at System.IO.FileStream..ctor(String path, FileMode mode)
       at Microsoft.Exchange.WebServices.Data.FileAttachment.Load(String fileName)
       at ST_ca6ba735ebbb448d8911242c01c9404f.csproj.CheckEmail.Main() in c:\Users\myuser\AppData\Local\Temp\5\Vsta\ddc9c4d4e00244648487dfd11010fb18\ScriptMain.cs:line 123

    As I said above, I have granted full read/write access to the folder that I am trying to drop the file in. I am not sure where to take it from here. I don't even know how to identify what user it is trying to write with. Any help would be appreciated.



    Wednesday, October 31, 2018 4:42 PM

Answers

  • Thanks for the reply. I actually sorted this issue out before I had a chance to read your reply. It turned out that I needed to add the fileName to the filePath in order to resolve the issue. I also added some logic in there to make sure I only grab .xlsx files since that is what I am after with this code. Here is the amended code. Everyting above and below the ...s is the same as before. I commented the old code that was replaced and marked the changes wit *s.

    ...
    
                    foreach (Item item in fir.Items)
                    {
                        item.Load(); 
                        EmailMessage email = item as EmailMessage;
                        if (email != null)
                        {
                            if (email.HasAttachments == true && email.Attachments.Count > 0)
                            {
                                if (email.Subject.Contains("emailsubjectline") == true)
                                {
                                    if (email.DateTimeReceived > latestReceivedtime) 
                                    {
                                        latestReceivedtime = email.DateTimeReceived;
                                        filePath = "C:\\myfolder";
                                        fileName = email.DateTimeReceived.Date.ToString("MM.dd.yyyy") + "_" + email.Attachments[0].Name.ToString();
                                            
    *                                   foreach(Attachment attachment in email.Attachments)
    *                                   {
    *                                       fileName = email.DateTimeReceived.Date.ToString("MM.dd.yyyy") + "_" + attachment.Name.ToString();
    *                                       if (fileName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase))
    *                                       {
    *                                           if (File.Exists(filePath) == false && filePath != "")
    *                                           {
    *                                               FileAttachment fileAttachment = attachment as FileAttachment;
    *                                               filePath = Path.Combine(filePath, fileName);
    *                                               fileAttachment.Load(filePath);
    *                                           }
    *                                       }
    *                                   }
                                        latestEmail = email;
                                    }
                                }
                            }
                        }
                    }
    
    
                    //if (File.Exists(filePath) == false && filePath != "")
                    //{
                    //    FileAttachment fileAttachment = latestEmail.Attachments[0] as FileAttachment;
                    //    fileAttachment.Load(filePath);
                    //}
    				
    ...
    I hope someone finds this helpful.

    Wednesday, October 31, 2018 6:21 PM

All replies

  • SSIS doesn't run under your account, it runs under the account the service is configured to run as. That account would need necessary privileges.

    Your code, on that line, is calling Load but the filepath you specified doesn't exist so it'll fail anyway. Your if logic around that code says if the file doesn't exist then load the file. Since the file doesn't exist the load will fail.

    Since you mentioned you're trying to download attachments you should be Save(ing) the attachment, not Load(ing). Since that class doesn't have a save method you'll have to write one yourself. Since the Content property has the file content as a binary you can use File.WriteAllBytes to save it.

    //Not tested
    File.WriteAllBytes(filePath, fileAttachment.Content);

    Note though that the directory path to the file must already exist otherwise it'll fail. So before saving you should verify the target directory already exists and create it if it doesn't.


    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, October 31, 2018 5:34 PM
    Moderator
  • Thanks for the reply. I actually sorted this issue out before I had a chance to read your reply. It turned out that I needed to add the fileName to the filePath in order to resolve the issue. I also added some logic in there to make sure I only grab .xlsx files since that is what I am after with this code. Here is the amended code. Everyting above and below the ...s is the same as before. I commented the old code that was replaced and marked the changes wit *s.

    ...
    
                    foreach (Item item in fir.Items)
                    {
                        item.Load(); 
                        EmailMessage email = item as EmailMessage;
                        if (email != null)
                        {
                            if (email.HasAttachments == true && email.Attachments.Count > 0)
                            {
                                if (email.Subject.Contains("emailsubjectline") == true)
                                {
                                    if (email.DateTimeReceived > latestReceivedtime) 
                                    {
                                        latestReceivedtime = email.DateTimeReceived;
                                        filePath = "C:\\myfolder";
                                        fileName = email.DateTimeReceived.Date.ToString("MM.dd.yyyy") + "_" + email.Attachments[0].Name.ToString();
                                            
    *                                   foreach(Attachment attachment in email.Attachments)
    *                                   {
    *                                       fileName = email.DateTimeReceived.Date.ToString("MM.dd.yyyy") + "_" + attachment.Name.ToString();
    *                                       if (fileName.EndsWith(".xlsx", StringComparison.OrdinalIgnoreCase))
    *                                       {
    *                                           if (File.Exists(filePath) == false && filePath != "")
    *                                           {
    *                                               FileAttachment fileAttachment = attachment as FileAttachment;
    *                                               filePath = Path.Combine(filePath, fileName);
    *                                               fileAttachment.Load(filePath);
    *                                           }
    *                                       }
    *                                   }
                                        latestEmail = email;
                                    }
                                }
                            }
                        }
                    }
    
    
                    //if (File.Exists(filePath) == false && filePath != "")
                    //{
                    //    FileAttachment fileAttachment = latestEmail.Attachments[0] as FileAttachment;
                    //    fileAttachment.Load(filePath);
                    //}
    				
    ...
    I hope someone finds this helpful.

    Wednesday, October 31, 2018 6:21 PM
  • Glad you have it working but note your if statement is still not right. File.Exists will never be true since you're passing a directory name each time. And the second condition would never not be true either so you could remove that entire if statement and the code will behave as it does now.

    Michael Taylor http://www.michaeltaylorp3.net

    Wednesday, October 31, 2018 6:27 PM
    Moderator