EmailLogProvider Sample

  • Article

New: 14 April 2006

The EmailLogProvider sample demonstrates how to create a custom log provider, the Email Log Provider, which sends logging output as an e-mail message in either plain text or HTML format. This sample is not supported on Itanium-based operating systems.

For more information about how to create a custom log provider, see Creating a Custom Log Provider. For general information about the use of log providers in Integration Services, see Integration Services Log Providers.

The custom log provider expects a semicolon-delimited list of strings that specify the SMTP server, the To address, the From address, and whether the message is in text or HTML format. The log provider caches logged events, and when the package finishes running, it sends the cached output in the body of a mail message. It ignores the final two standard columns (dataCode and dataBytes) which may contain binary data that cannot be displayed correctly. The sample encapsulates the text logging code in a separate TextLogWriter class, and the HTML logging code in a separate HtmlLogWriter class, for ease of reuse. Each of these helper classes has OpenLog<format>, Log<format>, and CloseLog<format> subroutines, which are called from OpenLog, Log, and CloseLog respectively.

This code sample implements the following functionality:

  • Overriding of the LogProviderBase class.
  • Default persistence support that does not require the developer to write custom persistence code.

Important

Samples are provided for educational purposes only. They are not intended to be used in a production environment and have not been tested in a production environment. Microsoft does not provide technical support for these samples.

Running the Sample

If you already know how to locate, build, and install code samples, you can go directly to the section Testing the Sample, and read about how to configure and run the code sample.

Prerequisites

This sample requires that the following components are installed.

  • Microsoft Visual Studio 2005
  • Microsoft SQL Server 2005 Integration Services

The sample also requires an available SMTP server for mail functionality.

Location

If the code sample was installed to the default location, the sample is located in the following directory:

C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services\\Programming Samples\Control Flow\EmailLogProvider Sample

The C# solution for the code sample is located in the CS directory, and the Visual Basic solution is located in the VB directory.

For information about the two-step process required to install the samples, see Installing Samples. To obtain the latest version of the samples, including new samples released after the original release of SQL Server 2005, see SQL Server 2005 Samples and Sample Databases (April 2006).

Building the Sample

If you have not already generated a strong name key file in the Samples folder, use the following procedure to generate this key file. The sample projects are configured on the Signing tab of the Project Properties dialog box to sign assemblies at build time with this key file.

To generate a strong name key file

  1. To open a Microsoft Visual Studio 2005 command prompt, click Start, point to All Programs, point to Microsoft Visual Studio 2005, point to Visual Studio Tools, and then click Visual Studio 2005 Command Prompt.

    - or -

    To open a Microsoft .NET Framework command prompt, click Start, point to All Programs, point to Microsoft .NET Framework SDK v2.0, and then click SDK Command Prompt.

  2. At the command prompt, use the change directory (CD) command to change the current folder of the command prompt to the Samples folder. The key file that you create in this folder will be used by all SQL Server 2005 code samples.

    Note

    To determine the folder where samples are located, click Start, point to All Programs, point to Microsoft SQL Server 2005, point to Documentation and Tutorials, and then click Samples Directory. If the default installation location was used, the samples are located in <system_drive>:\Program Files\Microsoft SQL Server\90\Samples.

  3. At the command prompt, run the following command to generate the key file:

    sn -k SampleKey.snk

    Important

    For more information about the strong-name key pair, see "Security Briefs: Strong Names and Security in the .NET Framework" in the .NET Development Center on MSDN.

To build the sample in Microsoft Visual Studio 2005

  1. From the File | Open menu, click Project and open the EmailLogProviderVB.sln or EmailLogProviderCS.sln.

  2. On the Build menu, click Build Solution to build the solution.

Installing the Sample

This sample is provided in both Visual Basic and C#. To distinguish the assemblies for each version of the sample, the name of the output assembly has CS or VB appended. After successfully building the component, follow these steps in order to add it as a connection manager in a package in Business Intelligence Development Studio.

To copy the component to the Connections folder

  1. Open Windows Explorer or your preferred application for working in the file system.

  2. Copy the assembly (EmailLogProviderCS.dll or EmailLogProviderVB.dll) to the LogProviders folder located at %system%\Program Files\Microsoft SQL Server\90\DTS.

To install the component into the global assembly cache (GAC) by dragging the assemblies

  1. Open Windows Explorer or your preferred application for working in the file system.

  2. Drag the assemby from the LogProviders folder to the folder where the global assembly cache (GAC) is located, at %system%\assembly.

To install the component into the global assembly cache (GAC) by using gacutil.exe

  1. Open a Command Prompt window.

  2. Type the following commands to install the C# version of the component into the GAC:

    gacutil.exe -iF " C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\EmailLogProvider Sample\CS\EmailLogProviderCS\bin\Debug\EmailLogProviderCS.dll"

    - or -

    Type the following commands to install the Visual Basic version of the component into the GAC:

    gacutil.exe -iF " C:\Program Files\Microsoft SQL Server\90\Samples\Integration Services\Programming Samples\Control Flow\EmailLogProvider Sample\VB\EmailLogProviderVB\bin\Debug\EmailLogProviderVB.dll"

Testing the Sample

You can now test the custom log provider in a package.

To use the sample log provider in a package

  1. Confirm the name of an available SMTP server.

  2. From the SSIS menu, select Logging, and add the Custom log provider for mail messages (CS) or Custom log provider for mail messages (VB). You may want to configure two instances of the log provider so that you can test both the text and the HTML output formats.

  3. In the Configuration column, enter a semicolon-delimited list of four strings that specify the SMTP server, the To address, the From address, and whether the message is in text or HTML format. For example:

    smtpserver;joe@northwind.com;joe@northwind.com;html

  4. Configure logging as appropriate so that the package and its components log their events to the custom log provider.

  5. Run the package, and then view the mail messages that contain the logging output in your mail program.