SSIS-Script Task - Generics List use

' Microsoft SQL Server Integration Services Script Task

Imports

System

Imports

System.DataImports System.MathImports Microsoft.SqlServer.Dts.RuntimeImports Microsoft.SqlServer.Dts.Pipeline.Wrapper

Imports

Microsoft.SqlServer.Dts.Runtime.Wrapper

Imports

System.CollectionsImports System.Collections.Generic

Imports

System.TextImports System.Data.SqlClientImports System.NetImports System.Net.MailPublic
Class ScriptMain' The execution engine calls this method when the task executes.' To access the object model, use the Dts object. Connections, variables, events,' and logging features are available as static members of the Dts class.

----------------------------------------------------------------------------------------------------

' Public Sub Main()'' Add your code here

Dim Users As New List(Of User)Dim Managers As New ArrayList()Dim objUser As UserDim objManager As String


Dim smtpConnectionString As String = DirectCast(Dts.Connections("SMTP_CONN_MAIL_SERVER").AcquireConnection(Dts.Transaction), String)Dim smtpServer As String = smtpConnectionString.Split(New Char() {"="c, ";"c})(1)Dim htmlMessageFrom As String = Dts.Variables("emailFrom").Value.ToString()Dim htmlMessageTo As String


Dim htmlMessageSubject As String = Dts.Variables("emailSubject").Value.ToString()Dim htmlMessageBody As New StringBuilder()' get users objectUsers = getUsers()
' create a unique list of manager list contains email address
For Each objUser In UsersIf Not (Managers.Contains(objUser.strManagerEmail.ToString().Trim())) ThenManagers.Add(objUser.strManagerEmail.ToString().Trim())
End IfNext' send Mail
  htmlMessageTo = objManager.ToString() For Each objManager In Managers'objUser.strManagerEmail.ToString()

  htmlMessageBody.Append(

    For Each objUser In Users      If (objUser.strManagerEmail.ToString() = objManager.ToString()) Then ' get list of users for a manager

          htmlMessageBody.Append(

"text" & objUser.strPSTSize.ToString("###,###,###,##0") & ")      End If  Next

htmlMessageBody.Append(

SendMailMessage(htmlMessageTo, htmlMessageFrom, htmlMessageSubject, htmlMessageBody.ToString(),
"some text")True, smtpServer)' reset the bodyhtmlMessageBody.Remove(0, htmlMessageBody.Length)
htmlMessageTo =
String.EmptyNextDts.TaskResult = Dts.Results.Success
End Sub

----------------------------------------------------------------------------------------------------

Public Function getUsers() As List(Of User)Dim objUsers As List(Of User) = New List(Of User)()Dim newUser As UserDim cm1 As ConnectionManager = Dts.Connections("ISTools_ADO.NET")'Dim cm2 As ConnectionManager = Dts.Connections("ISTOOLS_OLEDB")
Dim oReader As SqlDataReader' For an ADO.Net ConnectionManager using a SqlClient provider

oCmd.CommandType = Data.CommandType.Text
oCmd.CommandText = Dim sqlConn As System.Data.SqlClient.SqlConnection = DirectCast(cm1.AcquireConnection(Dts.Transaction), SqlConnection)Dim oCmd As SqlCommand = New SqlCommand"select * FROM table"oCmd.Connection = sqlConn

'sqlConn.Open()oReader = oCmd.ExecuteReader()
If oReader.HasRows Then
objUsers.Add(
Convert.ToInt32(oReader(3)), Convert.ToInt32(oReader(4)), Convert.ToString(oReader(6)), Convert.ToString(oReader(7))))
While oReader.ReadNew User(Convert.ToString(oReader(0)), Convert.ToString(oReader(1)), Convert.ToInt32(oReader(2)), _End WhileEnd If'release the object finally

cm1.ReleaseConnection(sqlConn)

----------------------------------------------------------------------------------------------------Private Sub SendMailMessage( _ByVal SendTo As String, ByVal From As String, _ByVal Subject As String, ByVal Body As String, _ByVal IsBodyHtml As Boolean, ByVal Server As String)If (SendTo.Trim() = "") Then SendTo = FromDim htmlMessage As MailMessageDim mySmtpClient As SmtpClientDim maSendTo As New MailAddress(SendTo)Dim maFrom As New MailAddress(From)New MailMessage(maFrom, maSendTo)New SmtpClient(Server)End Sub

htmlMessage =
htmlMessage.Subject = Subject
htmlMessage.Body = Body
htmlMessage.IsBodyHtml = IsBodyHtml
mySmtpClient =
mySmtpClient.Credentials = CredentialCache.DefaultNetworkCredentials
mySmtpClient.Send(htmlMessage)
htmlMessage.Dispose()

----------------------------------------------------------------------------------------------------

Private Function uniqueArray(ByVal str() As String) As ArrayListDim noDups As New ArrayList()For i As Integer = 0 To str.Length - 1If Not noDups.Contains(str(i).Trim()) ThennoDups.Add(str(i).Trim())
End IfNext

---------------------------------------------------------------------------------------------------- Class

End

----------------------------------------------------------------------------------------------------Public
Class User' User Object to hold each user record. Create pr0perties to read/write'Dim strLanid As String'Dim strFullName As String'Dim strHDriveSize As Integer'Dim strPSTSize As Integer'Dim strNonPSTSize As Integer

'Dim strUserEmail As String

----------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------Private p_strLanid As StringPrivate p_strFullName As StringPrivate p_strHDriveSize As IntegerPrivate p_strPSTSize As IntegerPrivate p_strNonPSTSize As IntegerPrivate p_strUserEmail As String

-properties

Public Property strLanid() As StringGet
Return p_strLanidEnd Get
p_strLanid = value
Set(ByVal value As String)End SetEnd PropertyPublic Property strFullName() As StringGet
Return p_strFullNameEnd Get
p_strFullName = value
Set(ByVal value As String)End SetEnd PropertyPublic Property strHDriveSize() As IntegerGet
Return p_strHDriveSizeEnd Get

p_strHDriveSize = value
Set(ByVal value As Integer)End SetEnd PropertyPublic Property strPSTSize() As IntegerGet
Return p_strPSTSizeEnd Get
p_strPSTSize = value
Set(ByVal value As Integer)End SetEnd PropertyPublic Property strNonPSTSize() As IntegerGet
Return p_strNonPSTSizeEnd Get
p_strNonPSTSize = value
Set(ByVal value As Integer)End SetEnd PropertyPublic Property strUserEmail() As StringGet
Return p_strUserEmailEnd Get
p_strUserEmail = value
Set(ByVal value As String)End SetEnd PropertyPublic Property strManagerEmail() As StringGet
Return p_strManagerEmailEnd Get
p_strManagerEmail = value
Set(ByVal value As String)End Set

----------------------------------------------------------------------------------------------------

Public Overrides Function ToString() As String

----------------------------------------------------------------------------------------------------

End

----------------------------------------------------------------------------------------------------

Class

----------------------------------------------------------------------------------------------------
InnerList.Add(GenericObject)

----------------------------------------------------------------------------------------------------

InnerList.RemoveAt(index)
Public Sub Remove(ByVal index As Integer)End Sub

----------------------------------------------------------------------------------------------------End

Class 'End of GenericCollection classPublic Function Item(ByVal index As Integer) As GenericTypeReturn CType(InnerList.Item(index), GenericType)End Function

Public Sub Add(ByVal GenericObject As GenericType)End Sub GenericCollection(Of GenericType)Inherits Collections.CollectionBase

Class 'End of Employee ClassReturn ("LanId : " + strLanid + vbCrLf + _"FullName : " + strFullName + vbCrLf + _"HDriveSize : " + strHDriveSize.ToString() + vbCrLf + _"PSTSize : " + strPSTSize.ToString() + vbCrLf + _"NonPSTSize : " + strNonPSTSize.ToString() + vbCrLf + _"UserEmail : " + strUserEmail + vbCrLf + _"ManagerEmail : " + strManagerEmail)End FunctionEnd PropertyPrivate p_strManagerEmail As String

Sub New(ByVal Lanid As String, ByVal FullName As String, ByVal HDriveSize As Integer, ByVal PSTSize As Integer, _ByVal NonPSTSize As Integer, ByVal UserEmail As String, ByVal ManagerEmail As String)Me.strLanid = LanidMe.strFullName = FullNameMe.strHDriveSize = HDriveSizeMe.strPSTSize = PSTSizeMe.strNonPSTSize = NonPSTSizeMe.strUserEmail = UserEmailMe.strManagerEmail = ManagerEmailEnd Sub'Dim strManagerEmail As String

Return noDupsEnd FunctionReturn objUsersEnd Function
" Text")

' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.

' Write scripts using Microsoft Visual Basic

' The ScriptMain class is the entry point of the Script Task.

' Developed by Rajesh Subbiah on 5 Aug 2011