Eventlog as a source

Case
I want to use the Windows Eventlog as a source in SSIS.

Solution
You can either read the saved eventlog files (*.evt / *.evtx) with for example this open source component or you can read directly from the eventlog itself. Let's elaborate the last option.

1) Script Component
Go to your Data Flow and add a Script Component. The Type should be Source.

Script Type Source

2) Add Columns

Edit the Script Component and go to the Inputs and Outputs tab. Add four columns at the output section:

1. EntryType (string 255)
2. Source (string 255)
3. Message (string 4000)
4. TimeGenerated (database timestamp)

Add output columns

This example uses only four columns, but there are more columns available in the eventlog.

3) The Script
Go to the first tab and hit the Edit Script... button. This script is in the default C# language. SSIS will create three methods: PreExecute, PostExecute and CreateNewOutputRows. Remove the first two, because we don't need them. Replace your method with this one and add the using row at the top.

// C# code
using System;
using System.Data;
using System.Diagnostics;       // Added
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;

[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
    public override void CreateNewOutputRows()
    {
        // Get all events from the Application(/System/Security) log from the local server (.)
        EventLog myEvenLog = new EventLog("Application", ".");

        // Create variable to store the entry
        EventLogEntry myEntry;

        // Loop trough all entries (oldest first)
        for (int i = 0; i < myEvenLog.Entries.Count; i++)
        {
            // Get single entry
            myEntry = myEvenLog.Entries[i];

            // Add new records
            this.Output0Buffer.AddRow();

            // Fill columns
            this.Output0Buffer.EntryType = myEntry.EntryType.ToString();
            this.Output0Buffer.Source = myEntry.Source;
            this.Output0Buffer.TimeGenerated = myEntry.TimeGenerated;
            // Take a max of 4000 chars
            this.Output0Buffer.Message = myEntry.Message.Substring(0, (myEntry.Message.Length > 4000 ? 3999 : myEntry.Message.Length - 1));
        }
    }
}

Note 1: with variables you can avoid hard coded strings in your Script Component.

Note 2: You should add some error handling to your script (Try Catch) to avoid unexpected errors (authorization, too large texts, etc.).


4) The result
Add a random transformation component after your source and add a dataviewer between them to see the result.

The result

Note: It's also possible to write to the eventlog with .net code, but that is not a best practice. Use the SSIS script event logging instead!