=================================================================================================== matchIT SQL Copyright (c) 2007-2021, 360Science ltd. All rights reserved. =================================================================================================== Version 2.4.1.8 Released 7th July 2021 The matchIT API's release notes can be found in: C:\Program Files\Common Files\matchIT API\release notes.txt --------------------------------------------------------------------------------------------------- 2.4.1 - 20th November 2020 --------------------------------------------------------------------------------------------------- - [2.4.1.3] Minor Bug Fixes. - [2.4.1.2] GenerateCorrectedAddresses handles data containing characters used as delimiters better - [2.4.1.2] GenerateCorrectedAddresses puts Verify errors into an Errors table - Address verification preventCompanyUpdates setting properly reflected in saved settings - Suppression cleaned table better handles alphabetic characters in unique ref fields - Address verification consistency improvements - Handle insufficient permissions for address verification user on temp folder better - [2.4.1.5] UK Address Verification improvements to rural addresses and fuzzy number matching - [2.4.1.6] UK Address Verification: - non-PAF improvments - retain unmatched one-word flat names - e.g. "GFF" - retain unmatched flat letters when matching e.g. "19G" to "19" - allow minimal input - e.g. premise and postcode - [2.4.1.8] UK Address Verification: - fixed issue with inconsistent results --------------------------------------------------------------------------------------------------- 2.4.0 - 26th March 2020 --------------------------------------------------------------------------------------------------- - UK addressing use matchIT Hub. - Update Loqate addressing to version 2020Q2.0. --------------------------------------------------------------------------------------------------- 2.3.6 - 18th April 2019 --------------------------------------------------------------------------------------------------- - Added new Integration Services (SSIS) component and stored procedure for NCOA 48 month processing. - The GenerateNCOAAddresses SSIS task and procedure is no longer supported. - Update Loqate addressing to version 2019Q2.0. - Performance improvements in International Address Verification - Add support for SQL Server 2019. - Update Loqate addressing to version 2020Q2.0. --------------------------------------------------------------------------------------------------- 2.3.5 - 18th June 2018 --------------------------------------------------------------------------------------------------- - Suppression parallel processing. Increased performance when running multiple suppressions jobs at the same time. - Suppression data keys can now we loaded in with the data. - FIPS 140-2 compliance. - Remove support for SQL Server 2005. - ZAP option added for SSIS UK Mail Sortation task. --------------------------------------------------------------------------------------------------- 2.3.4 - 24th April 2018 --------------------------------------------------------------------------------------------------- - Added Integration Services (SSIS) components for SQL Server 2017. - Added new Integration Services (SSIS) component and procedure for NCOA 48 month processing. - The GenerateNCOAAddresses SSIS task and procedure is no longer supported. - Update Loqate addressing to version 2018Q2.0. - UK addressing enhancements. - Update Loqate addressing to version 2018Q4.0. --------------------------------------------------------------------------------------------------- 2.3.3 - 21st December 2017 --------------------------------------------------------------------------------------------------- - Improvements to the Use Header functionality when mapping columns. - Improved the performance of msp_OutputOverlapDedupedTable. - A more descriptive exception is thrown when a config references a table that doesn't exist. - Changes to the default match keys for company only matching. - When an SSIS template isn't found, a more descriptive exception is thrown. - When creating a new GroupExactMatches/Overlap task, the masterRecordIdentification setting is now inherited from the template. - Sampling now works in the GenerateKeys SSIS task. Suppression improvements and changes (UK only): - Added NCOA Suppress and Re-Mover to suppressions. - Significant performance improvements when finding exact matches during suppressions. - The suppression task errors if the monitor runs before key generation has started. - Improved detection of running suppression tasks to prevent concurrent instances. - The GAS suppression dataset no longer has a Prefix field. - reConnect at temporary level now works. - New addresses from NCOA Update and reConnect now appear in the __cleaned__ table. - Changed the name that's used to identify reConnect hits that don't get a new address. --------------------------------------------------------------------------------------------------- 2.3.2 - 4th October 2017 --------------------------------------------------------------------------------------------------- - Added option to output all records when outputting matching groups. - Added master record identification for exact matching. - Improved overlap algorithm, this also affects suppression processing. - Processing when use SQL Server Data Tools now runs on the server. - Added use header functionality to Generate Corrected Addresses. - Group Matches: Master Record Identification is now enabled or disabled by the template when the package was created with a version of matchIT SQL prior to 2.2. - Improved UK addressing handling of foreign addresses. --------------------------------------------------------------------------------------------------- 2.3.1 - 24th May 2017 --------------------------------------------------------------------------------------------------- - (matchIT API 1.4.13) - Upgraded fuzzy compare algorithm to Damerau-Levenshtein - with new options to allow for looser fuzzy matching. - New Preferred Job File Directory option added to the SSIS Mail Sortation task. - SSIS FindSuppressions now supports SQL Server Data tools deployment. - Grouping tasks no longer error when there are zero records in the input table. - Various other fixes. - Disable19 option added for US Addressing. - ACSKeyline is now returned when applicable with verified US addresses. --------------------------------------------------------------------------------------------------- 2.3 - 16th March 2017 --------------------------------------------------------------------------------------------------- - (matchIT API 1.4.11) - Interactive reports can now be created for use with Microsoft Power BI. - The SuppressionConfiguration task can now execute the suppression SSIS package, for simplified suppression usage and integration (UK only). - Various other improvements and fixes to the suppression SSIS package (UK only). - International addressing now includes SERP for Canadian address validation. - The MatchingGroups output table now includes additional columns: NumDupes, BaseScore (requires a compatibility of 2.3.0+). - Performance improvements to GroupMatches when processing large groups of matching pairs. - The elements in mfn_SingleRecordMatch2 and mfn_SingleRecordMatchEx2 are no longer case sensitive. - The GenerateKeys task no longer throws an exception when joined tables contain columns with the same name. - Various other fixes and improvements to the SSIS tasks. - New option (Detect Unrecognized Qualifications) to control detection of unrecognized qualifications when parsing a fullname. - The SSIS components cannot be installed when only SQL Server Express or Web Edition is installed. - New CreatePunTrim.sql script that creates a new general purpose function (fn_PunTrim) that can be used to remove punctuation characters from values. --------------------------------------------------------------------------------------------------- 2.2.1 - 6th December 2016 --------------------------------------------------------------------------------------------------- - (matchIT API 1.4.10) - The web-based UI can now only be used with Microsoft IIS. - The web-based UI can no longer be used on Windows XP or Windows Server 2003. - Various security enhancements to the web-based UI: served via HTTPS by default; a default password has not been configured; weak passwords are not allowed; lockout on multiple failed login attempts. - UK addressing: Added support for consumer name verification and correction. - Added Integration Services (SSIS) components for SQL Server 2016. - Added a command-line utility for unattended importing of suppression data sets (UK only). - Matches are no longer missed when using functions (left, right, substring) in the match keys with a case-insensitive SQL Server collation. --------------------------------------------------------------------------------------------------- 2.2 - 22nd February 2016 --------------------------------------------------------------------------------------------------- - (matchIT API 1.4.8) - Added integrated suppression functionality for common data sets (UK only). - Added integrated mail sortation functionality (UK only). - An input column can now be mapped to the new MiddleNames data type when name data is spread across multiple columns (prefix, first names, middle names, last name). - The summary reports for FindMatches/Overlap and GroupMatches/Overlap now correctly list the total number of records in each data source. - The summary reports for GroupMatches/Overlap now indicate the total number of duplicate records rather than the total number of matching pairs. - New setting, allowBlankKeys, that can be disabled to prevent comparing records that have any blank values within the current match key; this can help to reduce the number of false matches. - New stored procedure, msp_FormatAddressesForMailing, that can be used to ensure that UK input addresses are formatted for mailing labels. - When selectAllColumns is disabled (the default), schema and table names are no longer dropped from mapped columns when selecting from tables. - BulkGenerateKeys now uses selectAllColumns. - UK addressing: Added DPS only mode. DPS count no longer short by one. Option to append default DPS codes on non-passing records. - mfn_SingleGenerateKeys: Increase width of xml argument. - Any exclusion words found in a record can be output to a new column, mkExclusions, when keys are generated. - The GenerateNCOAAddresses SSIS task now correctly uses any filters on a manual data source. --------------------------------------------------------------------------------------------------- 2.1 - 2nd September 2015 --------------------------------------------------------------------------------------------------- - (matchIT API 1.4.8) - GenerateCorrectedAddresses: New UK Addressing engine. IMPORTANT NOTE: If you are an existing user of UK Addressing and upgrading to matchIT SQL 2.1, you will need to contact helpdesk@360Science.com to obtain licence files for the new addressing engine. Also, during the installation, be sure to check "Download data files for UK addressing" since the existing data files are not compatible with the new engine. - GenerateCorrectedAddresses: It is now possible to specify a minimum verification level for individual countries (international addressing only). - Worker processes can now write to a debug log file to help diagnose issues. - Various minor improvements and enhancements. - Fixes specific to 32bit version of UK addressing. - Socket connections from stored procedures and SSIS tasks are now bound to random ports within a predetermined range, instead of allowing the operating system to randomly bind them. - The AddrEngine output field is just dependent on APIs licensed rather than APIs enabled. - msp_OutputOverlapLargeClusters now correctly uses the mappings from the overlap data source. --------------------------------------------------------------------------------------------------- 2.0.6 - 26th November 2014 --------------------------------------------------------------------------------------------------- - (matchIT API 1.4.3) - GenerateCorrectedAddresses: Added support for using batch cloud api for international addressing. --------------------------------------------------------------------------------------------------- 2.0.5 - 21st October 2014 --------------------------------------------------------------------------------------------------- - (matchIT API 1.4.3) - SSIS tasks are now always reliably copied within a package or to another package. - Added support for missing data types: smallmoney, date, datetime2, smalldatetime, datetimeoffset, and time. - All name and organization matching matrices referenced by an XML configuration file are now correctly applied when any such file contains tab characters. - Allow use of email/telephone/dob/customfields in TVF. - The 32-bit service is now correctly installed on 32-bit machines. - GenerateNCOAAddresses now transfers encrypted data to and from the NCOA service. - US addresses are no longer processed by international addressing when US addressing is disabled unless international addressing is enabled for US addressing. Same for UK addresses. - New NCOA version. - GenerateNCOAAddresses now replaces embedded nonprintable characters (including tabs, carriage returns, and line feeds) with spaces when outputting data for subsequent NCOA processing. - FindMatches: If pre-clustering is disabled, the first record's unique ref is now correctly read from the source table. --------------------------------------------------------------------------------------------------- 2.0.4 - 18th July 2014 --------------------------------------------------------------------------------------------------- - (matchIT API 1.4.3) - Added Integration Services (SSIS) components for SQL Server 2014. - All Unicode input data is now transliterated into Latin1 characters, to allow the matchIT API to both generate match keys for records containing this data and compare pairs of such records. - When summary reports are being output and overwriteReports is enabled, any existing report table is now truncated before writing the data for a new report. - The addressing module no longer crashes when logging a processing failure and the unique reference contains uncommon non-alphanumeric characters. - GenerateNCOAAddresses has been updated to allow uploading large data files to the NCOA service. - GenerateCorrectedAddresses: Individual licensed addressing APIs can be temporarily disabled. --------------------------------------------------------------------------------------------------- 2.0.3 - 19th June 2014 --------------------------------------------------------------------------------------------------- - (matchIT API 1.4) - The matchIT SQL managed service has been replaced with a native/unmanaged service to prevent occasional service startup issues. - BulkGenerateKeys: It is now possible to specify a width used for each column in the output table that can be created alongside the keys table. If not specified, then nvarchar(max) is still used. - It's now possible to map a 'money' column type, so that an exception is no longer thrown when outputting the deduped and duplicate data tables. - It's now possible to explicitly enable or disable the summary reports produced when exact matching and grouping. - SSIS: Data sources can no longer be changed from within any of the Find* or Group* tasks when legacy data source IDs are still being used. --------------------------------------------------------------------------------------------------- 2.0.2 - 2nd June 2014 --------------------------------------------------------------------------------------------------- - (matchIT API 1.4) - GenerateCorrectedAddresses: New 64-bit addressing module to allow simultaneous use of US and International addressing. - GenerateCorrectedAddresses: If licensed for multiple addressing APIs, then all addresses are now passed to the correct addressing API. - GenerateCorrectedAddresses: With US addressing the output DPV codes now contains 5 characters; position 4 contains the vacancy indicator and position 5 contains the DFS2 nostats indicator. - GenerateCorrectedAddresses: New international addressing setting, intelligentScoring, that when disabled causes a simplified scoring algorithm to be used to increase the verification rate. - Added two new stored procedures (msp_OutputLargeClusters and msp_OutputOverlapLargeClusters) that can be used after msp_FindMatches or msp_FindOverlap respectively to determine which records have not been compared because they're in a large cluster. - SSIS: Added a new advanced setting to the FindMatches and FindOverlap tasks for listing the records in any logged large clusters. - Improved control over exact matching keys: ability to specify optional keys that do not have to be populated to be considered a match (requires a compatibility of 2.0.2+). - The FindOverlap and FindExactOverlap SSIS tasks are now editable if the two data sources cannot be automatically assigned from preceding tasks. - GroupMatches now reliably assigns the matchref when merging exact and fuzzy matches if FindExactMatches used multiple exact match keys. - An exception is no longer thrown by BulkGenerateKeys when a view is referenced by a data source. --------------------------------------------------------------------------------------------------- 2.0.1 - 7th March 2014 --------------------------------------------------------------------------------------------------- - (matchIT API 1.4) - GenerateCorrectedAddresses: Added an option to allow reverse lookup from ZIP/ZIP+4 and company, for when the street name is missing from the input. --------------------------------------------------------------------------------------------------- 2.0 - 28th January 2014 --------------------------------------------------------------------------------------------------- - (matchIT API 1.4) - The stored procedures and their assembly can now be automatically created when matchIT SQL is first installed or reinstalled. Existing users can benefit by manually running bin\matchITSQLConfigurator.exe and modifying .sql scripts to run the stored procedures from the matchIT_SQL database. - The stored procedures and their assembly are now only created by CreateStoredProcedures.sql. - International address verification can now be run using an internet-based service that doesn't require the reference data to be installed locally. - [DEPRECATION] The msp_CreateKeysTable stored procedure has been deprecated, and will be removed in a future release of matchIT SQL; it is still available for configurations that have a compatibility less than 2.0.0. - [DEPRECATION] The msp_GenerateKeys and msp_AddKeyFieldsToTable stored procedures have been deprecated, and will be removed in a future release of matchIT SQL. - Improvements to the GroupOverlap algorithm to ensure that matchrefs are always assigned correctly regardless of whether the tables are mapped to the main and overlap datasources or to the reverse (requires a compatibility of 2.0.0+). - SSIS template configuration files and temporary files created at runtime can now be stored in any folder on any disk by modifying the paths specified in templates\settings.xml. - Match keys can now be specified using standard/concise form; for example, "mkPostOut + mkName1". - The matchIT SQL Service can now write to a debug log file to help diagnose issues. - GroupMatches contains performance improvements when merging exact matches. - selectAllColumns is now disabled by default to improve key generation and matching performance when a table has a large number of columns. - Should an error occur when GenerateCorrectedAddresses and GenerateNCOAAddresses are run, the error message and stack trace are now propagated to the Error Report. - Exact matching now correctly accounts for conditional columns (filtering). - Added a new setting - generalSettings/useNOLOCK - that applies the NOLOCK table hint when enabled to help improve key generation, matching, and addressing performance. - Added a new setting - generalSettings/outputCompareResults - that can be used to determine which matching matrix entries were used when scoring names and/or organizations. Note that this should be used for debugging purposes only, and may be withdrawn or modified in a future release. - GenerateNCOAAddresses: The output table is (re)created before any data is uploaded to the web service so that any permissions problems are earlier detected. - Exact match keys with multiple elements will no longer prevent summary reporting to fail because the key has overflowed the Value column. - When matching at individual level it is now possible to match organizations by increasing the weights, without having to modify or copy the organization matching matrix too. - When matching at business level it is now possible to match names by increasing the weights, without having to modify or copy the name matching matrix too. - When matching at custom level it is now possible to match names and organizations by increasing their weights, without having to modify or copy the name and organization matching matrixes too. - Miscellaneous address verification improvements. - Miscellaneous browser-based UI improvements. - Miscellaneous improvements to some SSIS tasks. --------------------------------------------------------------------------------------------------- 1.4.9 - 17th May 2013 --------------------------------------------------------------------------------------------------- - (matchIT API 1.3.9) - GenerateCorrectedAddresses now provides verification and correction of international addresses for licensed users. - GenerateCorrectedAddresses can now output an additional column (PafDescExtra) that provides additional detail for verified addresses (requires a compatibility of 1.4.9+). - GenerateKeys can now extract middle names from parsed first names (this is disabled by default, but can be enabled via matchITAPISettings/generate/name/extract/middleNames). --------------------------------------------------------------------------------------------------- 1.4.8 - 27th February 2013 --------------------------------------------------------------------------------------------------- - (matchIT API 1.3.8) - [COMPATIBILITY] A new compatibility setting has been introduced into config files. New configs and templates are assigned a compatibility level, which matchIT SQL will use to help prevent compatibility problems when upgrading to a newer version. - [DEPRECATION] Removed the 'purgeFrom' property in datasource definitions. Exact matches are now only flagged within the overlap table in order to be consistent with FindOverlap. 'purgeFrom' will be fully removed in a future release, so configs and processes must be amended. - [DEPRECATION] The msp_GroupOverlap stored procedure has been deprecated and will be removed in a future release of matchIT SQL. Please use msp_GroupOverlap2 instead, which takes an additional argument so as to be fully consistent with all other overlap-related stored procedures. - [DEPRECATION] The msp_CreateCustomMatchesTable and msp_CreateCustomGroupedMatchesTable stored procedures have been deprecated, and will be removed in a future release of matchIT SQL. - Numerous major and minor usability and functional improvements to the SSIS components. - Improvements to allow multiple SSIS tasks to run concurrently without error. - The FindMatches/Overlap SSIS task's GUID is now appended to the name of the large_clusters table (only applicable to new packages or to existing packages that are using the new templates). - The GroupExactMatches/Overlap SSIS task's GUID is now appended to the name of the __exact__ table (only applicable to new packages or to existing packages that are using the new templates). - SSIS packages that contain any matchIT SQL SSIS components no longer contain matchIT SQL-specific package variables. - GroupExactOverlap: MatchRefs have been swapped so as to be consistent with fuzzy overlap grouping (MatchRef now refers to unique refs from the Record2 column, requires a compatibility of 1.4.8+). - Records containing blank search keys are now ignored when fuzzy matching (requires a compatibility of 1.4.8+). - The matches, matches_grouped, exact_matches, and exact_matches_grouped tables now use the unique ref's data type (requires a compatibility of 1.4.8+). - An exception is now thrown if invalid XML settings were passed to the matchIT API (requires a compatibility of 1.4.8+). - An exception is no longer thrown if a match key is incorrectly cased in an XML configuration. - An exception is now thrown if msp_CreateKeysTable is unable to create the keys table, instead of failing silently. - The keys table will no longer be created with a NULLable unique ref column. - Unless specified, joined tables (such as the keys table) are now joined using INNER instead of LEFT (requires a compatibility of 1.4.8+). - Improvements to preserve multiple failures logged when correcting addresses. - Columns in the corrected addresses table are now created using NVARCHAR instead of VARCHAR. - OutputDuplicates and OutputDedupedTable now correctly list all duplicates and non-duplicates respectively. - The exact_matches table now includes a column that indicates the first key that each exact match was found by (requires a compatibility of 1.4.8+). - If certain summary report types have been disabled, then neither their tables nor output files are now created. - When FindMatches/Overlap are running and pre-clustering is enabled, progress log messages now include a percentage for the current key. - Extra statistics (matches found, comparisons made, etc.) are now written to the log table. - Large clusters are now logged as warnings in the log table. - It is now possible to change the name of the Progress and Faults tables that are created during address correction. - mkFlatNo is now included in the default exact match key. - Additional data files for tighter business matching are now installed (see, for example, C:\Program Files\matchIT SQL\config\dataFiles\us\tight). These should be used with the matchITAPISettings/generate/organization/useEquivalentName setting enabled. - A warning is now displayed if Visual Studio is running during matchIT SQL installation, to help prevent SSIS upgrade problems. - The Microsoft .NET Framework 3.5 is now required for the browser-based UI. - The Microsoft ASP.NET 2.0 Ajax Extensions are no longer installed. - Added a new table-valued function (mfn_SingleGenerateKeys) for generating the match keys for a single record (NB: only available to licensed users). - GenerateNCOAAddresses now uploads each output file in turn, and imports the result set after each upload, instead of uploading all files and then importing the results. - Worker processes now create sockets bound to ports within a default range of 52200-52299. - An exception is now thrown if a stored procedure or SSIS task cannot establish communication with a worker process, instead of waiting indefinitely. --------------------------------------------------------------------------------------------------- 1.4.7 - 15th August 2012 --------------------------------------------------------------------------------------------------- - (matchIT API 1.3.7) - Numerous usability and functional improvements to the web-based UI. - Various usability improvements to these SSIS tasks: GenerateKeys, GenerateCorrectAddresses, and GenerateNCOAAddresses. - BulkGenerateKeys no longer throws an exception if the keys table already exists; instead, key generation will now be skipped if the keys table contains the same number of records as the source data table. - Significant improvements have been made to how the Addressing functionality handles bad data, and how it recovers from fatal errors. - Multiple search keys can now be specified for the SingleRecordMatch stored procedure and table-value functions (for duplicate prevention purposes). - SingleRecordMatch will no longer query the entire table if the passed arguments do not produce any non-blank search key values. - The GenerateCorrectedAddresses SSIS task no longer allows the name of the output table to match the name of any input table. - The output table no longer needs to be specified when running GenerateCorrectedAddresses. If left blank, then its name is a concatenation of the first input table's name plus a suffix. - Connection strings in SSIS packages can now be encrypted. - Improvements to the web-based UI when used from Internet Explorer. - All non-fatal warning messages are now written to the log table. - Improvements have been made to the performance of the OutputDedupedTable stored procedure. - Primary keys are now created on all tables produced by the OutputDuplicates, OutputDedupedTable, and GroupMatches stored procedures and their overlap equivalents. - When exact matching using FindExactMatches/Overlap, the stripPuncWhenExactMatching option now allows correct handling of non-Western character sets. - The PUNTRIM function now strips whitespace characters and correctly handles non-Western character sets. - All ORDER BY clauses are now performed with a binary collation to ensure T-SQL consistency with the underlying .NET code. - When exact matches are merged with fuzzy matching results during grouping, for certain matching levels (individual, family, or business) the address is no longer considered. - SSIS tasks can now be copied within containers. - Fixed issue with SSIS components not being copied to correct location in SSIS 2008/2012 when SSIS isn't installed to the default path. - Sampling now works regardless of whether ensureUniqueRefIsClustered is enabled or disabled. - Summary reports can now be independently enabled or disabled for certain stored procedures and SSIS tasks. - The summary report for FindMatches now correctly states the number of matches found at business level. - Alternative organization matching matrices are now installed; these can be specified in an XML configuration file to help reduce the number of false matches in the business matching level. - Fixed memory issues in GenerateCorrectedAddresses. - Improvements to the efficiency of exact matching that can result in faster performance and less tempdb usage. - When fuzzy matching, an index is now created on the temporary clusters table to help improve performance when querying some tables and views. --------------------------------------------------------------------------------------------------- 1.4.6 - 11th April 2012 --------------------------------------------------------------------------------------------------- - (matchIT API 1.3.5) - Added Integration Services (SSIS) components for SQL Server 2012. - Key generation no longer fails when the unique ref is a float column (for example, after importing data using the SQL Server Import and Export Wizard). - The web-based UI no longer throws an exception if a new data source is added and saved without setting certain configuration values. - Fixed bug in UI with custom defined keys tables in XML not getting persisted. - Fixed bug in Addressing with source column widths greater than 200. - Fixed bug in Addressing with blank corrected mappings. - Improved Addressing error logging. - Fixed 'Invalid attempt to read when no data is present' bug in FindExactOverlap. - Fixed transaciton context issue when calling mfn_SingleRecordMatch from trigger. - Added a new configuration setting (generalSettings/selectAllColumns) that can be used to control which columns are selected during FindMatches/Overlap: all (default), or mapped. --------------------------------------------------------------------------------------------------- 1.4.5 - 27th February 2012 --------------------------------------------------------------------------------------------------- - (matchIT API 1.3.5) - When generating keys, an exception is thrown if there is no unique clustered index referencing the UniqueRef column. This can be disabled via generalSettings/ensureUniqueRefIsClustered. - Added fn_PunTrim function, written in T-SQL. Also added functions LTRIM, TRIM, and LOWER, which use native T-SQL functions. - Prevent records from exact matching when their key values only contain whitespace or are blank. - Triggers are no longer created on output tables. - Ensure that the msp_Output... stored procedures log progress info in the log table. - Allow special XML characters to be used in database, schema, table and column names. - Allow tables that contain apostrophes in the table name. - When fuzzy matching, search keys are analyzed to determine if any are inefficient with regards to the number of matches a key finds in relation to its elapsed time. - When saving a configuration file via the web-based UI, paths to matching matrix configuration files are incorrect when matchIT SQL has not been installed to the default folder. - In the Generate Keys task, prevent the same field mapping from being used more than once, unless it is a CustomField mapping. - In the Generate Keys SSIS task, automatically populate the Data Source ID with the name of the first table. - Provide options in the SSIS UI to change the Exclude Exact Matches/Overlap settings and the Merge Exact Matches/Overlap settings. - When fuzzy matching and preClustering is enabled (the default), all missing indexes are now created to help improve deduplication performance. - In the Generate Corrected Addresses SSIS task, prevent the same field mapping from being used more than once. - Fix to ensure that the Matches table and Overlap table names specified in the Output Options of the UI are saved. - Prevent settings in GenerateKeys and GenerateCorrectedAddresses tasks from being lost because of differently cased table names (when switching between databases). - Addition of new FindMatchesAdvanced.sql and FindOverlapAdvanced.sql demo scripts, which include exact matching and overlapping. - When reinstalling, certain changes to the web.config file for the web-based UI are no longer overwritten with default values. - Improvements made to msp_GroupMatches procedure algorithm for increased performance. --------------------------------------------------------------------------------------------------- 1.4.4 - 29th November 2011 --------------------------------------------------------------------------------------------------- - (matchIT API 1.3.4) - Further improvements to the matchIT SQL UI to improve usability. - Added a new stored procedure, msp_GenerateNCOAAddresses, that can process addresses through an NCOA service (NB: only available to licensed US users). - Added two new table-valued functions - SingleAddressLookupUS and SingleAddressLookupExUS - that can be used to generate a corrected single address (NB: only available to licensed US users). - GroupMatches now creates the matches_grouped table after dropping any existing one. - The GroupMatches SSIS task no longer fails if FindExactMatches had previously run on the table and its output table (exact_matches) is using a schema other than dbo. - Enhancement to allow for BIGINT type columns as primary keys. - Both the matchIT SQL UI and the GenerateKeys SSIS task now allow a nationality of New Zealand to be selected without causing an exception when either the stored procedure or the task executes. --------------------------------------------------------------------------------------------------- 1.4.3 - 12th October 2011 --------------------------------------------------------------------------------------------------- - (matchIT API 1.3.3) - The stored procedures now load data directly into tables; Bulk Insert statements are no longer used, and bulkadmin privileges are no longer required. - Various enhancements to the matchIT SQL UI to improve usability. - Various enhancements to the summary reports. - Data files for all regions are now installed to the config subfolder. - It is now possible to specify which folder contains the data files via the web-based UI and the SSIS tasks. In addition, the nationality can now be configured via the SSIS tasks. - The matches table now includes a column that lists the first key that each match was found by. - Added a new setting - generalSettings/mergeExactMatches - that, during grouping, can be used to merge exact matches with fuzzy matches. - When grouping fuzzy matches, the matches_grouped's ID column is now an identity column. This means that the matches_grouped table can no longer be joined to the matches table using the ID column; instead, the Record1 and Record2 columns must be used for joining. - msp_GroupExactOverlap now produces an __exact__ table regardless of whether any exact matches are found, in line with the functionality of msp_GroupExactMatches. - OutputMatchingPairs etc. will no longer cause an 'ambiguous column' exception when columns with the same name exist in multiple joined tables. - OutputOverlapDuplicates will now output only one instance of a record when it matches multiple records from the main table. - When running GenerateCorrectedAddresses, an 'address key' can now be output that will uniquely identify verified UK addresses. - When running GenerateCorrectedAddresses, blank values are now written to the corrected addresses output table instead of NULL values. - Support for SQL Server 'context connections' has been removed from matchIT SQL, to avoid potential ambiguity by ensuring that connection strings in configuration files are always used. - In configuration files, 'nonContextConnectionString' elements have been deprecated and should be replaced with 'connectionString' elements. - Version numbers of the components (stored procedures, service, and worker) are checked at runtime to ensure that installation was successful and that the previous stored procedures assembly is no longer in use. - The stored procedure assembly version is now written to the log table when a stored procedure is executed. - It is now possible to use unmodified configuration files originating from versions of matchIT SQL prior to 1.3.0. - Custom settings in the .config files (in the bin folder) are no longer lost when matchIT SQL is reinstalled. - Fixed issues with hard coded 'ID' column references in output procedures functionality. - Improvements to TIGER Data coding algorithms. --------------------------------------------------------------------------------------------------- 1.4.2 - 16th September 2011 --------------------------------------------------------------------------------------------------- - (matchIT API 1.3.2) - Added two new table-valued functions - SingleRecordMatch and SingleRecordMatchEx - that can be used by licensed users to identify records that match the input data (mainly for duplicate prevention purposes). - Updated Addressing Module to 1.0.5 - Added TIGER data appending functionality for US addressing. - SSIS: Columns are no longer listed twice if they appear in multiple instances of the same named table, but in different database schemas. --------------------------------------------------------------------------------------------------- 1.4.1 - 16th August 2011 --------------------------------------------------------------------------------------------------- - (matchIT API 1.3.1) - Improvements to how exceptions and failed T-SQL statements are reported. - Added sampling functionality. - Added keys table suffix property to configuration and automatic keys table generation functionality so there is now no need to specify a keys table in the configuration. - Added the ability to map columns to the PostOut and PostIn matchIT API fields (for example, if a table containing US data has separate Zip and Zip+4 columns). - It is now possible to configure which key fields are output to the keys table. - It is now possible to output a subpremise key column to the keys table. - Modified the default match key that's used when exact matching to prevent false matches with different first names that are phonetically identical. - Improvements to the grouping algorithm to ensure that no ungrouped matches remain. - FindExactMatches/Overlap now log additional progress information to the log table. - When logging progress information, two stored procedures that start at the same precise instant will no longer have the same identifier. - Concurrent instances of GenerateCorrectedAddresses can now be executed to completion without being prematurely terminated. - The default fuzzy match keys for UK configs now use PostOut+PostIn, instead of Postcode, to provide consistency with the other keys by only referencing the keys table. - Large clusters are now correctly detected and logged when FindMatches/Overlap is executed with the preClustering and excludeExactMatches options enabled. - An exception is no longer thrown when data being bulk inserted into the large clusters table overflows the Search column. - Improvements to GroupExactOverlap to ensure all exact matches are correctly excluded from FindOverlap when excludeExactMatches is enabled. - The SingleRecordMatch stored procedure now returns matching records without causing the worker process to crash. - Usability improvements to some SSIS forms. - Removed obsolete stored procedures (msp_TestReadingFromDatabase, msp_TestWritingToDatabase, and msp_TestDumpMatches). --------------------------------------------------------------------------------------------------- 1.4.0 - 13th June 2011 --------------------------------------------------------------------------------------------------- - (matchIT API 1.3.0) - matchIT SQL now fully benefits from a machine with multiple CPUs and/or cores. In conjunction with the matchIT API, worker processes are able to make use of all available cores to provide maximum scalable performance. - The matchIT SQL Installation and Deployment Guide is now included. - Added the ability to impersonate the user account executing a stored procedure from the SQL Server Management Studio, for connecting to a remote database and for performing Bulk Inserts. - It is no longer possible to map certain read-only matchIT API fields to input table columns (ie. contact, salutation, and second name elements). - The OutputOverlap stored procedures can now be used with tables located on different databases of the same SQL Server instance. - After running GenerateCorrectedAddresses, the 'DPS codes generated' count is now correctly output to the statistics. - An SSIS package can now be executed on the same or different computer regardless of which folder matchIT SQL has been installed to. - Added a new setting for configuration files - matchITAPISettings/datPath - that can be used to specify an alternate folder for the data files used by the matchIT API component. --------------------------------------------------------------------------------------------------- 1.3.4 - 18th March 2011 --------------------------------------------------------------------------------------------------- - (matchIT API 1.2.10) - Added new SSIS components as an alternative to running the matchIT SQL stored procedures. - When generating the keys table, it is now possible to output a new table that contains standardized and cased data from the original input data. - Automatic exclusion of exact matches if FindExactMatches/Overlap is executed prior to FindMatches/Overlap, via a new config property, excludeExactMatches. - Connection strings in XML configuration files can now be encrypted. Please be aware that this provides a low level of security and Windows access controls should still be used to protect configuration files. - Deprecated the 'purgeFrom' property in datasource definitions. Existing configs remain fully supported until this is removed in a future release. If not specified, exact matches are now flagged within the overlap table, to be consistent with FindOverlap. - Fixed bug with cancellation of Addressing stored procedure. - An exception is no longer thrown when grouping matches at a matching level that contains no actual matches. - New configuration setting - flagMatchesAtHigherLevels - that provides the ability to automatically flag matches at higher matching levels. - The default weights for telephone, email, and date of birth have been set to zero. - An exception is no longer thrown by msp_GenerateSingleKeys when inserting data that contains an apostrophe. - An exception is no longer thrown by msp_GroupOverlap when grouping matches at a matching level that doesn't contain any matches. - An exception is no longer thrown by msp_FindMatches and msp_FindOverlap when a composite key contains a column from a view. - Fixed issue with Web UI not persisting selected values for table names. - Fixed issue with column names not being formatted with square parentheses in queries. - When exact matching, composite key values are no longer concatenated so as to prevent incorrect exact matches being reported in certain circumstances. - Fixed null reference exception when saving xml in UI. - Fixed SQL syntax bug produced when table and schema share the same name. - Updated Addressing Module to 1.0.3.2 - Algorithmic / Scoring enhancements. --------------------------------------------------------------------------------------------------- 1.3.3 - 21st December 2010 --------------------------------------------------------------------------------------------------- - (matchIT API 1.2.9) - Added option to automatically delete existing keys table on key generation. - Addressing is now available to licensed US users. - A new option in configuration files will identify the 'best' record in a group of matches (generalSettings/masterRecordIdentification). - Statistical summary reports can now be output in a variety of formats (including PDF, XLS, and RTF) when certain stored procedures complete. - Table and column names are now able to contain spaces. - It is now possible to use the stored procedures with both 32- and 64-bit instances of SQL Server installed on the same machine. - In the web-based configuration UI, key columns can now be assigned to CustomField mappings. - When preClustering is enabled, an exception is no longer thrown if a configuration's data source contains multiple joined tables and key columns are not in adjacent tables. - By default, temporary files are now created in the matchIT SQL\temp folder rather than the user account's temporary files folder. - The stored procedures now disable the SQL Server NOCOUNT option to prevent problems with UPDATE statements. - A warning is now output to the message pane when a stored procedure runs and the product is due to expire. --------------------------------------------------------------------------------------------------- 1.3.2 - 29th September 2010 --------------------------------------------------------------------------------------------------- - (matchIT API 1.2.8) - Added a new stored procedure, msp_GenerateCorrectedAddresses, that can output corrected addresses to a table for subsequent key generation and matching (NB: this is currently only available to licensed UK users). - The web-based UI now includes the ability to create subfolders of the config folder, and browse or edit the configuration files within these subfolders. - All progress logging is now written to a new log table in the database, instead of using temporary text files. - Added the ability to log processing information that can be helpful for debugging purposes. - The msp_GroupMatches and msp_GroupOverlap stored procedures can now remove 'bridged' matches (for instance, a group contains multiple records but one particular record links to two non-matching records). - The FindOverlap stored procedure can now overlap two databases with the same name, but which are located on different SQL Server instances. - The FindExactOverlap stored procedure can now overlap two databases on the same SQL Server instance. - The quality scoring settings have been removed from the UI because quality scores are not currently output by matchIT SQL. - Large clusters are now correctly reported when pre-clustering is enabled and a key finds no clusters to process. - The large_clusters table no longer includes the Record column which logged the unique reference of the first record in the large cluster. --------------------------------------------------------------------------------------------------- 1.3.1 - 8th July 2010 --------------------------------------------------------------------------------------------------- - (matchIT API 1.2.7) - A scheduled task is created upon installation that will periodically check for new releases of matchIT SQL. - Added new stored procedures for outputting matching pairs, matching groups, duplicate records, and the deduped table. - When using the stored procedures for overlapping tables, it is now possible to independently configure the names of the various output tables. - The UPPER function can now be used in search keys to prevent msp_FindOverlap from missing matches if either database is using a binary collation. - When a stored procedure throws an exception, the current xml configuration file is no longer kept open by SQL Server to prevent modifications. - msp_CreateKeysTable now throws a more useful exception message when the table definition specifies an invalid table name or an invalid uniqueRef column. - Amended bulk insert statement in BulkGenerator.cs to explicitly state the code page to use, to avoid corruption of non-ascii characters. - Two concurrently executing stored procedures could, in rare circumstances, attempt to create a temporary clusters table at the exact same time, causing an exception to be thrown by one stored procedure. - The Query class now implements IDisposable to prevent the SqlDataReader remaining connected if an exception is thrown. - When the web-based UI is set to use the IIS webserver, the Aspnet account is now granted read-write access to the 'config' subfolder. - Fixed issue with msp_SingleRecordMatch throwing open data reader exception. --------------------------------------------------------------------------------------------------- 1.3.0 - 25th March 2010 --------------------------------------------------------------------------------------------------- - (matchIT API 1.2.6) - The StoredProcedures assembly now requires EXTERNAL_ACCESS permissions instead of UNSAFE; all use of unmanaged code has been completely removed from the stored procedures to improve the stability and robustness of SQL Server. - The APIInterface has been replaced with two new applications: the matchIT SQL Service, and the matchIT SQL Worker. - Added a new application, the matchIT SQL Monitor, that monitors and displays the status of the service and worker processes. - Removed the MultiThreadedFindMatches, MultiThreadedFindOverlap, and UnloadAPIInterface stored procedures. - Added a new web-based user interface that provides creation and modification of XML configuration files. - Multiple exact match keys can now be specified when using the msp_FindExactMatches and msp_FindExactOverlap stored procedures. - Added two new stored procedures that can be run after exact matching: msp_GroupExactMatches and msp_GroupExactOverlap. - It is now possible to use tables in schemas other than the default 'dbo'. - It is now possible to change the name of the exact_matches table when using the msp_FindExactMatches and msp_FindExactOverlap stored procedures. - Conditional column definitions will now work correctly with NULL values. - Integer columns can now be used in match key definitions. - Variables that hold counts in the code increased from Int32 to long to avoid overflow into negative numbers when dealing with very high volumes - Keys Table now includes Name2Found Column, which can be used to determine the validity of a corresponding prefix and first name (see FirstNameFound in matchIT API Guide). --------------------------------------------------------------------------------------------------- 1.2.0 - 25th January 2010 --------------------------------------------------------------------------------------------------- - (matchIT API 1.2.5) - First release of matchIT SQL. - Renamed these stored procedures: - msp_PurgeExactMatches -> msp_FindExactMatches; - msp_PurgeExactOverlap -> msp_FindExactOverlap and removed all purging functionality from the stored procedures. - The APIInterface DLL is now installed into C:\Program Files\Common Files\matchIT API. - The clusters table is now created using a variable column width of 255 characters to prevent problems with long key values. --------------------------------------------------------------------------------------------------- 1.1.3 - 24th December 2009 --------------------------------------------------------------------------------------------------- - (matchIT API 1.2.4) - Component scores (name, address, postcode, etc.) can now be included in the output matches table via new attributes in the configuration XML. - Improvements to the BatchProcessor clustering mechanisms to help increase performance and efficiency. - Configuration XML files are no longer restricted to a maximum of two data source definitions. - Specifying a non-blank directory for tempFileDirectory in the configuration XML no longer results in an exception being thrown. - When exact matching, key values are now concatenated rather than comma-separated to allow for data being split across columns. - When exact matching, a query will no longer fail when a column contains a null value. - Timeouts will no longer occur when grouping matches. - An exception will no longer be thrown by msp_FindMatches or msp_FindOverlap when a table contains no records. --------------------------------------------------------------------------------------------------- 1.1.2 - 8th October 2009 --------------------------------------------------------------------------------------------------- - (matchIT API 1.2.2) - Major changes to allow configuration via XML files, including definitions of matchIT API settings, database field mappings, table definitions and match keys. - The GroupMatches algorithm has been rewritten to provide significantly improved performance and efficiency. - Temporary files can now be created in a folder other than the temp folder of the SQL Server service's account. - The msp_SingleRecordMatch stored procedure now works correctly with the changes made to the APIInterface data protocol in version 1.0.6. - When using the multithreaded stored procedures, the cumulative statistics are now updated correctly when a thread throws an exception. - In the keys table, all matchIT API key columns of type varchar have been reduced in size from 255 to 50 characters. - When running the FindOverlap stored procedure, each unique large cluster is now reported only once. - The BatchProcessor classes now log progress information to a temporary file. - The Generator and BulkGenerator classes now log progress information to a temporary file instead of using the SqlContext results pipe. - Improvements to the BatchProcessor clustering mechanisms to help increase performance, to reduce unnecessary comparisons, and to better handle null field values. - When grouping matches at a level for which there is no overlap, an exception is no longer thrown. - The ExactPurge classes no longer exclude records that have a blank value in any of the match key columns. - The ExactPurge classes no longer miss matches if the exact key columns contain certain non- alphanumeric characters. - A new stored procedure has been added, msp_UnloadAPIInterface, that will force the APIInterface and matchIT API DLLs to unload from the SQL Server service process. - The APIInterface now statically links to the Visual C++ 2005 runtime to help prevent deployment issues. --------------------------------------------------------------------------------------------------- 1.1.1 - 5th August 2009 --------------------------------------------------------------------------------------------------- - (matchIT API 1.2.0) - When the BulkGenerator and Generator classes throw an exception, the results pipe is now closed so that the exception message isn't lost. - Removed all superfluous instances of SqlServer.Server.SqlContext.IsAvailable. --------------------------------------------------------------------------------------------------- 1.1.0 - 27th May 2009 --------------------------------------------------------------------------------------------------- - (matchIT API 1.2.0) - The matchIT SDK for SQL Server can now be installed independently of the matchIT SDK. - A diagnostics tool, for checking prerequisite development software, is now provided with the matchIT API. - Fix to ensure that Select statements are generated correctly when using the multithreaded stored procedures. - Fix to ensure that the APIInterface doesn't cause a SQL Server crash on machines running Windows Server 2008 when finding duplicates. --------------------------------------------------------------------------------------------------- 1.0.6 - 24th March 2009 --------------------------------------------------------------------------------------------------- - Matches can now be grouped into sets of matching records. - Improvements to memory usage plus forced garbage collection, to help prevent out-of-memory exceptions. - The communication protocol between the stored procedures and the APIInterface now correctly handles all newlines and carriage returns embedded in the data. - matchIT API errors in the APIInterface are now logged. - Fix to ensure that duplicates are no longer logged multiple times. - Added several matchIT API settings that were not being explicitly set in the APIInterface. - All DllImports have been consolidated into one file, they're now no longer spread throughout several source files. - The Generator class can now work with unique ref columns of any data type. - All matching and key fields are now prefixed with 'mk' to help ensure they don't conflict with any columns from other database tables. - All stored procedures are now prefixed with 'msp_' to indicate that they're implemented using managed code. - When the BulkGenerator fails to bulk import the keys file, exceptions are no longer caught and ignored. - A new stored procedure has been implemented that will find all records that match the search criteria, for use with duplicate prevention systems. - A trigger has been implemented to update the keys table whenever a contact record is added, updated, or deleted. --------------------------------------------------------------------------------------------------- 1.0.5 - 11th July 2008 --------------------------------------------------------------------------------------------------- - All matches are now output to a table. - All large clusters are now logged to a table. - Ability to easily use composite match keys with multiple elements. - Added the ExactPurge classes that allows purging exact matches from a table before running the fuzzy batch processor. - Fix to ensure that matches are not missed by the batch processor when an ordered Select query is not case-sensitive. - Fix to ensure that the batch processor correctly fills the cluster array before notifying that the cluster is too large to be processed. - Performance and stability improvements to the batch processing algorithms. --------------------------------------------------------------------------------------------------- 1.0.4 - 27th May 2008 --------------------------------------------------------------------------------------------------- - The SQL Server toolkit is now the matchIT SDK for SQL Server. - Requires the matchIT SDK to be installed beforehand. - The Core class now throws an exception if the matchIT API engine can't be initialized (for example, activation code expiry). - New stored procedure, sp_CreateUniqueRefField, that demonstrates how to add a unique reference column to tables that don't currently have one. --------------------------------------------------------------------------------------------------- 1.0.3 - 26th February 2008 --------------------------------------------------------------------------------------------------- - Added the BulkGenerator class to provide fast bulk import of keys data for large databases, as an alternative to using the Generator class. - In the BatchProcessor class, a record is no longer skipped when it contains no data for either of the current key fields in use. - Added two stored procedures, for testing purposes, that will calculate the rates at which data can be read from and written to the database. --------------------------------------------------------------------------------------------------- 1.0.2 - 23rd January 2008 --------------------------------------------------------------------------------------------------- - Added the MultiThreadedBatchProcessor class to assist implementation of multithreaded deduplication. - Added x64 configurations to the solution and projects. --------------------------------------------------------------------------------------------------- 1.0.1 - 8th October 2007 --------------------------------------------------------------------------------------------------- - Key generation no longer fails when importing a table that doesn't contain consecutive unique reference IDs. - When using the TwoFileDedupe class, querying the second table now correctly uses that table's unique reference column instead of the first table's. --------------------------------------------------------------------------------------------------- 1.0.0 - 3rd August 2007 --------------------------------------------------------------------------------------------------- - First release.