Email Validation Simplified with Web API and SSIS from Melissa

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 the accuracy of email addresses when originally collected and over the lifetime of our customers. How can we perform email validation during data collection on our web site? 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, the entry of incorrect email addresses occurs. In addition, 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 – Melissa performs email validation against its 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, Melissa adds 500,000 to 4,000,000 email addresses 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 and Response 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. Email extraction includes SQL Server database tables or flat files. Drag and drop the corresponding source widget into the SSIS workspace as well as the Global Verify component from Melissa. Save the output 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.

Key Takeaways

  • Email validation is essential for organizations to ensure accurate communication and improve deliverability.
  • Melissa’s Global Email Verification offers real-time and batch processing solutions with GDPR compliance.
  • The service includes a Delivery Confidence Score based on various validation criteria to assess email quality.
  • Users can integrate Melissa’s API with SQL Server Integration Services for efficient email validation workflows.
  • To get started, outline your email verification needs, request a demo, and contact support for technical questions.

Next Steps

MSSQLTips.com Product Spotlight sponsored by Melissa, makers of Melissa’s Global Email Verification.

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *