48 MONTH NCOA processing in mSQL

NCOA Processing

generateNCOA48Addresses can be used to keep your database up-to-date as your customers move or their addresses are corrected.  Note that this is available to licensed users only, and can only process US data. This is licensed as an additional module to mSQL and requires you have mSQL and the US addressing module first in order to access this module.

generateNCOA48Addresses is available as both a stored procedure and an SSIS task.  The process involves sending data from the input table to an online service, and writing the received processed data into an output table for subsequent processing and use.  

Input Parameters:

  • Configuration file – the file path of the configuration file to be used when this procedure is run.
  • Datasource ID – specifies the data source to be used within the configuration file, which contains the table and column mapping specifications.

 

Stored Procedure

When running as a stored procedure, the data source is used to configure the input table and field mappings that are passed to the NCOA service.  If this will follow GenerateCorrectedAddresses, be sure to use the corrected addresses table and its columns as inputs to the NCOA service.

Previous versions would use the output NCOA tables and columns in following stored procedures (i.e. GenerateKeys), it is reccomended that your NCOA be separate from a matching process to have complete control over the configuration and/or to not use the old default NCOA table names because the stored procedure will then try to determine existence of the NCOA table and automatically make use of it even if it is not explicitly set.

 

 

 

 

SSIS Task

To use the output NCOA tables and columns in following tasks (i.e. GenerateKeys), it is necessary to map the table and its columns in the task. You will need to run the NCOA at least once in order for the table to be made, although it is generally suggested to separate the NCOA process from your matching process as they're often run at different frequencies, and you are charged for NCOA by usage.

 

 

Setting

Description

dataSources

Specifies the database connection, table and column mappings used to define the dataset being processed.

dataServices

specify the type, currently the only type available is ncoa at the time of this writing, but additional options such as addressing or reverse phone append may be added as demand grows

dataServices->sourceSettings->sourceTables

Source table(s) used as input for the NCOA

dataServices->sourceSettings->fieldMappings

Mappings of the databasefields to the matchITField's, a name(first/laste or Fullname) or company are required for NCOA

dataServices->sourceSettings->outputSettings

Specify the outputTable table name, if none is specified then the input table name will be used and a suffix appended

dataServices->sourceSettings->outputSettings->outputColumns

By default, Name and Company fields will be output in the same way they are mapped on input. However, additional information such as the parsed name and address elements along with various other return codes can be optionally added to the output. See below xml example for a listing of all outputColumns

dataServices->sourceSettings->outputSettings->progressTable

not used for NCOA, present as its used by the underlying addressing

dataServices->sourceSettings->outputSettings->faultsTable

not used for NCOA, present as its used by the underlying addressing

dataServices->sourceSettings->advanced->accountSettings->username

Mailer's user name, provided by 360science after completing your first PAF form

dataServices->sourceSettings->advanced->accountSettings->password

Mailer's password, provided by 360science after completing your first PAF form, although required to be changed at least once before using NCOA the first time

dataServices->sourceSettings->advanced->jobSettings->listID

listID, or often referred to as PAF ID, if you're doing your own data you will only have one listID, although if you are a list services provider you will need a listID for each client

dataServices->sourceSettings->advanced->uploadSettings->encryptData

This is a legacy PGP encryption setting, no longer used

dataServices->sourceSettings->advanced->uploadSettings->sshHostKeyFingerprint

by default enabled, uses ssh over SFTP to help keep your data secure, the current ssh hostkey fingerprint is ssh-dss 1024 71:e6:15:4a:d1:b3:93:72:d7:e3:f3:10:c4:f7:36:c9 although could be subject to change in the future

dataServices->sourceSettings->advanced->processingOptions->outputCasing

The output casing setting is used to define the casing of the NCOA output. This defaults to UpperAndLower but can also be set to Upper, Lower, or Auto which will use your input to determine the output casing type.

dataServices->sourceSettings->advanced->processingOptions->blockSize

This is a legacy setting from the 18 month NCOA and will be deprecated eventually, the default block size is 20 million, if you are processing more than 20 million you should increase the block size so that it all will process in one run.

dataServices->sourceSettings->advanced->processingOptions->timeout

In case of non response of lost connection to the server, instead of waiting infinitely the process will end itself with a timeout error of x minutes per 100,000 records, with a minimum of x minute if the file is less than 100,000 records. the default is 0 in the 48 month ncoa meaning no timeout, the main reason a process will hang is because the wrong username/password/listID were provided.

dataServices->sourceSettings->advanced->processingOptions->retries

This is a legacy setting from the 18 month NCOA and will be deprecated eventually

dataServices->sourceSettings->advanced->processingOptions->returnInterval

Sets how often in minutes that the NCOA will ping the server to check for a return file, typically the NCOA will process approximately 10 million an hour on average, although variables such as upload time/download time and the current server queue can all impact performance.

 

 

 

An Example xml:
<dataSource id="1">

<connectionString value="Data Source=.;Initial Catalog=matchIT_SQL_demo;Integrated Security=True" encrypted="False"/>


<tables>
<table name="dbo.Email_Database_March_2018_48_month_NCOA" uniqueRef="Executive_ID"/>
</tables>
<fieldMappings/>

