FAQs

Contents

FAQs#

Common Issues#

Invalid Permissions#

Insufficient privileges can prevent a project from successfully performing file operations at runtime. This issue is often related to the application’s permission level, so it is necessary to ensure that the program has the required permissions to write to any specified directories for successful task completion.

These errors can be accompanied by a 0x80004005 code.

Solution: Right-click on the program’s shortcut and select “Run as administrator.”

Data File Errors#

Outdated or missing data files can cause various error messages when using the On-Premise processing mode in the Contact Verify component. The underlying binaries expect all data files to be present in the designated directory, and the component will not function properly if there are any discrepancies.

Solution: Please follow the steps under the Resolving Data File Issues section in order to resolve any data file errors.

Memory Allocation Errors#

The size of the buffers allocated for the data flow will significantly impact the performance of an SSIS project. Larger buffers allow more data to be loaded into memory at once, while smaller buffers help manage memory consumption. Both configurations can enhance speed and functionality, so finding the optimal buffer size for a specific system is crucial.

Messages relating to memory errors may resemble:

  • “A buffer failed while allocating…”

  • “The Data Flow task failed to create a buffer to call PrimeOutput…”

Solution: There are three properties that can be used to modify the data flow’s buffers and mediate memory allocation issues.

  1. DefaultBufferMaxRows - This represents the maximum number of rows a buffer can hold before being processed and moved to the next component in the data flow.

  2. DefaultBufferSize - This value sets the maximum number of bytes in a buffer.

  3. AutoAdjustBufferSize - When set to True, this will dynamically adjust buffer size at runtime based on the system’s available memory and the amount of data being processed.

Missing Components#

If Melissa’s components do not appear in the SSIS Toolbox after a successful installation, there may be a mismatch between the Target Server Version configured in SSIS and the version of SQL Server used in the target environment on the system.

Solution: Please refer to the SSIS Setting Target Server Version Option section in order to modify the Target Server Version within SSIS to match the target SQL Server version.

Public Key Token Errors#

A mismatch between an SSIS project’s Public Key Token and the target SQL Server version can cause errors related to the Public Key Token, which may prevent the successful execution of the project.

Messages related to Public Key Token errors may resemble:

  • “Public Key Token could not be loaded.”

  • “Could not load file or assembly…”

Solution: Please refer to the How to Update Public Key Token section in order to correctly set the Public Key Token to the value specific to the target SQL Server version.

Melissa’s SSIS 2012 Platform Deprecation FAQ#

What should I do with my existing SSIS 2012 packages?#

Existing SSIS 2012 packages can be migrated into your new SSIS 2014+ environment.

How do I upgrade my existing packages for SSIS 2014+?#

To upgrade your existing SSIS 2012 to SSIS 2014+, follow these steps:

  1. Install Melissa’s SSIS components into your new environment.

  2. Open the package using Visual Studio 2013+.

  3. Edit the Public Key Token value for each of Melissa’s SSIS components contained within the package.

    • For instructions on how to obtain the new Public Key Token value for Melissa’s SSIS components and how to edit the Public Key Token value, visit our SSIS Wiki page: SSIS How to Update Public Key Token

    • Once the new Public Key Token has been added to your package, you can then save the package. Your package has now been updated.

    • Per Microsoft’s standards, each of Melissa’s SSIS components must contain a unique value for the Public Key Token for each platform.

What happens if my organization is not ready in time for the deprecation?#

You will still be able to use Melissa’s SSIS 2012 platform. However, after the July 24th, 2023 deadline, Melissa’s SSIS 2012 platform components will remain static and will not receive any further updates regarding new fields or options added to the Melissa SSIS components. Also, support will be limited for Melissa’s SSIS 2012 platform. To maintain security in your environment and to stay up-to-date with Melissa’s latest SSIS component features, please migrate to a SQL Server 2014+ environment.

What does this mean for me?#

If you currently use Melissa’s SSIS 2012 platform, which consists of Visual Studio 2010 and Microsoft SQL Server 2012, it is highly recommended to upgrade to a SSIS 2014+ environment. If you are currently using a SSIS 2014 + environment, then you are up-to-date and will not need to upgrade your environment.

Why are you doing this?#

SQL Server 2012 will no longer receive security updates, non-security updates, bug fixes, or technical support after Microsoft ends support on July 12th, 2022. To maintain security and support for our clients, we believe it is best to deprecate Melissa’s SSIS 2012 platform.

Will any other of Melissa’s SSIS supported platforms be deprecated anytime soon?#

Microsoft will end its extended support for SQL Server 2014 on July 9th, 2024. Melissa will then deprecate the SSIS 2014 platform. Existing clients will be notified of the SSIS 2014 deprecation event.

Can I expect the same features and updates from Melissa’s SSIS components when I upgrade to SQL Server 2014+?#

Yes, Melissa’s SSIS components are designed identically across all of our supported platforms. Deprecated SSIS platforms will no longer receive new features or updates and will remain static.

What about SSIS 2022? Will Melissa support the SSIS 2022 platform?#

Microsoft has released Visual Studio 2022. However, the SQL Server Integration Service template for Visual Studio 2022 has not yet been released. Melissa plans on supporting SSIS 2022 in the near future, pending the release of SQL Server Integration Services from Microsoft. The latest SSIS news and updates will be posted on Melissa’s blog. Stay tuned.

For assistance on transitioning to another of Melissa’s officially supported SSIS platforms, please contact Melissa’s Tech Support: Tech@melissadata.com

Which officially supported SSIS platform does Melissa recommend?#

Melissa recommends SSIS 2016+ platforms. While SSIS 2014 is supported by Melissa, we plan to deprecate the SSIS 2014 platform next, in early 2025. As Microsoft will also end support for SQL Server 2014 on July 9th, 2024.

What other officially supported platforms does Melissa plan on deprecating in the future?#

Here is an official timeline of Melissa’s SSIS platform deprecation’s planned:

1/1/2025

1/27/2025

7/26/2027

10/23/2028

SSIS 2012

Deprecated - Not Supported by Melissa

Deprecated - Not Supported by Melissa

Deprecated - Not Supported by Melissa

Deprecated - Not Supported by Melissa

SSIS 2014

Supported by Melissa

Deprecated - Not Supported by Melissa

Deprecated - Not Supported by Melissa

Deprecated - Not Supported by Melissa

SSIS 2016

Supported by Melissa

Supported by Melissa

Deprecated - Not Supported by Melissa

Deprecated - Not Supported by Melissa

SSIS 2017

Supported by Melissa

Supported by Melissa

Supported by Melissa

Deprecated - Not Supported by Melissa

SSIS 2019

Supported by Melissa

Supported by Melissa

Supported by Melissa

Supported by Melissa

Contact Verify Dedicated Cloud (Appliance Mode) Deprecation#

As part of our ongoing efforts to streamline and modernize our services, Melissa will be deprecating the Dedicated Cloud (Appliance Mode) for the Contact Verify SSIS Component on May 5th, 2025.

Impact#

SSIS Contact Verify Users on Dedicated Cloud:

Users on the Dedicated Cloud mode for Contact Verify in SSIS will no longer be able to use this feature.

Melissa Data Cloud Users:

If you are already using the Melissa Data Cloud, this change will not affect your service, and no action is required.

Frequently Asked Questions#

Why is Melissa deprecating the Dedicated Cloud (Appliance Mode)?

This deprecation is part of our effort to modernize our services and deliver a more efficient, streamlined experience for users. Continuing to support the Dedicated Cloud mode is no longer feasible due to evolving technology trends and advancements in cloud-based solutions.

What are my options moving forward?

You can migrate to either the Melissa Data Cloud or on-premise solutions. Both options will continue to support the Contact Verify functionality within SSIS, ensuring a smooth transition without service interruptions.

How can I migrate my services?

For guidance on migration, please reach out to your Melissa account representative. We are here to assist you throughout the transition process and answer any questions you may have.

Data Quality Components for SSIS#

What is affected during an install or upgrade?#

By default the data is stored at this path: C:\Program Files\Melissa DATA\DQT\Data

  • Sample Projects is located

    • C:\Program Files\Melissa DATA\DQT\DQS\Samples

  • Copy of Component DLLS:

    • C:\Program Files\Melissa DATA\DQT\DQS\SSIS_2005

    • C:\Program Files\Melissa DATA\DQT\DQS\SSIS_2008

    • C:\Program Files\Melissa DATA\DQT\DQS\SSIS_2012

    • C:\Program Files\Melissa DATA\DQT\DQS\SSIS_2014

  • Object DLLS:

    • C:\Program Files\Melissa DATA\DQT\DQS\32_bit

    • C:\Program Files\Melissa DATA\DQT\DQS\64_bit

  • Copy of Config Files:

    • C:\Program Files\Melissa DATA\DQT\DQS\Config

  • Affected During Update or Install:

    • Component Pipeline Dlls:

      • C:\Program Files\Microsoft SQL Server\110\DTS\Binn

      • C:\Program Files (x86)\Microsoft SQL Server\110\DTS\Binn

    (Note That 110 refers to SQL SERVER 2012, please look for your corresponding version)

    • Active Config Used by SSIS:

      • C:\ProgramData\Melissa DATA

    • MatchUp Data Files and Configurations:

      • C:\ProgramData\Melissa DATA\MatchUP

(mdMatchup.mc stores your matchcode and will not be overwritten if one exists, you can copy this to a new install to transfer matchcodes.)

How do I enable On-Premise Global Verify?#

Please make sure that you have a valid License Key that has the local processing enabled. Otherwise the on-premise settings in advanced configuration settings screen will not be visible. You need to set the countries that you want to be processed through locally, the other countries that is not selected will be processed through the Global Verify web service. Please make sure your datafile structures is correct and all datafiles is present, otherwise the dll may crash or reject your settings. When everything is set you can now enter the path for the root folder of your data files into the data path field. You can test to see if you have everything set correctly by pressing test configuration. The tester will report any issues that it finds with your setup. Please contact technical support if you are still having issues with your setup and we will gladly help you out.

How come I can’t process less than 100 records for SmartMover NCOA?#

USPS regulations state we cannot process NCOA for files less than 100 records. In adherence to the new regulations, SmartMover now checks the first batch to see if it contains less than 100 records. If the batch is less than 100 records we will reject the batch and return an error message. SmartMover will interrupt the processing after processing begins. Please contact your sales representative if you have any questions or concerns regarding this change.

Can we save current settings for DQC for future?#

Yes, Melissa SSIS includes the feature that you can save currents settings for future use. Under File Tab you can save current setting in a .cfg file in local your drive. Under same File tab click “Open” and upload the .cfg file.

