Error converting data type during Run Staging Procedures job step

Products

• Information Centric Analytics

Issue/Introduction

An error similar to the following is logged in the SQL Server Agent log and in the Log_DataTransformation table for the RiskFabric Processing job step Run Staging Procedures (step 4):

Error Msg: Error converting data type nvarchar to bigint.

This failure may occur either immediately after integrating a new data source or some time after a data source has been integrated and processed successfully.

Environment

Release : 6.5.4

Component : Integration Wizard

Cause

A source column mapped to a destination column of data type bigint contains a non-numeric character, which cannot be cast or converted to a numeric.

For previously functioning integrations, this typically occurs when upstream data are corrupted or change unexpectedly in a manner the integration's data source query is not designed to handle. E.g., a source row containing the value '314159' in an ID column is concatenated with a versioning column containing the value '2' to produce a unique ID ('3141592'). At some point, however, the values recorded in the data source's versioning column are prepended with the character 'v' (i.e., 'v2') resulting in the string '314159v2', which cannot be cast as a numeric data type.

Resolution

For a mapping between a numeric destination column and a source column that intermittently or consistently contains non-numeric characters, a formula needs to be applied to remove or replace non-numeric characters. If a formula is already in use, it will need to be modified to parse strings in the form that produced this condition. Ideally, this should be corrected in the upstream data source, but it can addressed by either re-writing the integration's data source query or by applying a formula in the Import Rule Mapping for the data source to search for and remove or replace non-numeric characters.