<dataServices type="ncoa">
<databaseSettings databaseType="SqlServer">

<sourceSettings>
<sourceTables>
<table name="dbo.input_table" uniqueRef="ID"/>
</sourceTables>


<fieldMappings>

<fieldMapping databaseField="dbo.input_table.Company_name" matchITField="Company"/>
<fieldMapping databaseField="dbo.input_table.First_Name" matchITField="FirstNames"/>
<fieldMapping databaseField="dbo.input_table.Last_Name" matchITField="LastName"/>
<fieldMapping databaseField="dbo.input_table.STREET1" matchITField="Street"/>
<fieldMapping databaseField="dbo.input_table.STREET2" matchITField="Street2"/>
<fieldMapping databaseField="dbo.input_table.CITY" matchITField="City"/>
<fieldMapping databaseField="dbo.input_table.STATE" matchITField="State"/>
<fieldMapping databaseField="dbo.input_table.ZIP" matchITField="ZipPlus4"/>

</fieldMappings>
</sourceSettings>


<outputSettings>
<outputTable tableName=""/>


<outputColumns>
<column enabled="False" columnName="FullName"/>
<column enabled="False" columnName="FirstNames"/>
<column enabled="False" columnName="MiddleNames"/>
<column enabled="False" columnName="LastName"/>
<column enabled="True" columnName="Company"/>
<column enabled="False" columnName="Street"/>
<column enabled="False" columnName="Street2"/>
<column enabled="True" columnName="Suite"/>
<column enabled="True" columnName="Urbanization"/>
<column enabled="False" columnName="City"/>
<column enabled="False" columnName="State"/>
<column enabled="False" columnName="ZipPlus4"/>
<column enabled="True" columnName="Zip5"/>
<column enabled="True" columnName="Plus4"/>
<column enabled="True" columnName="CoaCode"/>
<column enabled="True" columnName="CoaFound"/>
<column enabled="True" columnName="MoveEffectiveDate"/>
<column enabled="True" columnName="DP"/>
<column enabled="True" columnName="CRRT"/>
<column enabled="True" columnName="LotCode"/>
<column enabled="True" columnName="LotDir"/>
<column enabled="True" columnName="CountyNumber"/>
<column enabled="True" columnName="CountyName"/>
<column enabled="True" columnName="CongDistrict"/>
<column enabled="True" columnName="LACS"/>
<column enabled="True" columnName="Vacant"/>
<column enabled="True" columnName="HouseNumber"/>
<column enabled="True" columnName="PreDirectional"/>
<column enabled="True" columnName="StreetName"/>
<column enabled="True" columnName="StreetSuffix"/>
<column enabled="True" columnName="PostDir"/>
<column enabled="True" columnName="SUD"/>
<column enabled="True" columnName="UnitNum"/>
<column enabled="True" columnName="Leftovers"/>
<column enabled="True" columnName="PMB"/>
<column enabled="True" columnName="DPV"/>
<column enabled="True" columnName="DpvAnswer"/>
<column enabled="True" columnName="DpvCMRA"/>
<column enabled="True" columnName="DpvFalsePositive"/>
<column enabled="True" columnName="DpvFootnotes"/>
<column enabled="True" columnName="Barcode"/>
<column enabled="True" columnName="LastLine"/>
<column enabled="True" columnName="MoveType"/>
<column enabled="True" columnName="Result"/>
<column enabled="True" columnName="Corrections"/>
<column enabled="True" columnName="ErrorMessage"/>
<column enabled="True" columnName="DSF2NoStats"/>
<column enabled="True" columnName="SuiteLink"/>
<column enabled="True" columnName="Source"/>
<column enabled="True" columnName="QueryName"/>
<column enabled="True" columnName="WasEWSCoded"/>
<column enabled="True" columnName="WasDPVCoded"/>
<column enabled="True" columnName="PCOAConfidence"/>
</outputColumns>

<progressTable tableName="dbo.Progress"/>
<faultsTable tableName="dbo.Faults"/>

</outputSettings>

</databaseSettings>


<advanced>
<accountSettings>
<username></username>
<password></password>
</accountSettings>

<jobSettings>
<listID></listID>
<jobRef>ncoa_job</jobRef>
</jobSettings>

<uploadSettings>
<encryptData>False</encryptData>
<sshHostKeyFingerprint enabled="True">
<value>ssh-dss 1024 71:e6:15:4a:d1:b3:93:72:d7:e3:f3:10:c4:f7:36:c9</value>
</sshHostKeyFingerprint>
</uploadSettings>


<processingOptions>
<outputCasing>UpperAndLower</outputCasing>
<blockSize>20000000</blockSize>
<timeout>0</timeout>
<retries>0</retries>
<returnInterval>1</returnInterval>
</processingOptions>

</advanced>


<progressLoggingSettings>
<filePath value=""/>
<interval value="5000"/>
</progressLoggingSettings>

</dataServices>
</dataSource>

 

Was this article helpful?
0 out of 0 found this helpful

have a question or not finding what you're looking for?

Submit a ticket to get some help