Speed Facts for CVC?#

EVC is an enterprise component so performance of EVC is highly dependent on machine configuration. Operating System, RAM, Processor or using network drive for data input or output, make significant impact on performance of EVC. In addition performance also depends upon 2 more main factors:

How you are using EVC?#

It is highly recommended for EVC users, select the only those features i.e. LACSLink(Address Add-on) DNS Lookup(Email, Add-on), which are required. Unnecessary use of add-on and feature will effect the performance.

Type of Data?#

If the input data is really corrupted or having lot of errors, will effect the performance. We have additional 2 different scenarios for using EVC, which will help you to analyse the throughput of the component in different situations.

Profiler Speed?#

Different analysis features directly affect component performance. Options with data aggregation and sort analysis have a high impact on speed of the component. For a list of speed tests, see Profiler FAQ Service Speed.

How do I make Excel Source Drivers work?#

The version of the OLE Drivers must match your Office edition (32bit vs 64bit) in your project properties. If you have 32bit Office, click Project then Properties. Under configuration properties, click “debugging” and set “Run64BitRunTime” to False. If you have 64bit Office then set “Run64BitRunTime” to True.

In which format Data Quality Components(DQC) accept input?#

You can use any format as input for Melissa SSIS components. It can be .csv, .xls, .dbf, xlsx, SQL database etc. In some cases you might not see any column in drop down options of MD SSIS component, in this case you can use “Data Conversion”(in-built component in BIDS) to change the data type of particular missing column to “DT_WSTR”.

How we can update individual Melissa Object rather than updating whole CVC?#

EVC component installs all Melissa Objects in “Binn” folder under DTS folder of SQL Server. If you are using EVC in SQL Server 2008 and need to update only Address Object grab latest mdAddr.dll and replace older mdAddr.dll in following location:

  • C:\Program Files\Microsoft SQL Server\100\DTS\PipelineComponents

Same way you can update other Melissa Objects i.e for Email Object replace mdEmail.dll, for GeoCoder mdGeo.dll, etc.

If you are installed EVC on SQL Server 2005, the location of Melissa Object will be in following location:

  • C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents

Microsoft Fuzzy Lookup vs. The Data Matching and Cleansing Component?#

The Microsoft transforms are only available with SQL Server enterprise edition which is a significant cost in itself if your organization doesn’t have it already.

All of the following transforms are SQL Server Enterprise edition only:

  • Data Mining Training Destination

  • Data Mining Query Component

  • Fuzzy Grouping

  • Fuzzy Lookup

  • Term Extraction

  • Term Lookup

  • Dimension Processing Destination

  • Partition Processing Destination

The MD Fuzzy Matching Component allows more granular control over matching whereas the SSIS native fuzzy matching does not allow any control of its algorithms or access to multiple algorithms. There are only two configurable parameters with the stock Fuzzy lookup and grouping components ie: similarity threshold and maximum number of results per input record.

The MD Fuzzy Matching component has 16 configurable matching algorithms some of them developed specifically by us such as the unique Keyboard distance for misspellings. All industry standard matching algorithms are supported, and probabilistic match settings can be configured for each column of data and the rules can be daisy chained for maximum catch all situations. Also the component has the ability to ignore common data quality issues when matching. ie; whitespaces, irregular characters, etc.

The Melissa Fuzzy Matching Component can also inherently cleanse your data through regular expressions. Regular expressions can be built and tested through an expression builder which is also built in to the component. Cleansing your data prior to matching is a crucial step in order to get the most accurate results for matching.

The component also is able to automatically split up the results into 3 destinations: Matches, Possible Matches and Non-Matches. Based off the percentage score between 2 records, the component will re-direct the results to the appropriate destination according to the percent thresholds you’ve set in the component. Also the Fuzzy SSIS component has full metadata (source and compare scores for multiple algorithms) for tracking record lineages.

One more thing to bear in mind is that, the stock Fuzzy Lookup component in SSIS requires that your reference table be a SQL Table. The Fuzzy Matching Component does not limit you to a SQL Table Reference Database.

The DMCC suite also comes with the MatchUP component, which is specially designed for the unique set of matching problems with Address, Name and Company data fields. MatchUp recognizes combinations, inverse names, Acronyms, Nick names, numerical streets such as 12th vs. twelfth, and so on.

What Versions of SQL Server/Visual Studio Is Your Components Supported in?#

Melissa currently supports SQL Server versions 2012, 2014, 2016, 2017, and 2019. 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.

Officially Supported by Melissa#

Data Type Conversions#

Conversion chart of SSIS data types to SQL Server data types

SSIS Data Type

SSIS Expression

SQL Server

single-byte signed integer

(DT_I1)

two-byte signed integer

(DT_I2)

smallint

four-byte signed integer

(DT_I4)

int

four-byte signed integer

(DT_I4)

int

eight-byte signed integer

(DT_I8)

bigint

eight-byte signed integer

(DT_I8)

bigint

float

(DT_R4)

real

double-precision float

(DT_R8)

float

string

(DT_STR, «length», «code_page»)

char, varchar

Unicode text stream

(DT_WSTR, «length»)

nchar, nvarchar, sql_variant, xml

date

(DT_DATE)

date

Boolean

(DT_BOOL)

bit

numeric

(DT_NUMERIC, «precision», «scale»)

decimal, numeric

decimal

