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>