Quickstart

Quickstart#

Introduction#

Overview#

Melissa’s Data Quality Components for SSIS® is a collection of data cleansing services built as components for SSIS®.

Component List#

Component

Description

Business Coder

Business Coder searches for Business Demographics data based on the address and company name.

Contact Verify

Clean up contact data, removing bad or incomplete information before it invades your database and creates a negative impact on your data-driven initiatives. You’ll reduce undeliverables, increase communication efforts, and save money on all your marketing campaigns.

Fuzzy Match

The Fuzzy Match Component leverages a toolbox of state-of-the-art fuzzy matching algorithms and allows user-specified granular control on match thresholds and even fine tuning of the algorithms.

Generalized Cleanser

The Generalized Cleanser Component is a comprehensive tool that allows the user to standardize and cleanse their own data with respect to capitalization, punctuation, abbreviation, regular and custom expressions, and string replacement. This allows the user a high level of customization through combinations of these different actions, allowing easy standardization of any general data type.

Global Verify

Verify and standardize mailing addresses from across the world with the flexibility and accuracy delivered by Melissa products.

IP Locator

IP Locator returns geographic data for a given IP address. The level of detail may vary considerably.

MatchUp

MatchUp Component is an extremely fast and powerful programmer’s tool that can be integrated into custom applications to eliminate duplicate records.

Personator

Personator is an all-in-one contact verification and appending Web service. It allows you to pass in names, addresses, phone numbers, and email addresses; simultaneously parsing them, making conservative corrections, and even appending data derived from the input. It can also leverage all of these inputs to verify whole contact records.

Personator World

Melissa’s Personator World Component is a new ID verification solution makes it easy to check and verify your data in real time. Using trusted reference and national databases, the service can verify a person’s name, address, phone, email, date of birth and national ID.

Property

Property returns information about a given parcel of property, including: assessed value; last sale price; current mortgage; physical dimensions of land; and square footage. The level of detail may vary according to location.

Profiler

Melissa’s Data Profiler is a component that can be used to analyze a table’s data. This analysis provides a large number of statistics at varying levels of detail. Using these statistics, you can make educated decisions on what strategies you may need to employ to handle the data.

SmartMover

Enjoy the benefits of the most current address data available anytime and anywhere through SmartMover, by Melissa. The SmartMover Component uses the most cutting edge NCOA™ technology from the USPS.

System Requirements#

SSIS Requirements#

The Data Quality Components for SSIS are 32/64 bit tools available for Windows XP/2003/2008/Vista/7/8/10 and Microsoft SQL Server 2005/2008/R2 (Deprecated) - 2012 & 2014 (Supported).

  • There is a known issue with Windows 10 with SQL2012 + VS2010, if you have data viewer enabled it will hang the process. This issue happens regardless if our components are used or not, so it is likely a Windows issue. However, we are currently still investigating this issue.

Minimum Requirements#

  • 64-Bit Windows 7 or newer

  • Microsoft SQL Server 2012 or newer

  • 6 GB hard-disk space for Web Service update

  • 30 GB hard-disk space for On Premise data

  • 4 GB RAM

SQL Server/Visual Studio Version#

Melissa currently supports SQL Server versions 2012, 2014, 2016, 2017, 2019 and 2022. We also provide components for SQL Server 2005, 2008 and 2010 as part of the SSIS Installation, however, they are no longer supported.

Certain Microsoft SQL Server version are officially supported by specific Microsoft Visual Studio versions for our components. BusinessCoder and future new component will only be offered in SSIS 2010 and newer.

SSIS Installation#

Melissa’s SSIS components must be installed to the same drive as the SQL Server Integration Services instance.

You can verify the location of SQL Server Integration Services by using the Windows Search Bar and entering Services.msc. In the Services menu, scroll down until you see “SQL Server Integration Services XX.X”. Then right click on the entry and select Properties. In the General tab, locate the Path to executable directory. For example: C:\Program Files\Microsoft SQL Server\110\DTS\Binn\MsDtsSrvr.exe

In this case, SQL Server Integration Services is installed to the C: drive, so a regular installation is needed.

If the path to executable shows other than a C: drive, you will need to install Melissa’s SSIS components using the SSIS Install Other than C: drive instructions.

SSIS Install Other than C: drive Instructions: SSIS Install Other Than C: Drive