(DT_DECIMAL, «scale»)

decimal

currency

(DT_CY)

smallmoney, money

unique identifier

(DT_GUID)

uniqueidentifier

byte stream

(DT_BYTES, «length»)

binary, varbinary, timestamp

database date

(DT_DBDATE)

date

database time

(DT_DBTIME)

database time with precision

(DT_DBTIME2, «scale»)

time(p)

database timestamp

(DT_DBTIMESTAMP)

datetime, smalldatetime

database timestamp with precision

(DT_DBTIMESTAMP2, «scale»)

datetime2

database timestamp with timezone

(DT_DBTIMESTAMPOFFSET, «scale»)

datetimeoffset(p)

file timestamp

(DT_FILETIME)

image

(DT_IMAGE)

image

text stream

(DT_TEXT, «code_page»)

text

Unicode string

(DT_NTEXT)

ntext

Component Specific FAQ & Optimizations#

Matchup Component FAQ#

Basics:

Advanced:

Contact Verify Component FAQ#

Personator Component FAQ#

Profiler Component FAQ#

Global Verify Component FAQ#

SmartMover Component FAQ#

IP Locator Component FAQ#

Property Component FAQ#

BusinessCoder Component FAQ#

GeoCode Logging Advisory#

Reprocess Inputs Update#

SetLatitude

Use of SetLatitude will prevent records previously coded with GeoCoder Object from being counted towards your overall usage. By setting these properties to the values previously returned from GeoCoder Object, any records whose current values match the values set in the properties will not be counted against your total usage.

SetLongitude

Use of SetLongitude will prevent records previously coded with GeoCoder Object from being counted towards your overall usage. By setting these properties to the values previously returned from GeoCoder Object, any records whose current values match the values set in the properties will not be counted against your total usage.

SSIS 6.4: GeoCode Logging Advisory#

2016 Q4

In order to utilize your on-premise GeoCoder, you must open up a port so you may communicate with our servers (no private data is transferred through this port – this is purely for tracking purposes). Please download this file ftp://ftp.melissadata.com/updates/GeoLogTest.zip and follow the steps providing below to so we may assist you faster in providing a solution to your problem. This tool is to detect whether or not our servers are able to communicate with you or not.

  1. Ensure you have the following files in your C:\Program Files\Melissa DATA\DQT\Data (or equivalent) folder.

    mdGeoCanada.db
mdCbsa.dat
mdCbsa.idx
mdGeo.cty
mdGeo.dat
mdGeo.idx
mdGeo.lic
mdGeo.plc
mdGeoPoint.dat
mdGeoPoint.idx

  2. Unzip the Downloaded file, and move it to your desired location.

  3. Select x86 or x64 bit depending on your system architecture (if unknown, select x86).

  4. Double click “GeoLogTest.exe” and click OK.

  5. Enter your License Key in the License Field.

  6. Enter a directory for the log file to be created (Optional).

    1. If you leave as-is, the log file will be created in the same directory as your GeoLogTest.exe

    2. If you choose to leave it blank, a console window will pop up with your logging information.

  7. Ensure your DataPath is set to the correct location (Where your Data folder is inside your Melissa Folder).

  8. Set your postal code (Optional).

  9. Set the postal codes Plus 4 (Optional).

  10. Click “Lookup”.

  11. Send the trace log to your Melissa Tech Support Representative and we will determine the solution that best fits your needs.

SSIS Install Other Than C: Drive#

If you want to install your SQL Server to a path other than the default C: drive, follow this guide.

  1. Open Command Prompt as Administrator.

  2. cd to where the installer is located on your machine.

  3. Type the installer name along with the command /NoDetectSSIS

    ../../_images/SSIS_FAQ_OtherThanC_installer.jpg

  4. Once the installer appears, accept the agreement and click Next.

    ../../_images/SSIS_FAQ_OtherThanC_agreement.jpg

  5. Select which version of SQL Server Integration Services is installed.

    ../../_images/SSIS_FAQ_OtherThanC_version.jpg

  6. Select the SSIS root directory. Here you can modify where to install SSIS.

    ../../_images/SSIS_FAQ_OtherThanC_directory.jpg

  7. Enter your license string, and then select the components you want installed. The Melissa components will now be installed to the chosen directory.

SSIS How to Update Public Key Token#

In order to update your existing projects to a newer version of SQL Server, you will need to edit the PublicKeyToken in the code designer page.

Before starting this process we recommend obtaining a list of all the components that utilize PublicKeyToken in the OLD projects.

Updating the PublicKeyToken in Existing Projects

Warning

