Tuesday
May032011

Import Exchange NDR to SQL Database - Part One

I was instructed to report on the number of nondeliverable e-mails (aka "BounceBacks" or "NDR's") we were sending from a certain e-mail address.  Enterprise messaging solutions like Exchange obviously record this data somewhere, so we just need to harvest this data and import it into a SQL database where the data can be organized and accessed better.

In the first part of this post I'll show you how to get this information from your Exchange (2007 and above) implementation via PowerShell and pipe it into a flat file. In subsequent posts I'll go over how to get it into a SQL database using SSIS.

I'll start with the basic setup.

First of all you'll need to download the Exchange Managment Tools.  This would include the Exchange management Shell and all the Powershell cmdlets that come with Exchange.

One caveat to this project is that I use Powershell to query the Exchange information, so if you're not running Exchange 2007 or above, or are in a mixed environment, you'll only be able to capture information for Exchange 2007 and above mailboxes sending mail.  Microsoft made a bold move in tying much of the management functionality into Powershell starting with Exchange 2007, and one of the reasons was, apparently, the wealth of data and ease of use.

Depending on your Exchange implementation's complexity and security concerns it's doubtful you'd want to run the Powershell script on one of your Exchange Servers directly.  Here's a list of helpful links to get the Exchange tools and associated Powershell cmdlets running on a different box:

Instructions for Exchange 2010 tools
http://technet.microsoft.com/en-us/library/bb232090.aspx

Download Exchange 2007 Management Tools
http://www.microsoft.com/downloads/en/details.aspx?FamilyID=6be38633-7248-4532-929b-76e9c677e802

Download 32-bit version of Exchange 2007 Management Tools
http://www.microsoft.com/downloads/details.aspx?FamilyID=6be38633-7248-4532-929b-76e9c677e802&displaylang=en

Instructions for Exchange 2007 Management Tools on 32-bit machine
http://support.microsoft.com/kb/555841

 

Once you have the tools up and running a simple Powershell script should capture the information you're looking for:

The snippet above is pretty intuitive. We're looking for e-mails from a certain sender , "SenderToSearchFor@RyanBoyer.net'. We're only looking for e-mails that failed and we're sent within the time range specified. We're exporting the results to a comma delimited file.

According to the article here you're required to specify the MessageID or the Subject. In my case specifying the Subject made much more sense.

In the next post I'll go over the basic SSIS package I used to import this into the database.