Tuesday, January 29, 2013

SSIS Checking File/Folder Permissions

Recently I've been working on optimizing some SSIS packages. Part of this optimization was to delete files once they have been processed. I already knew the Proxy account had access to read the files from a directory but wasn't quite sure if the account had permissions to delete files. So Script Task to the rescue...

Below you will find the code that I've used to check if the user does have the required permissions (ReadData and Delete) to perform the tasks it's required to do. The script task is the first item in the control flow as I don't want all the other workflows to be executed if the permission isn't there to remove the files.

  25:          public void Main()
  26:          {
  27:              string path = Dts.Variables["inpSourceDirectory"].Value.ToString();
  28:              // Use WindowsIdentity as ssis packages run under a credential so will pick up the user executing the package
  29:              string NTUser = System.Security.Principal.WindowsIdentity.GetCurrent().Name;
  30:   
  31:              Boolean HasReadData = false, HasDelete = false;
  32:   
  33:              try
  34:              {
  35:                  DirectoryInfo di = new DirectoryInfo(path);
  36:   
  37:                  // Check the folder actually exists
  38:                  if (!di.Exists)
  39:                      throw new System.IO.DirectoryNotFoundException("The folder " + path + "does not exist. Check the folder path variable is correct");
  40:   
  41:                  // Directory Security throws a PrivilegeNotHeldException if AccessControlSections is All so use Access
  42:                  DirectorySecurity ds = di.GetAccessControl(AccessControlSections.Access);
  43:                  AuthorizationRuleCollection rules = ds.GetAccessRules(true, true, typeof(System.Security.Principal.NTAccount));
  44:   
  45:                  // Go through the rules returned from the directory security
  46:                  #region Authorization Rules
  47:   
  48:                  foreach (AuthorizationRule authorizationRule in rules)
  49:                  {
  50:                      // We're only interested in the current NTUser
  51:                      if (authorizationRule.IdentityReference.Value.Equals(NTUser, StringComparison.CurrentCultureIgnoreCase))
  52:                      {
  53:                          // Assign Rules
  54:                          FileSystemAccessRule CheckRules = (FileSystemAccessRule)authorizationRule;
  55:   
  56:                          foreach (string right in Enum.GetNames(typeof(FileSystemRights)))
  57:                          {
  58:                              #region ReadData Right
  59:                              // Check if NTuser has ReadData File System Right
  60:                              if (right == "ReadData")
  61:                              {
  62:                                  int val = Convert.ToInt32(Enum.Parse(typeof(FileSystemRights), right));
  63:                                  // remove combined values
  64:                                  if ((val != 0x1f01ff) && (val != 0x301bf) && (val != 0x20089) && (val != 0x200a9) && (val != 0x116))
  65:                                  {
  66:                                      if (((int)CheckRules.FileSystemRights & val) > 0)
  67:                                          HasReadData = true;
  68:                                  }

QR: Inline image 1

Posted via email from Jasper-Net