Please back up the original project file before starting.

  1. Locate the Package.dtsx by going to View > Solution Explorer.

    ../../_images/SSIS_FAQ_PublicKey_01-LocatePackage.png

  2. Go to the Data Flow tab and drag a NEW component from the SSIS Toolbox in order to get the NEW PublicKeyToken.

    ../../_images/SSIS_FAQ_PublicKey_02-NewComponent.png

  3. Right click the Package.dtsx on the right side in the Solution Explorer and click View Code (or alternatively hit F7). The following window should come up:

    ../../_images/SSIS_FAQ_PublicKey_03-ViewCode.png ../../_images/SSIS_FAQ_PublicKey_04-ViewCode.png

  4. Press CTRL+F to open the Find and Replace window. Type PublicKeyToken in the Find what: field and click Find Next to locate and copy the NEW token value to wordpad. Save this token, it will be used to update the older projects experiencing the issue. (Ex. PublicKeyToken=aa80e80ec340a80c)

    ../../_images/SSIS_FAQ_PublicKey_05-FindandReplace.png

  5. Open the old project to be updated in Visual Studio.

    ../../_images/SSIS_FAQ_PublicKey_06-OldProject.png

  6. Right click Package.dtsx on the right side under the Solution Explorer and click View Code.

    ../../_images/SSIS_FAQ_PublicKey_07-ViewCode.png

  7. Use Ctrl+F to search for PublicKeyToken in the project to be updated.

    ../../_images/SSIS_FAQ_PublicKey_08-FindandReplace.png

  8. Once it is found, paste the NEW PublicKeyToken that was collected in step 4, in place of the existing one. Save the project. Now the PublicKeyToken has been updated. (Save, close, and then reopen.)

SSIS Setting Target Server Version Option#

In Visual Studio, verify that the Target Server Version option is set to the corresponding SQL Server instance on the machine.

  1. To set the Target Server Version, in Visual Studio’s Solution Explorer, right click on the SSIS project Node and click Properties

    ../../_images/SSIS_TSV-Step2.png

  2. Select Configuration Properties. You will see the Target Server Version; select the desired version of SQL Server.

    ../../_images/SSIS_TSV-Step3.png

  3. Once set, the project will reload. Double click the .dtsx package in the Solution Explorer menu and our SSIS components should appear in the SSIS Toolbox.

    ../../_images/SSIS_TSV-Step4.png

SSIS Service Speed#

Melissa’s SSIS Enterprise level components can process hundreds of thousands of records per hour. SSIS is available in 2 processing modes, Cloud and On Premise, which will affect throughput speeds.

Major Factors#

Major factors impacting SSIS processing speed include:

  • Number of threads or processes

    • SSIS Cloud Processing components can handle multiple requests, increasing throughput speeds.

    • We recommend up to 10 threads for our web services; if you require significantly more speed, contact your sales representative to discuss available options.

    • We recommend creating multiple instances of our on-premise components to increase speed, utilizing a Balanced Data Distributor component to send data to each instance.

  • Type of input method provided

    • Using Melissa Address Key (MAK) as input will process faster since MAK is a globally unique identifier

    • Using parsed data elements (such as AddressLine1, City, and State) instead of unstructured data such as Free Form inputs will result in faster processing speeds.

  • Quality of input data

    • Data of poor quality that requires additional input, corrections and standardization will reduce processing speed

  • Number of output columns

    • Requesting additional output columns will reduce processing speeds as more data is requested from our services

  • Premium Mode Processing

    • Phone and Email services offer Premium mode processing, which will reduce processing speeds due to performing live requests that provide higher accuracy data

  • Type of SSIS input source utilized

    • SSIS can use a variety of input sources available via SSIS Toolbox. We recommend OLE DB or Flat file source for faster processing

  • Data Flow Buffer Size - DefaultBufferMaxRows and DefaultBufferSize Properties

    • The buffer size controls the amount of rows transferred by the data flow. Specifications for buffer size vary due to environment and input source speed. Typically, a slow source input speed will require a smaller buffer size

  • Adding Options and Actions

    • Including additional options or actions from our services will reduce processing speeds as additional data is requested or additional logic is utilized

  • Utilizing SQL Server Agent/Command Prompt to run projects

    • Processing speed can increase by utilizing SQL Server Agent or by running the project via Command Prompt. Process quicker without the overhead of Visual Studio

On Premise Components#

Speeds will vary due to hardware differences; higher speeds may be achieved with multiple instances of on-premise components.

SSIS Component

Throughput (records / hour)

Contact Verify – Name, Address, Phone, Geo, Email (Express)

500,000 – 600,000

Generalized Cleanser

9,000,000 – 12,000,000

MatchUp (Basic Match Code)

3,000,000 – 5,000,000

Profiler

800,000 – 1,250,000

Cloud Processing Components#

SSIS Component

Throughput (records / hour)

Contact Verify – Name, Address, Phone, Geo, Email (Express)

90,000 – 160,000

Contact Verify – Name, Address, Phone, Geo, Email (Premium)

50,000 – 70,000

Personator (Check, Verify)

200,000 – 220,000

Personator (Check, Verify, Move)

170,000 – 190,000

Personator (Check, Verify, Move, Append – Always)

140,000 – 160,000

SmartMover NCOA

1,500,000 – 2,000,000

SmartMover CCOA

450,000 – 490,000

BusinessCoder

220,000 – 270,000

Property

200,000 – 260,000

Global Verify – Name, Address, Phone (Express), Email (Express)

110,000 – 150,000

Global Verify – Name, Address, Phone (Premium), Email (Premium)

60,000 – 85,000

IPLocator

5,000,000 – 6,000,000

Personator World – Check – Name, Address, Phone (Express), Email (Express)

80,000 – 100,000

Resolving Data File Issues#

How do I resolve issues with data files?#

../../_images/SSIS_CV_TestConfigurationResults.png

(Note: The error you encounter might differ from the examples shown here.)

Cause: Various issues can occur with data files for the Contact Verify Component, such as an incorrect path to the data files path or outdated/missing data files.

Solution: Follow these steps to ensure the configuration is correct:

