SSIS - Send mail from script task

Sending a mail form an integration service package (SSIS) is implemented using the .Net framework classes.

The only question is how to use a SMTP Connection Manager task to configure the SMTP Server.

Public Sub Main()

    Dim mailMessage As MailMessage

    Dim smtpClient As SmtpClient

    Dim smtpConnectionString As String = DirectCast(Dts.Connections("SMTP Connection Manager").AcquireConnection(Dts.Transaction), String)

    Dim smtpServer As String = smtpConnectionString.Split(New Char() {"="c, ";"c})(1)

    mailMessage = New MailMessage("from@myMail.net", "to@myMail.net")

    mailMessage.Body = "myMessage"

    mailMessage.Subject = "mySubject"

    smtpClient = New SmtpClient(smtpServer)

    smtpClient.Credentials = CredentialCache.DefaultNetworkCredentials

    mailMessage.IsBodyHtml = True

    smtpClient.Send(mailMessage)

    Dts.TaskResult = Dts.Results.Success

End Sub

SQL Server Defragmentation

SQL Server stores the data and indexes on data files in a physical disk, this is called external fragmentation. As all files on the windows system, fragmentation degrades I/O performance.
SQL Server has also a internal fragmentation that occurs when records are removed from the database pages, but the space is not freed.
If data files have external fragmentation, index rebuilding will take longer since there is a I/O bottleneck.

Defragmentation is the process to fix fragmentation.
For each type of fragmentation, there is a solution:
1) Internal fragmentation:
Use the following script from SQL Server 2005 BOL to defragment or DBCC DBREINDEX .

2) External fragmentation:
Use a defragmentation tool like diskeeper to defragment the file system.

Developing a maintenance plan for SQL Server is a best practice.

Prevent Windows from paging the SQL Server data to virtual memory on disk

Paging the SQL Server data to virtual memory on disk reduces overall performance.
The Enable the Lock Pages in Memory Option is a windows policy that prevents Windows from paging the SQL Server data to virtual memory on disk.
The option is disabled by default.

This privilege must be enabled to configure Address Windowing Extensions (AWE), that must be activated on sql server.

To enable the lock pages in memory option:
1) Execute gpedit.msc on Start->Run
2) Expand Computer Configuration->Windows Settings
3) Expand Security Settings->Local Policies
4) Select the User Rights Assignment folder
5) Double-click Lock pages in memory and add the account that runs sqlservr.exe

In SQL Server Activate AWE.