SSIS Script Task Email with SqlConnection Logging / VB.Net

On a earlier post I talked about using a Foreach loop container to send dynamic emails. I finally was able to wrap up the project and decided to take it a little bit more by adding a logging method in the script task. Below is my code sample for getting values from ForEach Loop Container, creating the email and logging the sent email into sql server via a SqlConnection.

Sample Code:

Public Sub Main()
Dim EmailTypeID As Integer
Dim EmployeeID As String
Dim SalesID As String
Dim SamAccount As String
Dim RehireDate As String
Dim ServiceDate As String
Dim FromAddress As String
Dim ToAddress As String
Dim Subject As String
Dim Body As String
Dim Name As String

Try
' Log properties
EmailTypeID = Dts.Variables("User::EmailTypeID").Value
EmployeeID = Dts.Variables("User::EmployeeID").Value.ToString
SalesID = Dts.Variables("User::SalesID").Value.ToString
SamAccount = Dts.Variables("User::SAMAccount").Value.ToString
RehireDate = Dts.Variables("User::RehireDate").Value.ToString
ServiceDate = Dts.Variables("User::ServiceDate").Value.ToString

' Email properties
FromAddress = Dts.Variables("User::MailFromAddress").Value.ToString
ToAddress = Dts.Variables("User::MailToAddress").Value.ToString
Subject = Dts.Variables("User::MailSubject").Value.ToString
Body = Dts.Variables("User::MailBody").Value.ToString
Name = Dts.Variables("User::MailName").Value.ToString

If SendMail(FromAddress, ToAddress, Subject, Body, Name) = True Then
LogEmail(EmailTypeID, EmployeeID, SalesID, SamAccount, ToAddress, RehireDate, ServiceDate)
Dts.TaskResult = ScriptResults.Success
End If

Catch ex As Exception
ErrorEmail(ex)
Dts.TaskResult = ScriptResults.Failure
End Try
End Sub

Public Function SendMail(ByVal FromAddress As String, ByVal ToAddress As String, ByVal Subject As String, ByVal Body As String, ByVal Name As String) As Boolean
Dim myHtmlMessage As MailMessage
Dim mySmtpClient As SmtpClient
Dim Body2 As String
Dim NewHireUrl As String

'MsgBox("Send, started")
NewHireUrl = "http://msn.com"

Body2 = "Dear: " & Name & _
vbNewLine & vbNewLine & _
" message "

myHtmlMessage = New MailMessage(FromAddress, ToAddress, Subject, Body2)
mySmtpClient = New SmtpClient("smtp.server.com")
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(myHtmlMessage)
'MsgBox("Send, completed")
Return True

End Function

Public Function LogEmail(ByVal EmailTypeID As Integer, ByVal EmployeeID As String, ByVal SalesID As String, ByVal SamAccount As String, ByVal ToAddress As String, ByVal RehireDate As String, ByVal ServiceDate As String) As Boolean
Dim oCnn As New Data.SqlClient.SqlConnection
Dim sSQL As String
oCnn.ConnectionString = "Server=xxxx;Database=xxxx;User ID=user;Password=pass;Trusted_Connection=False"
sSQL = "insert into dbo.EmployeeEmail (DimEmailTypeKey, EmployeeID, SalesID, SamAccount, EmailAddress, DateOfEmail, RehireDate, ServiceDate, AuditKey) "
sSQL += "Values (@EmailTypeID, @EmployeeID, @SalesID, @SamAccount, @EmaillAddress, @DateOfEmail, @RehireDate, @ServiceDate, @AuditKey) "
Dim oCmd As SqlCommand = New SqlCommand(sSQL, oCnn)
oCmd.Parameters.Add("@EmailTypeID", SqlDbType.Int).Value = EmailTypeID
oCmd.Parameters.Add("@EmployeeID", SqlDbType.VarChar).Value = EmployeeID
oCmd.Parameters.Add("@SalesID", SqlDbType.VarChar).Value = SalesID
oCmd.Parameters.Add("@SamAccount", SqlDbType.VarChar).Value = SamAccount
oCmd.Parameters.Add("@EmaillAddress", SqlDbType.VarChar).Value = ToAddress
oCmd.Parameters.Add("@DateOfEmail", SqlDbType.VarChar).Value = Date.Now.ToString()
oCmd.Parameters.Add("@RehireDate", SqlDbType.VarChar).Value = RehireDate
oCmd.Parameters.Add("@ServiceDate", SqlDbType.VarChar).Value = ServiceDate
oCmd.Parameters.Add("@AuditKey", SqlDbType.VarChar).Value = 1

oCmd.CommandType = CommandType.Text
oCmd.Connection = oCnn
oCnn.Open()
oCmd.ExecuteNonQuery()
oCnn.Close()
Return False

End Function

Public Sub ErrorEmail(ByVal Exception As Exception)
Dim myHtmlMessage As MailMessage
Dim mySmtpClient As SmtpClient

myHtmlMessage = New MailMessage("admin@email.com", "admin@email.com", "Email Error", Exception.ToString())
mySmtpClient = New SmtpClient("smtp.server.com")
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(myHtmlMessage)
End Sub

SSRS ReportExecution2005.asmx call with code/ C# / https

