Email Validation Simplified with Web API and SSIS

By:   |   Updated: 2020-11-17   |   Comments   |   Related: More > Data Quality Services


Problem

Email is mission critical in our organization. Our organization uses email as a means to communicate with current and prospective customers due to the cost, speed, simplicity and interactivity. We need to ensure our email addresses are correct when they are originally collected and accurate over the lifetime of our customers. How can we perform email validation when data is collected on our web site? How can we validate our email data in bulk for particular projects to cleanup emails, prepare emails for marketing programs, etc.? How can we be sure we are meeting the GDPR requirements?

Solution

Email has been the communication medium of choice for organizations due to the cost, delivery speed, response speed and overall client interactivity. Unfortunately, email addresses can be entered incorrectly and email address data goes stale just like any other contact data. Most organizations have a need to:

  • Validate email addresses during registration on web applications
  • Cleanup emails for regular communication with customers
  • Improve the email deliverability for email-based marketing programs

Email based validation is more than just a simple RFC check, however it is much less complicated than validating full contact information (name, address, phone number, employment, marital status, household information, etc.) for a customer. This is where Melissa’s Global Email Verification solution can address real time email validation with a web-based API and batch-based email validation with SQL Server Integration Services (SSIS).

Melissa’s Global Email Overview

The value of the Melissa Global Email Verification solution centers around the artificial intelligence to build a numerical Delivery Confidence Score between 0 and 100 as well as public Result Codes in the final output. At a high level, the confidence score is based on artificial intelligence (Accept, Caution or Reject) surmised by domain validation (spam trap, disposable, accept all domains, mobile, black list IP), correct email format (syntax validation), mailbox validation (invalid mailbox, mail server not found), removal of illegal characters, validation from secondary data sources, compromised email checks and more. The final output is based on the best possible result and the associated data score.

Melissa Global Email Overview

Melissa’s Global Email solution includes modes for real time verification:

  • Premium Mode – Email address verification including an SMTP ping and additional proprietary validation logic for the most accurate data possible.
  • Express Mode - The email validation is performed against Melissa’s blazing fast cache to integrate directly into your applications for a truly real time solution. Melissa has more than 750,000,000 email addresses (both valid and illegitimate) in their cache. Further, 500,000 to 4,000,000 email addresses are added to the cache every 7 to 10 days to continuously improve their data set.

Both email validation modes from Melissa are GDPR compliant including encrypted cache for European users.

Melissa Global Email APIs

The Melissa Global Email API (REST, JSON, JSONP and XML) is used for both real time email validation and small batches. A simple Request (Learn more) and Response (Learn more) paradigm is used to process the data. The Request consists of the header as well as the record ID and email address. The Response includes the header, passes back the record ID as well as 23 fields denoting the record status. Let’s take a look at a sample JSON Request and Response:

Sample Request

{
  "TransmissionReference": "string",
  "CustomerID": "string",,
  "Options": "string",
  "Records": [
    {
      "RecordID": "string"
      "Email": "string",
    },
    {
      "RecordID": "string",
      "Email": "string",
    },
    ...,
  ]
}

Learn more about the JSON Request.

Sample Response

Below is a sample response screen shot showing the Deliverability Confidence Score and the associated data:

{
  "Version": "string",
  "TransmissionReference": "string",
  "TransmissionResults": "string",
  "TotalRecords": "string",
  "Records": [
    {
      "RecordID": "string",
      "DeliverabilityConfidenceScore": "string",
      "Results": "string",
      "EmailAddress": "string",
      "MailboxName": "string",
      "DomainName": "string",
      "TopLevelDomain": "string",
      "TopLevelDomainName": "string",
      "DateChecked": "string",
      "DomainAgeEstimated": "string",
      "DomainExpirationDate": "string",
      "DomainCreatedDate": "string",
      "DomainUpdatedDate": "string",
      "DomainEmail": "string",
      "DomainOrganization": "string",
      "DomainAddress1": "string",
      "DomainLocality": "string",
      "DomainAdministrativeArea": "string",
      "DomainPostalCode": "string",
      "DomainCountry": "string",
      "DomainCountryCode": "string",
      "DomainAvailability": "string",
      "DomainPrivateProxy": "string",
      "PrivacyFlag": "string",
    },
    {
      "RecordID": "string",
      "DeliverabilityConfidenceScore": "string",
      "Results": "string",
      "EmailAddress": "string",
      "MailboxName": "string",
      "DomainName": "string",
      "TopLevelDomain": "string",
      "TopLevelDomainName": "string",
      "DateChecked": "string",
      "DomainAgeEstimated": "string",
      "DomainExpirationDate": "string",
      "DomainCreatedDate": "string",
      "DomainUpdatedDate": "string",
      "DomainEmail": "string",
      "DomainOrganization": "string",
      "DomainAddress1": "string",
      "DomainLocality": "string",
      "DomainAdministrativeArea": "string",
      "DomainPostalCode": "string",
      "DomainCountry": "string",
      "DomainCountryCode": "string",
      "DomainAvailability": "string",
      "DomainPrivateProxy": "string",
      "PrivacyFlag": "string",
      "MXServer": "string",
    },
    ...,
  ]
}

Melissa Response Data with Deliverability Confidence Score

Learn more about the JSON Response.

Melissa Global Email with SQL Server Integration Services

Although some of the APIs enable batch processes, the record count is limited to 10. To address large scale data sets commonly found in databases, Melissa Global Email Verification includes direct integration with SSIS. The email data can be extracted from a SQL Server database table or flat file. The corresponding source widget can be dragged and dropped into the SSIS workspace as well as the Global Verify component from Melissa, then the output can be saved to a database table or flat file as shown below.

Melissa Global Email with SQL Server Integration Services

When you double click on the Global Verify widget in SSIS, the following interface displays the Email tab with the singular email address input field and 23 output fields of data which are used to validate the email addresses.

Melissa Global Verify

To continue the process, the email field is passed through the process as shown below.

Melissa Pass Through Columns

Finally, the output is configured with the associated Result Codes.

Melissa Output Data

If you are already proficient with SSIS, getting the email validation setup is fast and can be completed in as few as 5 minutes to begin benefiting from Melissa Global Email Verification.

Download Now - Melissa Data Quality Components for SSIS


Next Steps


Last Updated: 2020-11-17


get scripts

next tip button



About the author
MSSQLTips author Jeremy Kadlec Jeremy Kadlec is the Co-Founder, Editor and Author at MSSQLTips.com, CTO @ Edgewood Solutions and a six time SQL Server MVP.

View all my tips
Related Resources





Comments For This Article





Download Product Trial


agree to terms