Error: String or binary data would be truncated
Article ID: 184788
Updated On:
Products
Issue/Introduction
While running the RiskFabric Processing job, the job step Run Staging Procedures fails and returns an error similar to the following:
Message
ParentLogGroupID=13968;LogGroupDescription=spIW_RunStandardProceduresParallel @i_sProcedureType = 'Staging', @i_bIntraday = 0;StatusFlag=F;RunMinutes=.00
Errors
----------------
LogGroupID=13979;LogID=623848;LogGroupDescription=Integration Wizard - spIW_RunImportRuleMappingStaging @i_nImportRuleMappingID = 10;ErrorDesc=Error Msg: String or binary data would be truncated.Line Number: 3
The values for ParentLogGroupID, LogGroupID, LogID, and @i_nImportRuleMappingID will be specific to your environment.
Environment
Release: 6.x
Component: Integration Wizard
Cause
One or more staging columns contain values with string lengths that exceed the defined maximum length of the preprocessing table columns to which they are mapped, or string data is being inserted into a preprocessing column that is assigned a numeric data type. This can be from either a source column or a hardcoded value.
Resolution
To resolve this error, the source data needs to be investigated and corrected (if necessary) upstream from Information Centric Analytics (ICA), or a trimming function should be used in the Integration Wizard (IW) mapping within ICA.
To assist with troubleshooting, the following queries can be used to identify and isolate the offending columns:
/* Query 1 */
USE RiskFabric;
GO
SELECT irm.ImportRuleName,
irm.ImportRuleMappingName,
ls.LinkedServerLabel,
irm.DataSourceQueryID,
dsq.DataSourceQueryName,
irm.DataSourceQueryDestinationTableName,
irm.DefaultValue,
irm.FormulaValue,
irm.SourceColumnName,
irm.StagingColumnName,
irm.PreProcessTableName,
irm.ImportRuleMappingPreProcessTableName,
irm.PreProcessColumnName,
irm.ParentEntityName,
irm.ParentEntityDestinationTableName,
irm.MappingType
FROM dbo.vIW_ImportRuleMappingColumnPreProcessColumn AS irm
INNER JOIN dbo.LinkedServers AS ls
ON irmcppc.LinkedServerID = ls.LinkedServerID
INNER JOIN dbo.IW_DataSourceQuery AS dsq
ON irmcppc.DataSourceQueryID = dsq.DataSourceQueryID
WHERE ImportRuleMappingID = <ID> /* Pass the ImportRuleMappingID from the job step failure message here */;
/* Query 2 */
USE RiskFabric;
GO
SELECT TABLE_CATALOG,
TABLE_SCHEMA,
TABLE_NAME,
COLUMN_NAME,
DATA_TYPE,
CHARACTER_MAXIMUM_LENGTH
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = N'<TableName>' /* Pass the name of the DataSourceQueryDestinationTableName from Query 1 */
ORDER BY COLUMN_NAME;
Additional Information
As a best practice, Broadcom recommends using a CAST function on all columns in data source queries to set the data type and length to match the data types and lengths for corresponding columns in our core tables. The following section of the Symantec Information Centric Analytics Integration and Solution Accelerator Guide guide can help with identifying the correct data types and columns:
When you don't CAST the columns at the time the staging table is created, the IW will estimate the needed size based on a sample returned by the data source query. This is because a data source query is entity-agnostic; it's only when you create an import rule mapping that the IW determines which core table will ingest the data. Moreover, multiple import rule mappings can be created from a single data source query.
For more information, please refer to the following section of the Integration and Solution Accelerator Guide:
Using the Integration Wizard to Create a User-defined Data Source
Please note that designing and implementing IW mappings and formulas are your responsibility. If additional assistance is required, please contact your account team to enquire about a professional services engagement.
Feedback