Step 1: Check for Required Data Files: Ensure you have all the necessary data files for each on-premise object. The required files can be found here:

  1. Name Object

  2. Address Object

  3. GeoCoder/GeoPoint Object

  4. Phone Object

  5. Email Object

Step 2: Verify Data File Path Configuration: Confirm the Data File Path points to the correct location. By default, this path is C:\Program Files\Melissa DATA\DQT\Data.

Step 3: Update Outdated Data Files: If the data files exist and the Data File Path is configured correctly but errors persist, your data files might be outdated. Consider the following actions:

Option 1: Run the Full On-Premise SSIS Installer

  1. SSIS builds are released quarterly. You should receive emails from the sales rep with download links for the latest installer. Running this will update the data files.

  2. If you aren’t receiving these emails, please contact your Sales Rep or our Tech Support.

Option 2: Download Data Files Manually

  1. Use Melissa’s Releases page to download the latest data files using your License String. Links for each object:

    1. Name Object - https://releases.melissadata.net/browse?MFT=dq_name_data&RELEASE=latest&ID={YourLicense}

    2. Address Object - https://releases.melissadata.net/browse?MFT=dq_addr_data&RELEASE=latest&ID={YourLicense}

    3. GeoCoder/GeoPoint Object - https://releases.melissadata.net/browse?MFT=geocoder_data&RELEASE=latest&ID={YourLicense}

    4. Phone Object - https://releases.melissadata.net/browse?MFT=dq_phone_data&RELEASE=latest&ID={YourLicense}

    5. Email Object - https://releases.melissadata.net/browse?MFT=dq_email_data&RELEASE=latest&ID={YourLicense}

Option 3: Download Data Files Automatically

  1. Use the Melissa Updater, which checks for missing/outdated files and automatically downloads and replaces them.

  2. Melissa Updater Download/Tutorial

Known Issues#

SSIS 2014 BusinessCoder#

SSIS 2014 BusinessCoder Subscribers

If using SSIS 2014 BusinessCoder component for SSIS, we recommend to use Visual Studio 2013 per Melissa’s SSIS System Requirements

Currently the SSIS 2014 BusinessCoder component is not support by the Target Server Version option that is available in Visual Studio 2015+ when used in correlation with SQL Server 2014. Which causes the BusinessCoder component not to appear in the SSIS Toolbox.

To download Visual Studio 2013, please visit: Visual Studio Older Downloads - 2019, 2017, 2015 & Previous Versions

A Microsoft account is required for access.

SSIS Q3 9.3: Personator Date of Birth (DOB) and Date of Death (DOD) Fields are now accessible without a Demographics subscription in SSIS#

In the previous release of SSIS, the DOB and DOD fields were not functioning if a Demographics subscription was not active on the account.

Patched Q3 9.3 DQC Personator Component for SSIS#

A patch fix has been released and only applies to the Personator component. Please download the patch version if you are experiencing the issue.

When installing the patch, in the “Select Components” screen, please only select Personator (v1.0.0.5055)

../../_images/SSIS_Notice_9p3.png

Components Only ~246 MB

SSIS 9.1: Contact Verify – On Premise – Valid Canadian addresses are not receiving MAK when Country is set to Canada#

When Country is set to Canada in the Contact Verify Address Verify Options screen, valid Canadian addresses are not receiving a MAK.

We recommend to set the Country option to “US & Canada” as a work around for the issue.

SSIS 9.1: Contact Verify - On Premise - Address Object returning MAK for invalid Canadian records#

Invalid Canadian addresses are returning a Melissa Address Key MAK.

Patched 9.1 DQC Contact Verify Component for SSIS#

A patch has been released for the Contact Verify component. Please download the patch version if you are experiencing the issue

SSIS 8.1: SmartMover CCOA Unhandled Exception#

An unhandled exception would occur in SSIS with the SmartMover component during CCOA processing only. Affected builds include Smartmover 5008-5011.

Patched 8.1 DQC SmartMover Component for SSIS#

A patch fix has been released and only applies to the SmartMover component. Please download the patch version if you are experiencing the issue.

On the install prompt, only select the SmartMover component to be updated

../../_images/SSIS_Notice_8p1.png

Components Only ~211MB

SSIS 8.1: Global Verify Pass-Through Column Records#

The Global Verify component is currently experiencing an issue with the records contained within the Pass-Through columns being out of sync with the processed columns only when Email and/or Phone services are used. The records in the pass through columns repeat when the record per transmission is greater than 10 or if the dataset is greater than the threads multiplied by the records per request. As a work around we suggest to set the records per transmission to 10 via the configuration file.

Patched 8.1 DQC Components for SSIS#

We have released a patch fix for both web installer/ISO that are now available and live. This patch only applies to Global Verify Component. If you have already downloaded DQC Components for SSIS prior to 11/29/17, please download one of the patch versions below.

Components Only ~211MB

Installer with Components and data files included ~11GB

SSIS 7.4: Global Verify Pass-Thru records Issue#

Clients using Global Verify component, there is an important update that includes a patch for the 7.4 Q4 release of SSIS. We noticed that pass-thru records repeat whenever records per request is set to 100 and sample size of the record set exceeds 500 records. This leads to the issue of keeping the records in sync. The pass-thru address column is the only one effected. The SSIS address output column remains in sync and is not effected. The screen shot below shows the output file from Global Verify and it contains the pass-thru column of “Input Address” and the address output of “MD_Address1”. Notice the order is in sync until record 502.

