error handling

[msdnpublic1234]

Hi,

I have this .NET code which reads email ,username,sharepoint site,library,list,directory from the SSIS valriables which iterate through DTS object storing email and username .

The goal of this code is to upload file for each user onto sharepoint library,lookup a list for email delivery value and email the file to users who have subscribed to email.The issue i face are a couple and i want the code to handle those exception better without erroring out and continue with next iteration.Following are some of the errors:

1. The remote server returned an error: (503) Server Unavailable.

Suspect sharepoint online server?

1. action() running long time without throwing error and entering catch block

context.request timeout was earlier set to 1 hr=36000000 .Guess this is what was holding on to a file without exiting try and entering catch.I have sometime back reduced timeout to 5 mins.Pls suggest if this the cause.?

1. In general,any part of this code throwing error should be just logged on and not break the code/ error out the Upload to sharepoint component and continue with next iteration.

1. Dts.Events.FireError i assume just fires error so i can see it in package execution and not fail the component.Pls confirm.

My requirement:

I want errors being thrown from the code be logged properly and succeed current for iteration and move to next iteration.Typically,face issue with sharepoint uploads/server availability.How do i better use timeout of connection and wait a while,then obtain connection,retry.Pls improvise my code.


using Microsoft.SharePoint.Client;
using System;
using System.IO;
using System.DirectoryServices.AccountManagement;
using System.Net.Mail;
using System.Security;
using System.Text.RegularExpressions;
using System.Threading;

