Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
<System.AddIn.AddIn("ScriptMain", Version:="1.0", Publisher:="", Description:="")> _
<System.CLSCompliantAttribute(False)> _
Partial Public Class ScriptMain
Inherits Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
Enum ScriptResults
Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success
Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
End Enum
Public Sub Main()
Action_GenerateAndSendHTMLReport()
Dts.TaskResult = ScriptResults.Success
End Sub
Private Sub Action_GenerateAndSendHTMLReport()
Dim sOutputPath As String = SnippetParameter("User::EmailFileLocation", "", "").ToString()
Dim fileExtension As String = If(String.IsNullOrEmpty(sOutputPath), ".html", System.IO.Path.GetExtension(sOutputPath))
Dim sHTMLOutputFileName As String = "Verify_dbo_DimPayee - Duplicated PayeeAK - " & System.DateTime.Now.ToString("yyyyMMdd HHmmss") & ".html"
Dim sSqlStatement As String
sSqlStatement = "SELECT * " & _
"FROM dbo.Verify_dbo_DimPayee " & _
"WHERE PayeeAK IN " & _
"(" & _
"Select [PayeeAK] " & _
"FROM dbo.Verify_dbo_DimPayee " & _
"GROUP BY [PayeeAK] " & _
"HAVING COUNT(*) > 1) " & _
"ORDER BY PayeeAK;"
Dim sConnection_Manager_Name As String = "DWOperations".ToString()
Dim sReportTitle As String = "dbo.Verify_dbo_DimPayee - Records with Duplicate PayeeAK Values".ToString()
Dim sAction As String = "OnlyGenerateFile".ToString()
Dim sHTMLCSS As String = "Body{font-family: 'verdana'; font-size:11}" & System.Environment.NewLine & ".DataTable{} /*Style for table*/" & System.Environment.NewLine & ".NullCell{} /*Style for cells with null value*/" & System.Environment.NewLine & ".Error{} /*Style for error message*/" & System.Environment.NewLine & ".ReportTitle{font-size:16;font-style:bold;} /*Style for Report title*/" & System.Environment.NewLine & ".HeaderRow{font-size:12;background-color: #ccddcc;font-style:bold;} /*Style for Header Row*/" & System.Environment.NewLine & ".TotalRow{font-size:11;background-color: #ccddcc;font-style:bold;} /*Style for total row*/" & System.Environment.NewLine & ".EvenRow{background-color: #ffffff;font-size:11}" & System.Environment.NewLine & ".OddRow{background-color: #dddddd;font-size:11;}".ToString()
Dim sHeaderText As String = "".ToString()
Dim sFooterText As String = "".ToString()
Dim bShowRowNum As Boolean = Convert.ToBoolean(True)
Dim bShowSUM As Boolean = Convert.ToBoolean(False)
Dim bShowAVG As Boolean = Convert.ToBoolean(False)
Dim bSkipActionIfNoRecordFound As Boolean = Convert.ToBoolean(True)
Dim sOutput As String = GenerateHTMLReport(sConnection_Manager_Name, sSqlStatement, sHTMLCSS, sReportTitle, sHeaderText, sFooterText, bShowSUM, bShowAVG, bShowRowNum, bSkipActionIfNoRecordFound)
If bSkipActionIfNoRecordFound = True AndAlso String.IsNullOrEmpty(sOutput) = True Then
Exit Sub
End If
Dim bTemp As Boolean
If String.IsNullOrEmpty(fileExtension) Then fileExtension = ".html"
Dim sUniqueFileName As String = If(String.IsNullOrEmpty(sOutputPath), System.DateTime.Now.ToString("MM-dd-yyyy hhmmss.fff") & fileExtension, sHTMLOutputFileName)
Dim sAttachmentPath As String
If sAction <> "EmailAsEmbeddedOutput" Then
If String.IsNullOrEmpty(sOutput) = False Then
If System.IO.Directory.Exists(sOutputPath) Then
If Not sOutputPath.EndsWith("\") Then sOutputPath = sOutputPath & "\"
sAttachmentPath = System.IO.Path.Combine(sOutputPath, sUniqueFileName)
Else
sAttachmentPath = sOutputPath
End If
Else
bTemp = True
sAttachmentPath = System.IO.Path.Combine(System.IO.Path.GetTempPath(), sUniqueFileName)
End If
System.IO.File.WriteAllText(sAttachmentPath, sOutput)
Else
sAttachmentPath = ""
End If
If sAction = "OnlyGenerateFile" Then
Exit Sub
End If
Dim SB_Body As New System.Text.StringBuilder()
Dim isHtml As Boolean = (sAction = "EmailAsEmbeddedOutput" OrElse "HTML".ToString().ToUpperInvariant() = "HTML")
If sAction = "EmailAsEmbeddedOutput" Then
SB_Body.Append(sOutput)
Else
SB_Body.AppendLine("<H1>This is sample email</H1><P>")
SB_Body.AppendLine("<H1>Hello World!!!</H1><BR>")
SB_Body.AppendLine("<H2>Hello World!!!</H2><BR>")
SB_Body.AppendLine("<H3>Hello World!!!</H3><BR>")
SB_Body.AppendLine("<H4>Hello World!!!</H4><BR>")
End If
Try
SendMail("someone@someaddress.com".ToString() _
, "support@mycompany.com".ToString() _
, "".ToString() _
, "".ToString() _
, "SubjectLine".ToString() _
, SB_Body.ToString() _
, isHtml _
, "smtp-mycompany.com".ToString() _
, Convert.ToInt32(25) _
, Convert.ToBoolean(False) _
, "".ToString() _
, "".ToString() _
, "".ToString() _
, sAttachmentPath _
, "Normal")
Finally
If bTemp = True AndAlso System.IO.File.Exists(sAttachmentPath) Then
System.IO.File.Delete(sAttachmentPath)
End If
End Try
End Sub
Private Function GenerateHTMLReport(ByVal connMgr As String, ByVal sql As String, ByVal css As String, ByVal title As String, ByVal header As String, ByVal footer As String, ByVal isShowSum As Boolean, ByVal isShowAverage As Boolean, ByVal isShowRowNumber As Boolean, ByVal isSkipActionIfNoRecordFound As Boolean) As String
Dim currentDateTime As Date = DateTime.Now
Dim cmParam As Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100
Dim conn As IDbConnection = Nothing
Dim cm As ConnectionManager = Dts.Connections(connMgr)
Dim dt As DataTable
Try
If cm.CreationName.ToUpper = "OLEDB" OrElse cm.CreationName.ToUpper = "EXCEL" Then
cmParam = CType(cm.InnerObject, Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManagerDatabaseParameters100)
conn = CType(cmParam.GetConnectionForSchema(), OleDb.OleDbConnection)
Else
conn = CType(Dts.Connections(connMgr).AcquireConnection(Nothing), IDbConnection)
End If
Using ocmd As IDbCommand = conn.CreateCommand()
ocmd.CommandText = sql
ocmd.CommandType = CommandType.Text
dt = GetDBTable(ocmd, conn, cm.CreationName)
End Using
Finally
If conn IsNot Nothing Then conn.Dispose()
End Try
If (dt Is Nothing OrElse dt.Rows.Count < 1) AndAlso isSkipActionIfNoRecordFound = True Then
Return String.Empty
End If
Dim sb As New System.Text.StringBuilder()
sb.Append("<html><head><meta http-equiv=""content-type"" content=""text/html; charset=UTF-8""><title>" & title & "</title>")
sb.Append("<style type=text/css>" & css & "</style></head>")
sb.Append("<body>")
If String.IsNullOrEmpty(title) = False Then
sb.Append("<span class=reporttitle>" & title & "</span><br/>")
End If
If String.IsNullOrEmpty(header) = False Then
sb.Append(header)
End If
Dim colval As Object
Dim sVal As String
Dim bShowTotal As Boolean = (isShowAverage OrElse isShowSum)
Dim iCnt As Int64
Dim dSum(dt.Columns.Count - 1) As Double
Dim iColCnt As Integer
For Each dr As DataRow In dt.Rows
If iCnt = 0 Then
sb.Append("<table class=datatable>")
sb.Append("<tr class=""headerrow"">")
If isShowRowNumber = True Then
sb.Append("<td>Row#</td>")
End If
For Each col As DataColumn In dt.Columns
sb.Append("<td>" & col.ColumnName & "</td>")
Next
sb.Append("</tr>")
End If
sb.Append("<tr class=""" & IIf(iCnt Mod 2 = 0, "evenrow", "oddrow").ToString() & """>")
Try
sb.Append("<td>" & iCnt + 1 & "</td>")
iColCnt = 0
For Each colval In dr.ItemArray
If colval Is Nothing OrElse colval Is System.DBNull.Value Then
sb.Append("<td class=""nullcell""> </td>")
Else
sVal = colval.ToString
If String.IsNullOrEmpty(sVal) Then
sb.Append("<td> </td>")
Else
If bShowTotal AndAlso IsNumericDataType(colval) Then
If isShowSum OrElse isShowAverage Then
dSum(iColCnt) = dSum(iColCnt) + Convert.ToDouble(colval)
End If
End If
sb.Append("<td>" & sVal & "</td>")
End If
End If
iColCnt = iColCnt + 1
Next
Catch ex As Exception
sb.Append("<td colspan=" & dt.Columns.Count & " class=error>ERROR:" & ex.Message.Replace("<", "<").Replace("<", ">") & "</td>")
End Try
sb.Append("</tr>")
iCnt = iCnt + 1
Next
If iCnt <= 0 Then
sb.Append("<h6 class=NoRowFound>No record found</h6>")
Else
If bShowTotal Then
sb.Append("<tr class=totalrow>")
Dim i As Integer
If isShowRowNumber Then
sb.Append("<td>SUM" & If(isShowAverage, "<br>AVG</td>", "").ToString())
End If
For Each colval In dt.Rows(0).ItemArray
If IsNumericDataType(colval) Then
sb.Append("<td>" & If(isShowSum, Math.Round(dSum(i), 2).ToString(), "") & If(isShowAverage, "<br>" & Math.Round(dSum(i) / iCnt, 2), "").ToString() & "</td>")
Else
sb.Append("<td> </td>")
End If
i = i + 1
Next
sb.Append("</tr>")
End If
sb.Append("</table>")
End If
If String.IsNullOrEmpty(footer) = False Then
sb.Append(footer)
End If
sb.Append("<p><font size=""1"">This report was generated by <a style=""color: blue; text-decoration: underline; text-underline: single"" href=""http://www.pragmaticworks.com/products/business-intelligence/bixpress"">Pragmatic Works - BI xPress</a> in " & FormatTimeDiff(currentDateTime, Now) & ", " & Now() & "</font></p>")
sb.Append("</body></html>")
Return sb.ToString()
End Function
Function IsNumericDataType(ByVal o As Object) As Boolean
If TypeOf o Is System.Int16 _
OrElse TypeOf o Is System.Int32 _
OrElse TypeOf o Is System.Int64 _
OrElse TypeOf o Is System.Int64 _
OrElse TypeOf o Is System.Double _
OrElse TypeOf o Is System.Decimal _
OrElse TypeOf o Is System.Decimal _
OrElse TypeOf o Is System.Single Then
Return True
End If
Return False
End Function
Public Function GetDBTable(ByVal cmd As IDbCommand, ByVal cn As IDbConnection, ByVal connType As String) As DataTable
Try
cmd.Connection = cn
Dim da As IDataAdapter = Nothing
If connType.ToUpper = "OLEDB" OrElse connType.ToUpper = "EXCEL" Then
da = New Data.OleDb.OleDbDataAdapter(DirectCast(cmd, OleDb.OleDbCommand))
ElseIf connType.ToUpper.StartsWith("ADO.NET:SYSTEM.DATA.SQLCLIENT.SQLCONNECTION") Then
da = New Data.SqlClient.SqlDataAdapter(DirectCast(cmd, SqlClient.SqlCommand))
ElseIf connType.ToUpper.StartsWith("ADO.NET:SYSTEM.DATA.ODBC.ODBCCONNECTION") OrElse connType.ToUpper = "ODBC" Then
da = New Data.Odbc.OdbcDataAdapter(DirectCast(cmd, Odbc.OdbcCommand))
ElseIf connType.ToUpper.StartsWith("ADO.NET:SYSTEM.DATA.OLEDB.OLEDBCONNECTION") Then
da = New Data.OleDb.OleDbDataAdapter(DirectCast(cmd, OleDb.OleDbCommand))
End If
Using ds As New DataSet()
da.Fill(ds)
Dim returnTable As DataTable = If(ds.Tables.Count > 0, ds.Tables(0), Nothing)
ds.Tables.Clear()
ds.Relations.Clear()
ds.ExtendedProperties.Clear()
Return returnTable
End Using
Finally
If cn.State <> ConnectionState.Closed AndAlso cn.State <> ConnectionState.Broken Then cn.Close()
End Try
End Function
Public Function FormatTimeDiff(ByVal dt1 As Date, ByVal dt2 As Date) As String
Return (dt2 - dt1).TotalSeconds.ToString("N3") & " Seconds"
End Function
Private Sub SendMail( _
ByVal sendTo As String, _
ByVal sentFrom As String, _
Optional ByVal cc As String = "", _
Optional ByVal bcc As String = "", _
Optional ByVal subject As String = "", _
Optional ByVal body As String = "", _
Optional ByVal isBodyHtml As Boolean = True, _
Optional ByVal smtpServer As String = "localhost", _
Optional ByVal smtpPort As Integer = 25, _
Optional ByVal useSSL As Boolean = False, _
Optional ByVal userName As String = "", _
Optional ByVal password As String = "", _
Optional ByVal domain As String = "", _
Optional ByVal attachments As String = "", _
Optional ByVal priority As String = "Normal")
Using oMessage As New System.Net.Mail.MailMessage()
oMessage.From = New System.Net.Mail.MailAddress(sentFrom)
If String.IsNullOrEmpty(sendTo) = False Then
oMessage.[To].Add(sendTo.Replace(";", ","))
End If
If String.IsNullOrEmpty(cc) = False Then
oMessage.CC.Add(cc.Replace(";", ","))
End If
If String.IsNullOrEmpty(bcc) = False Then
oMessage.Bcc.Add(bcc.Replace(";", ","))
End If
If priority.ToUpper = "HIGH" Then
oMessage.Priority = System.Net.Mail.MailPriority.High
ElseIf priority.ToUpper = "LOW" Then
oMessage.Priority = System.Net.Mail.MailPriority.Low
Else
oMessage.Priority = System.Net.Mail.MailPriority.Normal
End If
oMessage.Subject = subject
oMessage.Body = body
oMessage.IsBodyHtml = isBodyHtml
If Not String.IsNullOrEmpty(attachments) Then
Dim files() As String = Split(attachments, "|")
For Each filename As String In files
If Not String.IsNullOrEmpty(filename) AndAlso System.IO.File.Exists(filename) Then
oMessage.Attachments.Add(New Net.Mail.Attachment(filename))
End If
Next
End If
Dim mySmtpClient As System.Net.Mail.SmtpClient = New System.Net.Mail.SmtpClient(smtpServer, smtpPort)
If String.IsNullOrEmpty(userName) Then
mySmtpClient.Credentials = System.Net.CredentialCache.DefaultNetworkCredentials
Else
mySmtpClient.Credentials = New System.Net.NetworkCredential(userName, password, domain)
End If
mySmtpClient.EnableSsl = useSSL
Try
mySmtpClient.Send(oMessage)
Finally
If (oMessage IsNot Nothing AndAlso oMessage.Attachments IsNot Nothing) Then
For Each attachment As System.Net.Mail.Attachment In oMessage.Attachments
attachment.Dispose()
Next
oMessage.Attachments.Dispose()
End If
End Try
End Using
End Sub
#Region "Helper functions"
Private Property SnippetParameter(ByVal sVarName As String, ByVal sConnName As String, ByVal sConnPropName As String) As Object
Get
Dim oRetVal As Object
If String.IsNullOrEmpty(sConnName) = False Then
Dim oCon As ConnectionManager
oCon = Dts.Connections(sConnName)
If String.IsNullOrEmpty(sConnPropName) Then
sConnPropName = "ConnectionString"
ElseIf sConnPropName = "<object>" Then
Return oCon
End If
oRetVal = oCon.Properties(sConnPropName).GetValue(oCon)
ElseIf String.IsNullOrEmpty(sVarName) = False Then
oRetVal = ReadVariable(sVarName)
Else
oRetVal = Nothing
End If
Return oRetVal
End Get
Set(ByVal oValue As Object)
If String.IsNullOrEmpty(sConnName) = False Then
Dim oCon As ConnectionManager
oCon = Dts.Connections(sConnName)
If String.IsNullOrEmpty(sConnPropName) Then
sConnPropName = "ConnectionString"
End If
oCon.Properties(sConnPropName).SetValue(oCon, oValue)
ElseIf String.IsNullOrEmpty(sVarName) = False Then
WriteVariable(sVarName, oValue)
End If
End Set
End Property
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables = Nothing
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
If Not String.IsNullOrEmpty(varName) AndAlso Dts.VariableDispenser.Contains(varName) Then
Dim vars As Variables = Nothing
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Else
result = Nothing
End If
Catch ex As Exception
Throw ex
End Try
Return result
End Function
#End Region
End Class |
|
Properties