../../_images/SSIS_Notice_7p4.png

We have released a patch fix for both web installer/ISO that are now available and live. This patch only applies to Global Verify Component. If you have already downloaded DQC Components for SSIS prior to 11/29/17, please download one of the patch versions below.

Patched 7.4 Q4 DQC Components for SSIS#

Components Only ~211MB

Installer with Components and data files included ~11GB

SSIS VMWare: SQL Server 2016 Compatibility with VMWare#

Please avoid utilizing the option Hot Swap CPU when hosting with SQL Server 2016 with VMWare. We discovered that this is against the best practices from VMWare (3.3.6 CPU Hot Plug). Engineers from both VMware and Microsoft has verified that this will lead to issues with thread contention. The setting MAXDOP if set to too high will also have an effect on the contention and SSIS processes/our components may hang especially with our Matchup Component.

As reported from our client, reports show that when querying SQL Server Numa in DMV (sys.dm_os_memory_nodes) only one node will be allocated 100% memory. After disabling Hot Swap CPU, SQL Server now correctly detects a single numa. Disabling Hot Swap CPU will increase performance and should prevent hanging issues from occurring in SSIS.

This Hot Swap CPU issue is only reported for SQL Server 2016 edition, which we may think is caused by the new cardinality estimator and automatic soft numa default features. The combination of new changes and incorrect setup in VMware may have caused the thread waits past a certain threshold.

SSIS 6.4: SQL Server 2016 Compatibility#

2016 6.4 Q4 Release officially supports SQL Server 2016. We recommend using the latest SSDT Build. Please test on a staging server to ensure that all previous processes is working as it should before utilizing in production. We will make an effort to resolve bugs and issues to best of our abilities in a timely manner.

SSIS 6.3: SQL Server 2016 Compatibility#

2016 6.3 Q3 Release supports SQL Server 2016 as a BETA. We recommend using the latest SSDT July Build. SQL Server 2016 will not be officially supported at this time, we cannot guarantee stability and caution to use at your own risk. This 2016 SQL Server BETA release is meant to allow for testing and should not be utilized in production. We will make an effort to resolve bugs and issues to best of our abilities in a timely manner. We plan to officially support 2016 SQL Server in 2016 Q4 Release.

SSIS 5.4: MatchUp Unhandled Exception Error#

We discovered an issue with the 64bit Matchup DLL in SSIS, which hangs with unhandled exception error when operated in 64bit mode with a certain data-type and modified pre-canned matchcode.

Resolution

As a workaround, you can use the 32bit version by switching run64bitruntime to false in project properties or use parameters to force 32-bit mode in DTExec. Matchup should run fine in 32bit mode.

SSIS 5.4: MatchUp Mapping Addressline 4-8 Issue#

Users that update to 5.4 may experience mapping issues with address 4-8 in Matchup Component.

Resolution

Updating to patch build 2683 will resolve this issue. Please download from https://download.melissadata.com/updates/DQC-web.exe

SSIS 5.3/5.4: MatchUp Expired Database without Updates#

Users who do not update to MatchUp build 2683 will experience an expired database (actual expiration date is 2015-12-29).

Resolution

Updating to build 2628 (or newer future builds) will enable Global Matching for supported Countries but may require changes to your application (see build history), and are not effected by an expiring database date.

Users who do not update and need an mdMatchUp.dat with an extended expiration date, please contact our tech support team at tech@melissadata.com or 1-800-800-6245 x4.

SSIS 5.3: Global Verify On Premise Crashes#

SSIS will crash when specific combination of visual studio and SSIS causes runtime to use only 32 bit processing. For now, we officially only support 64 bit version processing. You can check if the correct behavior is exhibited, by opening process manager and see if there are both 32bit and 64bit DtsDebugHost is running. If only 32bit is running, we do not officially support 32bit run time. We are working hard to provide a stable 32bit runtime DLL.

Resolution

This is a Microsoft SSIS known issue as they left out DTExec 64-bit in SSDT-BI/2013, we provide both 64bit and 32bit dlls but 32bit DLL is not officially supported by Melissa. As of current our object is meant to be run in 64bit runtime. If during processing SSIS uses only the 32bit instead of the 64bit. Please the below help links and try the solutions provided by Microsoft. We will notify when we have 32bit version ready.

Known Setup Issues
Installed SSIS 2012, Using Visual Studio 2012, Visual Studio 2010 Installed

Behavior: Uses two 32bit DtsdebugHost and crashes

Known Working
Installed SSIS 2012, Installed Visual Studio 2012, Using Visual Studio 2010

Behavior: Working

Installed SSIS 2014, Using Visual Studio 2013

Behavior: Working

SSIS 5.3: Global Verify License Key Issue#

SSIS will complain about License Key Issue.

Resolution

Fixed in patched build 2575. Please download from HTTPS https://download.melissadata.com/Updates/DQC-web.exe

SSIS: Matchup UK Matchcode Not Saving#

When using the Matchup UK Matchcodes, it doesn’t save the UK matchcodes.

Resolution

Fixed in patched build 2575. Please re-download from the notification email, it has been updated with the fix. https://download.melissadata.com/Updates/DQC-web.exe

Upgrading Global Address with Global Name, Phone, and Email#

Current users of the Global Verify Component who are only subscribed to Global Address Check and want to upgrade using either Global Name, Phone or Email, are required to re-install the components and enter their new License Key in the installer.