As stated in my prior post I have been working on InfoPath Forms Services. During the development of a form I wanted to attach a pdf version of the form in an email during submission. I have researched several ways to accomplish this task and the solution I used was to call reporting services. This call would allow me to return a memorystream and attach it to my email in code. In my case the difficult part was to make sure the call worked from both https and http calls. One key note to take into consideration is to make the web service url call directly from the Forms Collection your are currently in. This will ensure the proper creds are passed and "DefaultCreds" will work. Below I have provided a code sample on how to make the call and return a pdf in a memory stream.

Report Server Web Service Endpoints

Sample of Code:
private MemoryStream FormSubmitGetReportAttachment()
{
try
{

// Render arguments
byte[] result = null;
string reportPath = "http://moss/Reports/Sales/Review.rdl";
string format = "PDF";
string historyID = null;
string devInfo = @"False";
// Prepare report parameter.
ParameterValue[] parameters = new ParameterValue[1];
parameters[0] = new ParameterValue();
parameters[0].Name = "ApptKey";
parameters[0].Value = ApptKey.ToString();

//DataSourceCredentials[] credentials = null;
//string showHideToggle = null;
string encoding;
string mimeType;
string extension;
Warning[] warnings = null;
//ParameterValue[] reportHistoryParameters = null;
string[] streamIDs = null;

ReportExecutionService rs = new ReportExecutionService();
rs.Credentials = System.Net.CredentialCache.DefaultCredentials;
//rs.Credentials = new NetworkCredential(user, pass, domain);
rs.Url = "https://moss/Forms/_vti_bin/ReportServer/ReportExecution2005.asmx";
rs.ExecutionHeaderValue = new ExecutionHeader();

ExecutionInfo execInfo = rs.LoadReport(reportPath, historyID);
String SessionId = rs.ExecutionHeaderValue.ExecutionID;
rs.SetExecutionParameters(parameters, "en-us");

result = rs.Render(format, devInfo, out extension, out encoding, out mimeType, out warnings, out streamIDs);

MemoryStream ms = new MemoryStream();
ms.Write(result, 0, result.Length);
ms.Position = 0;
return ms;
}
catch (SoapException e)
{
ErrorMessage(e, true, e.Detail.OuterXml);
return null;
}
catch (Exception ex)
{
ErrorMessage(ex);
return null;
}

}

UserProfileService.asmx in MOSS 2007/ https

Over the past month I have been working with InfoPath Forms Services and managed code. I noticed something on calling a the UserProfileService that I wanted to share. The use of a local DataConnection in the InfoPath works on both https and http calls. But I wanted to strickly use a code solution for this call. For some reason my code solution would only work on http calls. I then came across this article and the recommendation of changing the webservice url from the main site to the site of the form itself.

The solution was to change the url in the web service call from :
https://moss/_vti_bin/userprofileservice.asmx

to:

https://moss/sites/forms/_vti_bin/userprofileservice.asmx

Code Examples of Local and Code only solutions:

Local Data Connection inside Form:
private void GetProfileInfo2()
{
try
{
XPathNavigator Label = Root.SelectSingleNode("/my:myFields/my:Label", NamespaceManager);
XPathNavigator Name = Root.SelectSingleNode("/my:myFields/my:UserName", NamespaceManager);
XPathNavigator ErrorMesageLabel = Root.SelectSingleNode("/my:myFields/my:MessageLabel", NamespaceManager);

WebServiceConnection UserSvc = (WebServiceConnection)DataConnections["GetUserProfileByName"];
XPathNavigator xnDoc = DataSources["GetUserProfileByName"].CreateNavigator();
UserSvc.Execute();
XPathNavigator xnAcctEmail = xnDoc.SelectSingleNode("dfs:myFields/dfs:dataFields/s0:GetUserProfileByNameResponse/s0:GetUserProfileByNameResult/s0:PropertyData/s0:Values[../s0:Name = \"WorkEmail\"]", NamespaceManager);
XPathNavigator xnAcctName = xnDoc.SelectSingleNode("dfs:myFields/dfs:dataFields/s0:GetUserProfileByNameResponse/s0:GetUserProfileByNameResult/s0:PropertyData/s0:Values[../s0:Name = \"PreferredName\"]", NamespaceManager);
if (xnAcctEmail != null)
{
Label.SetValue(xnAcctEmail.Value);
Name.SetValue(xnAcctName.Value);
}
}
catch (Exception exception)
{
ErrorMessage(exception);
Root.SelectSingleNode("/my:myFields/my:MessageLabel", NamespaceManager).SetValue(exception.ToString());
}
}

Code Only Call:
private void GetProfileInfo()
{

try
{
XPathNavigator RemoteEmail = Root.SelectSingleNode("/my:myFields/my:RemoteEmail", NamespaceManager);
XPathNavigator RemoteName = Root.SelectSingleNode("/my:myFields/my:RemoteName", NamespaceManager);


UserProfileService Profile = new UserProfileService();
Profile.Credentials = new System.Net.NetworkCredential("User", "Pass", "Domain");
//Profile.UseDefaultCredentials = true;
//Profile.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials;
Web_Service_Validation.UserProfile.PropertyData[] properties = Profile.GetUserProfileByName(UserName);

if (properties.Length > 0)
{
RemoteEmail.SetValue(properties[36].Values[0].Value.ToString()); // WorkEmail
RemoteName.SetValue(properties[4].Values[0].Value.ToString()); //name
}


}
catch (Exception exception)
{
ErrorMessage(exception);
Root.SelectSingleNode("/my:myFields/my:MessageLabel", NamespaceManager).SetValue(exception.ToString());
}
}