FAQs

FAQs#

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:

Install Melissa’s SSIS components into your new environment.
Open the package using Visual Studio 2013+.
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

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

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 #

Microsoft SQL Server 2019 - Microsoft Visual Studio 2019
- SQL Server Data Tools for Visual Studio 2019 – Select SQL Server Data Tools from the Data Storage and Processing section – https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-ver15
- SQL Server Integration Services Projects – https://marketplace.visualstudio.com/items?itemName=SSIS.SqlServerIntegrationServicesProjects
  - Can also be installed from Visual Studio 2019 Tools > Extension and Updates Section.
Microsoft SQL Server 2017 - Microsoft Visual Studio 2017
- SQL Server Data Tools for Visual Studio 2017 - https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017#ssdt-for-vs-2017-standalone-installer
Microsoft SQL Server 2016 - Enterprise Edition and RC2 - Microsoft Visual Studio 2015
- SQL Server Data Tools for Visual Studio 2015 (14.0.61021.0) - https://msdn.microsoft.com/en-us/mt186501.aspx?f=255&MSPPError=-2147217396
Microsoft SQL Server 2014 - Microsoft Visual Studio 2013
Microsoft SQL Server 2012 - Microsoft Visual Studio 2010/2012
Microsoft SQL Server 2010 - Microsoft Visual Studio 2008 - Deprecated
Microsoft SQL Server 2008 - Microsoft Visual Studio 2005 - Deprecated

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

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.

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

Unzip the Downloaded file, and move it to your desired location.
Select x86 or x64 bit depending on your system architecture (if unknown, select x86).
Double click “GeoLogTest.exe” and click OK.
Enter your License Key in the License Field.
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.
Ensure your DataPath is set to the correct location (Where your Data folder is inside your Melissa Folder).
Set your postal code (Optional).
Set the postal codes Plus 4 (Optional).
Click “Lookup”.
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.

Open Command Prompt as Administrator.
cd to where the installer is located on your machine.
Type the installer name along with the command /NoDetectSSIS
Once the installer appears, accept the agreement and click Next.
Select which version of SQL Server Integration Services is installed.
Select the SSIS root directory. Here you can modify where to install SSIS.
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.

Locate the Package.dtsx by going to View > Solution Explorer.
Go to the Data Flow tab and drag a NEW component from the SSIS Toolbox in order to get the NEW PublicKeyToken.
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:
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)
Open the old project to be updated in Visual Studio.
Right click Package.dtsx on the right side under the Solution Explorer and click View Code.
Use Ctrl+F to search for PublicKeyToken in the project to be updated.
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.

To set the Target Server Version, in Visual Studio’s Solution Explorer, right click on the SSIS project Node and click Properties
Select Configuration Properties. You will see the Target Server Version; select the desired version of SQL Server.
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.

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

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)

Components Only ~246 MB

Latest Web Installer – Personator Component Build 5055

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

Components Only ~211MB

Latest Web Installer - SmartMover Component 5012

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

Latest Web Installer - Global Verify Component Build 5018

Installer with Components and data files included ~11GB

Latest ISO On Premise Installer

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.

Patched 7.4 Q4 DQC Components for SSIS #

Components Only ~211MB

Latest Web Installer

Installer with Components and data files included ~11GB

Latest ISO On Premise Installer

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.

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.

FAQs

Contents

FAQs#