Officially Supported by Melissa#

  • Microsoft SQL Server 2022 – Microsoft Visual Studio 22

    • SSDT – SQL Server Data Tools – 17.2.40118.0

      • Accessible via Visual Studio Installer

      • Select edition of Visual Studio you want to add SSDT, select Modify

      • Select SQL Server Data Tools under Data storage and processing

    • SSIS – SQL Server Integration Services Projects 2022 – 16.0.5131.0

    • SQL Server 22 Developer – Integration Services 64bit (CMD Prompt – DTExec.exe) – 16.2211.5693.3

      • To execute projects via CMD prompt, SSIS 2022 requires 64bit SQL Server Integration Services from SQL Server 22 Developer Edition.

      • SQL Server 22 Developer Edition Download

      • In SQL Server 2022 Setup Feature Selection, select Integration Services.

    • Visual Studio 22 – Download

  • Microsoft SQL Server 2019 - Microsoft Visual Studio 2019

  • Microsoft SQL Server 2017 - Microsoft Visual Studio 2017

  • Microsoft SQL Server 2016 - Enterprise Edition and RC2 - Microsoft Visual Studio 2015

  • Microsoft SQL Server 2014 - Microsoft Visual Studio 2013

  • Microsoft SQL Server 2012 - Microsoft Visual Studio 2010/2012 - Deprecated: 7/24/2023

  • Microsoft SQL Server 2010 - Microsoft Visual Studio 2008 - Deprecated

  • Microsoft SQL Server 2008 - Microsoft Visual Studio 2005 - Deprecated

Azure Data Factory (ADF) Requirements#

To execute SSIS packages containing Melissa Data Quality Components on Azure Data Factory (ADF), you must first configure and build your SSIS packages in a local development environment where the Melissa SSIS components are installed. Once configured, the packages can then be deployed to Azure for execution.

Local Environment Requirements#

You must prepare a local machine with the following minimum specifications to develop and deploy SSIS packages:

  • Operating System: 64-bit Windows 7 or newer

  • SQL Server Version: Microsoft SQL Server 2017 (Developer or Standard Edition recommended)

  • Visual Studio Version: Visual Studio 2017 or newer with SQL Server Data Tools (SSDT) installed

  • Disk Space: * 6 GB hard-disk space (for Web Service component updates) * 30 GB hard-disk space (for On-Premise data if applicable)

  • Memory: 4 GB RAM minimum (8 GB recommended)

Important

Azure SSIS Integration Runtime (SSIS-IR) currently supports only SQL Server 2017 packages.

Therefore, it is required to install SQL Server 2017 in your local development environment to ensure the packages you deploy are compatible with the SSIS-IR instance in Azure.

Azure Services Requirements#

The following Azure services must be provisioned to deploy and execute SSIS packages in the cloud:

  • Azure Data Factory (ADF)

    • A running SSIS Integration Runtime (IR) instance

  • Azure SQL Server

    • Used to host the SSISDB catalog, where deployed packages are stored

  • Azure Blob Storage

    • Used to store the setup script, installation files, and runtime logs

Additional Notes:

  • Ensure your Azure subscription has sufficient resources and permissions to create and manage ADF, Azure SQL Database, and Blob Storage services.

  • You will need to generate a Shared Access Signature (SAS) token for Blob Storage with sufficient permissions (read, write, delete, create, list) for SSIS-IR setup and SmartMover export operations.

Installation#

SSIS Installation#

The Data Quality Components for SSIS can either be installed fully on-premise or as a web only install. The following sections will detail the install size, delivery method, data required, and install procedure.

Full On-Premise Install#

Size:

~7GB

Deliverable:

Physical Disk or Downloadable Zip File (DQC-SSIS_YYYYMM.zip)

Data:

Installs all necessary data files for components that have an option for On-Premise Mode.

Component

Mode

Web

Local

Business Coder

Contact Verify

✓

✓

Fuzzy Match

✓

Generalized Cleanser

Global Verify

✓

IP Locator

✓

✓

MatchUp

✓

Personator

✓

Personator World

✓

Property

✓

Profiler

✓

Smart Mover

✓

Web Only Install#

Size:

~150MB

Deliverable:

Download Only (setup-web.exe)

Data:

Installs only the minimum set of required data files.

Component

Mode

Web

Local

Business Coder

Contact Verify

✓

Fuzzy Match

✓

Generalized Cleanser

Global Verify

✓

IP Locator

✓

MatchUp

✓

Personator

✓

Personator World

✓

Property

✓

Profiler