../../_images/SSIS_Install_License.png

Upgrading your License Key in the Component GUI using File > Advanced Configuration will currently not work. Alternatively, you can manually change your License Key by editing the GlobalVerify.SSIS.Config under:

C:\\ProgramData\\Melissa DATA\\GlobalVerify

and replace your License Key for this entry:

<License>ENTER NEW LICENSE KEY HERE</License>

Save the file and open SQL Server Data Tools. You should now be able to use the Global Verify Component with Global Address, Name, Phone and Email.

SSIS: Matchup - Date Keys are Not Being Built Correctly#

When using the Component Type: Date in your MatchCode, Date Keys sometimes do not get built in the MatchKey. This is due to a conflict in the expected input format for Dates. Date Inputs should be in the format ‘yyyMMdd’. This becomes problematic particularly for some DBMS. SQL Server for example may have a Date Column defined with char[30]. This causes trailing spaces to be included in the Date Input which conflicts with the expected Input Format, causing the Date Key to not be built.

Future Resolution

We will be adding a fix in a later build to trim the trailing white spaces for Date Inputs.

SSIS: Matchup Matchcode Edits Not Saving#

When using the Matchcode Editor, when selecting [OK] after editing a matchcode and then reopening the matchcode editor, the changes have not been saved.

Resolution

Check the location of your Matchcode data files (File > Advanced Configuration). This is usually C:\ProgramData\Melissa DATA\MatchUP unless you have overridden the installer. Right Click the MatchUp folder and give ‘Full Control’ permissions to the respective user group.

Azure Data Factory (ADF)#

How do I deploy SSIS packages from Visual Studio for execution by SSIS-IR in ADF?#

This guide covers how to deploy an SSIS package that uses Azure SQL Server for both input and output. If you plan to use a different input/output method, the configuration steps will vary.

In this approach, the package is deployed to the SSISDB database on Azure SQL Server. You can then trigger its execution in Azure Data Factory by starting a pipeline that invokes the SSIS Integration Runtime (SSIS-IR).

Important

The SSIS package must be built for SQL Server 2017, as SSIS-IR in Azure Data Factory currently only supports SQL 2017 packages.

Step 1: Create a 2017 SSIS Package In Visual Studio With Input and Output being Azure SQL Server#

../../_images/SSIS_FAQ_ADF_Step1.png

Step 2: Deploy the SSIS Package to Azure SQL Server#

  1. Navigate to Project (top-left menu) → Click Deploy.

    ../../_images/SSIS_FAQ_ADF_Step2-1.png

  2. In the Integration Services Deployment Wizard, click Next.

    ../../_images/SSIS_FAQ_ADF_Step2-2.png

  3. Under the Deployment Target tab:

    • Choose SSIS in SQL Server or SSIS in Azure Data Factory (both will deploy your package to the SSISDB of your Azure SQL Server).

    • Click Next.

    ../../_images/SSIS_FAQ_ADF_Step2-3.png

  4. Under the Select Destination tab, enter the following details:

    • Server Name: Enter your Azure SQL Server name.

    • Authentication: Select SQL Server Authentication.

    • Login & Password: Use the credentials created when setting up the Azure SQL Server.

    • Click Connect to verify the connection.
      • If the connection is successful, the Path text box will become active:

        • Click Browse…

        • Under SSISDB, click New Folder… to create a subfolder for SSIS packages (unless you already have one that you would like to use).

        • Click OK, then Next.

    ../../_images/SSIS_FAQ_ADF_Step2-4.png

  5. Under the Review tab:

    • Review the deployment details.

    • Click Next.

    ../../_images/SSIS_FAQ_ADF_Step2-5.png

  6. Under the Results tab:

    • If everything is configured correctly, a success message will confirm that your package has been deployed successfully.

    Note

    The SSIS Integration Runtime (SSIS-IR) instance linked to this Azure SQL Server must be running at the time of package deployment.

    ../../_images/SSIS_FAQ_ADF_Step2-6.png

Step 3: Execute Package in Azure Data Factory (ADF) Environment#

  1. Navigate to your Data Factory (V2) instance on the Azure Portal.

  2. Click Launch Studio.

    ../../_images/SSIS_FAQ_ADF_Step3-2.png

  3. On the left-hand side, click Author.

    ../../_images/SSIS_FAQ_ADF_Step3-3.png

  4. Click Add New Resource → Pipeline → Pipeline.

  5. In the search box, type Execute SSIS Package, then drag and drop it into the pipeline.

  6. Rename the Pipeline and Execute SSIS Package as desired.

    ../../_images/SSIS_FAQ_ADF_Step3-6.png

  7. Configure the SSIS Package Execution. Go to the Settings tab:

    • Azure-SSIS IR: Select your SSIS-IR instance from the dropdown (Ensure it is active and running).

    • Package Location: Choose SSISDB from the dropdown, then click Refresh to display all newly deployed packages.

    • Folder - Project - Package: Select the path of your package. If you haven’t renamed the package file, it should default to package.dtsx.

    ../../_images/SSIS_FAQ_ADF_Step3-7.png

  8. Click Debug.

    ../../_images/SSIS_FAQ_ADF_Step3-8.png

  9. You will be directed to the Output tab where you can track the execution status of your package. The status will indicate whether the package succeeds or fails.

    ../../_images/SSIS_FAQ_ADF_Step3-9.png
Contents