ETL fails to run

Products

• CA Identity Suite
• CA Identity Governance

Issue/Introduction

ETL failed with the following message

 ERROR [com.eurekify.businessflow.bb.i2.etl.RunEtlPhase] (taskExecutorServiceContainer-1) Failed to execute STAGING phase for ETL process etl.rcm.alerts(alertWithEntitiesStg): com.eurekify.i2.core.etl.process.step.exception.EtlProcessStepExecutionException: Failed to execute SQL Step.
 at com.eurekify.i2.core.etl.process.step.ExecuteSQLStep.execute(ExecuteSQLStep.java:40) [classes:]
 at com.eurekify.businessflow.bb.i2.etl.RunEtlPhase.executeAndFinish(RunEtlPhase.java:67) [classes:]
 at com.eurekify.businessflow.bb.i2.etl.AbstractEtlBuildingBlock.executeAndFinish(AbstractEtlBuildingBlock.java:22) [classes:]
 at com.eurekify.businessflow.bb.AutoResultBuildingBlock.execute(AutoResultBuildingBlock.java:15) [classes:]
 at com.eurekify.businessflow.bb.BuildingBlock.run(BuildingBlock.java:78) [eurekify-api.jar:]
 at com.eurekify.services.BuildingBlockServiceImpl.execute(BuildingBlockServiceImpl.java:128) [classes:]
 at com.eurekify.tms.services.TaskExecutorServiceImpl.executeBuildingBlock(TaskExecutorServiceImpl.java:71) [classes:]
 at com.eurekify.tms.services.TaskExecutorServiceDefaultDecider.executeBuildingBlock(TaskExecutorServiceDefaultDecider.java:59) [classes:]
 at com.eurekify.tms.services.TaskExecutorServiceDefaultDecider.execute(TaskExecutorServiceDefaultDecider.java:40) [classes:]
 at sun.reflect.GeneratedMethodAccessor494.invoke(Unknown Source) [:1.8.0_212]
 at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) [rt.jar:1.8.0_212]
 at java.lang.reflect.Method.invoke(Method.java:498) [rt.jar:1.8.0_212]
 at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:273) [spring-core-3.0.6.RELEASE.jar:3.0.6.RELEASE]
 at org.springframework.jms.listener.adapter.MessageListenerAdapter.invokeListenerMethod(MessageListenerAdapter.java:463) [spring-jms-3.0.6.RELEASE.jar:3.0.6.RELEASE]
 at org.springframework.jms.listener.adapter.MessageListenerAdapter.onMessage(MessageListenerAdapter.java:355) [spring-jms-3.0.6.RELEASE.jar:3.0.6.RELEASE]
 at com.eurekify.jms.RedeliveryAwareMessageListenerAdapter.onMessage(RedeliveryAwareMessageListenerAdapter.java:24) [classes:]
 at org.springframework.jms.listener.AbstractMessageListenerContainer.doInvokeListener(AbstractMessageListenerContainer.java:535) [spring-jms-3.0.6.RELEASE.jar:3.0.6.RELEASE]
 at org.springframework.jms.listener.AbstractMessageListenerContainer.invokeListener(AbstractMessageListenerContainer.java:495) [spring-jms-3.0.6.RELEASE.jar:3.0.6.RELEASE]
 at org.springframework.jms.listener.AbstractMessageListenerContainer.doExecuteListener(AbstractMessageListenerContainer.java:467) [spring-jms-3.0.6.RELEASE.jar:3.0.6.RELEASE]
 at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.doReceiveAndExecute(AbstractPollingMessageListenerContainer.java:325) [spring-jms-3.0.6.RELEASE.jar:3.0.6.RELEASE]
 at org.springframework.jms.listener.AbstractPollingMessageListenerContainer.receiveAndExecute(AbstractPollingMessageListenerContainer.java:243) [spring-jms-3.0.6.RELEASE.jar:3.0.6.RELEASE]
 at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.invokeListener(DefaultMessageListenerContainer.java:1058) [spring-jms-3.0.6.RELEASE.jar:3.0.6.RELEASE]
 at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.executeOngoingLoop(DefaultMessageListenerContainer.java:1050) [spring-jms-3.0.6.RELEASE.jar:3.0.6.RELEASE]
 at org.springframework.jms.listener.DefaultMessageListenerContainer$AsyncMessageListenerInvoker.run(DefaultMessageListenerContainer.java:947) [spring-jms-3.0.6.RELEASE.jar:3.0.6.RELEASE]
 at java.lang.Thread.run(Thread.java:748) [rt.jar:1.8.0_212]
Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: String or binary data would be truncated.
 at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:259)

Cause

This is due to data size over the column limit.

Resolution

Modify the database schema with the following queries:

ALTER TABLE I2_ALERT_WITH_ENTITIES_STG ALTER COLUMN ENTITY1_RES3 NVARCHAR (768);
ALTER TABLE I2_ALERT_WITH_ENTITIES_STG ALTER COLUMN ENTITY2_RES3 NVARCHAR (768);
ALTER TABLE I2_ALERT_WITH_ENTITIES_STG ALTER COLUMN ENTITY3_RES3 NVARCHAR (768);
ALTER TABLE I2_ALERT_WITH_ENTITIES_STG ALTER COLUMN ENTITY4_RES3 NVARCHAR (768);