namespace ST_620e186177a9473c85f220e5bd4a8c5a
{

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

public void Main()
{
var site = (string)Dts.Variables["$Package::Site"].Value;
var library = (string)Dts.Variables["$Package::Library"].Value;
var DeliveryParam = (string)Dts.Variables["$Package::List"].Value;
var directory = (string)Dts.Variables["$Package:irectory"].Value;
var filename = (string)Dts.Variables["User::SP_Filename"].Value;
var EmailFlag = "";
var username = (string)Dts.Variables["User::SP_Uname"].Value;

var email = (string)Dts.Variables["User::SP_Email"].Value;

var email_prop = email.Remove(email.IndexOf("@"));
var email_username = username.Remove(username.IndexOf("@"));

string usercheck = CheckUserExist(email_username);
string mystring;

//Needed to check emails without the separator
var user = email_prop.Split(new char[] { '.' }, StringSplitOptions.RemoveEmptyEntries);
if (user.Length > 1)
{
var name = string.Format("{0},{1}", user[1], user[0]);
mystring = name;
}
else
{
var name = user;
mystring = name[0];
}


var path = Path.Combine(directory, filename);
try
{

var documentName = Regex.Replace(filename, "[#%&*:<>?/|]", String.Empty);

var context = new ClientContext(site);

var securePassword = new SecureString();
var password = "gAcredR6";

foreach (char c in password.ToCharArray()) securePassword.AppendChar(c);
context.Credentials = new SharePointOnlineCredentials(@"jdoe@abc.com", securePassword);

var list = context.Web.Lists.GetByTitle(library);
// Migrating from sql table to shareporint for mail delivery parameters
List Deliverylist = context.Web.Lists.GetById(new Guid(DeliveryParam));
CamlQuery camlQuery = new CamlQuery();
camlQuery.ViewXml = "<View>"
+ "<Query>"
+ "<Where><Contains><FieldRef Name='Username' /><Value Type='Text'>" + username.Trim() + "</Value></Contains></Where>"
+ "</Query>"
+ "</View>";
ListItemCollection collListItem = Deliverylist.GetItems(camlQuery);

context.Load(list.RootFolder);
context.Load(
collListItem, items => items.Include(
item => item["Username"],
item => item["Delivery"]));

context.ExecuteQuery();

// Check if collection contains matching row
if (collListItem.Count > 0)
{
foreach (ListItem oListItem in collListItem)
{
EmailFlag = oListItem["Delivery"].ToString().Trim();
}
}
else
{
EmailFlag = "On Demand";
}
context.Load(list.RootFolder);
context.ExecuteQuery();

var url = list.RootFolder.ServerRelativeUrl + "/" + documentName;
var bytes = System.IO.File.ReadAllBytes(Path.Combine(directory, filename));
using (var stream = new MemoryStream(bytes))
{//earlier used 36000000

context.RequestTimeout = 600000;

//Microsoft.SharePoint.Client.File.SaveBinaryDirect(context, url, stream, true);
RetryLoop(() => Microsoft.SharePoint.Client.File.SaveBinaryDirect(context, url, stream, true));

var file = context.Web.GetFileByServerRelativeUrl(url);
var item = file.ListItemAllFields;
context.Load(item);
context.ExecuteQuery();


item["Property"] = mystring;

Thread.Sleep(500);
item.Update();

context.ExecuteQuery();

}
var info = new System.IO.FileInfo(Path.Combine(directory, filename));
Dts.Variables["User::SP_MailSize"].Value = (info.Length / (1024 * 1024)) < 30;
if (EmailFlag == "Email" && (info.Length / (1024 * 1024)) < 30 && usercheck != null)

{

SendMail(email, path, mystring);
}

}
catch (Exception exp)
{
bool fireagain=false;
var message = String.Format("{0} - {1}{2}", exp.Message, path, Environment.NewLine);

System.IO.File.AppendAllText(directory + "Sharepoint.txt", "Date :" + DateTime.Now.ToString() + "\t" + "Message:" + message);

// Dts.Events.FireError(0, "SharePoint Exception", exp.Message, null, 0);
Dts.Events.FireInformation(-1, "Exception", exp.Message, null, 0,ref fireagain);
}
finally
{
Thread.Sleep(200);
}
Dts.TaskResult = (int)ScriptResults.Success;
}

//Need arises due to wrong email address coming from front end
public string CheckUserExist(string samAcc)
{
using (var domainContext = new PrincipalContext(ContextType.Domain, "abc", "jdoesvs", "Abcd1234"))
{
using (var foundUser = UserPrincipal.FindByIdentity(domainContext, IdentityType.SamAccountName, samAcc))
{
if (foundUser != null)
{
return foundUser.EmailAddress;
}
else
return "";
}
}
}

private void SendMail(string email, string path, string user)
{
var directory = (string)Dts.Variables["$Package:irectory"].Value;
try
{
using (var smtp = new SmtpClient("smtp.abc.com"))
{
var message = new MailMessage();

smtp.Timeout = 360000;
message.To.Add(email);
message.Subject = "Automated Sales Reports";
message.From = new MailAddress("FinanceRerport@abc.com");
message.Attachments.Add(new System.Net.Mail.Attachment(path));
message.IsBodyHtml = false;

smtp.Send(message);
message.Dispose();

}
}
catch (Exception exp)
{
var message = String.Format("{0} - {1}{2}", exp.Message, path, Environment.NewLine);

System.IO.File.AppendAllText(directory + "Sales_email.txt", "Date :" + DateTime.Now.ToString() + "\t" + "Message:" + message);
}

}

private void RetryLoop(Action action, int sleep = 400, int retries = 3)
{
var directory = (string)Dts.Variables["$Package:irectory"].Value;
while (true)
{
try
{
action();
break;
}
catch (Exception ex)
{

if(--retries==0)

{
var message = String.Format("Retry: {0} Exception: {1}", retries, ex.Message);
System.IO.File.AppendAllText(directory + "Sharepoint.txt", "Date :" + DateTime.Now.ToString() + "\t" + "Message:" + message);
Dts.Events.FireError(0, "Retry attempts", ex.Message, null, 0);

throw;
}

Thread.Sleep(sleep);
}

}
}

enum ScriptResults
{
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
};


}
}







Continue reading...