✓

Smart Mover

✓

Install Procedure#

To install, download and run the Data Quality Components Executable.

License Agreement#

  1. Read and accept the license agreement.

../../_images/SSIS_Install_Agreement.png

Click Next to continue.

License Information#

  1. Enter your License Key, or continue with the install and enter your License Key later.

../../_images/SSIS_Install_License.png
Component Selection#

  1. Select which components to install.

../../_images/SSIS_Install_Components.png

Click Next to continue.

Review#

  1. Review your installation settings and ensure that everything is as you want it.

../../_images/SSIS_Install_Review.png

Click Install to continue.

Install#

  1. Wait for the components to install.

../../_images/SSIS_Install_Progress.png
Finish#

  1. Once the Data Quality Components are done installing, you will be prompted to finish the setup.

../../_images/SSIS_Install_Finish.png

Basics#

How to Open SSIS#

  1. To open SSIS please find the version of SQL Server and open SQL Server Data Tools.

../../_images/SSIS_NewProject_Open.png
Start New Project#

  1. Once SSIS loads, Select New Project. Then under Business Intelligence, select integration services and start a new integration services project.

../../_images/SSIS_NewProject_01.png ../../_images/SSIS_NewProject_02.png
Data Flow Tab#

  1. Our components are installed in the data flow SSIS toolbox. You will need to add a data flow task in order to view the toolbox and our components. Otherwise you will not be able to see the correct toolbox in which our components are located in. Our components are only visible in Data Flow Tab.

../../_images/SSIS_NewProject_DataFlow.png

Azure Data Factory (ADF) Installation#

Currently available for:

  • Business Coder

  • Contact Verify - Cloud Version

  • Global Verify

  • IP Locator

  • Personator Consumer

  • Property

  • Smart Mover

You can create an SSIS package locally in your development environment, then deploy that package to Azure SQL Server (SSISDB) and execute it using an SSIS Integration Runtime (SSIS-IR) instance in Azure Data Factory. This approach allows you to scale SSIS package processing in the cloud, providing greater flexibility, reliability, and resource management compared to traditional on-premises deployments.

Set Up the Azure Data Factory Environment by Starting an SSIS Integration Runtime (SSIS-IR) Instance#

Follow these steps to set up an SSIS Integration Runtime (SSIS-IR) in Azure Data Factory, configured to run Melissa SSIS components using your own Azure Blob Storage container.

Step 1: Clone the ADF Web Installer Repository#

Clone the ADF Web Installer repository to your local machine using this Git repository:

Step 2: Download Installation Dependencies#

You have two options to obtain the installation dependencies required for setting up the SSIS-IR.

Option 2: Using Melissa Updater:#

Download Melissa Updater:

Run Melissa Updater from the command line:

.\MelissaUpdater.exe file `
    -n "DependenciesADF.zip" `
    -r "latest" `
    -l "{{LicenseKey}}" `
    -y "INTERFACE" -o "WINDOWS" -c "ADF" -a "ANY" `
    -t "{{DownloadDirectory}}"

  • After downloading, extract the contents into the same directory as your cloned repository.

Directory Structure After Extraction

The folder after extraction should contain the following:

.
├── Setup.ps1
├── main.cmd
├── dotnet-framework-35/
│   └── microsoft-windows-netfx3-ondemand-package~31bf3856ad364e35~amd64~~.cab
└── melissa-web-installer/
    └── ADF-DQC-Web.exe
../../_images/SSIS_AzureInstall_Directory.png

Note

If you cloned the repository from Git, you may see README.md and LICENSE.md files in the folder. These files are not required for the SSIS-IR deployment and can be safely removed.

Step 3: Create an Azure Blob Storage Container#

  1. Open your Azure Storage Account (Blob storage).

  2. Create a new container (e.g., ssis-ir-deployment).

    ../../_images/SSIS_AzureInstall_BlobContainer.png

  3. Generate a SAS Token for this container.

    • Click the […] button on the right end of the container name and choose Generate SAS

    • Grant permission for the SAS to Read, Add, Create, Write, Delete, and List.

    • Be sure to copy the full SAS string.

      ../../_images/SSIS_AzureInstall_SASPermissions.png

Note

We recommend setting a long expiration date for the SAS token. Azure Data Factory may re-deploy the SSIS-IR at a later time using this token.

Step 4: Configure the Setup Script#

Open Setup.ps1 in a code editor and update the following variables:

# Blob Storage Information
$storageAccount  = "<your_storage_account_name>"
$containerName   = "<your_container_name>"

# SAS Token (add the leading '?')
$sasToken        = "<your_blob_container_SAS>" # This should look like "?sp=...&sig=..."

# Melissa Product License Key
$productLicense  = "<your_license_key>"

Save the file after making your changes.

../../_images/SSIS_AzureInstall_BlobScript.png
Step 5: Upload Deployment Files to Blob#

Upload all files and folders in this repo to the container you just created.

Important

Do not place files inside an additional folder. The SSIS-IR setup process expects main.cmd to be at the top level of the container.

../../_images/SSIS_AzureInstall_DeployementFiles.png
Step 6: Configure SSIS-IR in Azure Data Factory#

  1. Navigate to Azure Data Factory > Integration Runtimes.

    ../../_images/SSIS_AzureInstall_IntegrationRuntimes.png

  2. Click + New to create a new Azure-SSIS Integration Runtime.

    ../../_images/SSIS_AzureInstall_New.png

  3. During setup, under Integration runtime setup, be sure to:

    • Check the box: “Customize your Azure-SSIS Integration Runtime with additional system configurations/component installations”

    • Provide the full SAS URL to your blob container: https://<your_storage_account>.blob.core.windows.net/<your_container_name>?<your_sas_token>

    ../../_images/SSIS_AzureInstall_EditRuntime.png

This allows ADF to run your custom startup script and install Melissa SSIS components automatically during IR provisioning.

Step 7: Start the SSIS-IR#

On startup, the SSIS-IR will:

  • Install prerequisites (.NET Framework 3.5)

  • Install Melissa SSIS components

Logs & Troubleshooting#

You can monitor logs from the setup process directly in your blob container:

Folder Name

Description

custom-setup-logs/

Logs from the custom setup script

installer-logs/

Logs from the Melissa installer

If the SSIS-IR fails to start or install, check the latest files in those folders for detailed error messages.

Tips#

  • You can restart the SSIS-IR to re-trigger the setup.

  • Make sure you do not rename or move files from the repo before uploading.

Subscribe#

Subscriptions#

You must have an account with Melissa in order to use the full features of Melissa’s Data Quality Components for SSIS.

You may subscribe to Melissa’s Data Quality Components (DQC) for SSIS through the Melissa website at https://www.melissa.com/sql-server or by calling a Customer Service Representative at 800-MELISSA ext. 3 (800-635-4772 ext. 3).

Once you are subscribed, your Customer Service Representative will give you a License Key needed to use the Data Quality Components for SSIS.

Community Edition#

Some of Melissa’s Data Quality Suite Components have a Community Edition. This is a no-cost version of the component available to you for trying out the components.

To obtain a Community Edition License Key, contact a Customer Service Representative

You may subscribe to Melissa’s DQC for SSIS through the Melissa website at www.MelissaData.com/newuser or by calling a Customer Service Representative at 800-MELISSA ext. 3 (800-635-4772 ext. 3).

Once you have your Community Edition License Key you can use the Community Edition of the Data Quality Suite Components.

Limitations#

The Community Edition of these components have limited functionality. They provide a few basic abilities for you to sample our products, while some options may be greyed out. If you wish to unlock the full potential of our products, please subscribe.

Component

Full Edition

Community Edition

Global Verify Component

Global Address Verification & Standardization

✓

International Geocoding

✓

Contact Verification Component

Address Parsing

✓

✓

Name Parsing

✓

Last Name Only

Email Correction

✓

✓

Phone Formatting

✓

✓

Address Verification

✓

Does NOT Validate or Standardize

Name Verification

✓

Email Verification

✓

Phone Verification

✓

Geocoding

✓

Genderizing

✓

License Required

✓

MatchUp Component

Record Limit

Unlimited

50,000

Matchcodes *

19 Built-in

9 Built-in

Custom Matchcodes

✓

License Required

✓

Profiler Component

Record Limit

Unlimited

50,000

Result Codes

✓

License Required

✓

Generalized Cleanser Component

Record Limit

Unlimited

100,000

Available Tools

  • Casing

  • Punctuation

  • Abbreviation

  • Expression

  • Regular Expression Search Replace

  • Text Search and Replace

  • Casing

  • Punctuation

  • Abbreviation

  • Expression

  • Limited Expression Search Replace

  • Limited Text Search and Replace

License Required

✓
Contents