Trouble Shooting for Error 8152 and Error 3621

Problem

We have an ETL job which failed recently, an alert was sent to us:

SQL Server Job System: 'XXX-XXXXX' completed on \\SQL001

JOB RUN:            'XXX-XXXXX' was run on 1/1/2017 at 0:00:00 PM

DURATION:         0 hours, 3 minutes, 37 seconds

STATUS:               Failed

MESSAGES:         The job failed.  The Job was invoked by Schedule XX (Night).  The last step to run was step 1 (EXECUTE [DBXXXXX].[dbo].[SPXXX]).

Solution

Start SSMS, expand SQL Server Agent - Jobs, right click on the job 'XXX-XXXXX', select "View History" from the right-click menu.

This error message can be found in the log:

"START:[Table001] [SQLSTATE 01000] (Message 50000)  String or binary data would be truncated. [SQLSTATE 22001] (Error 8152)  The statement has been terminated. [SQLSTATE 01000] (Error 3621).  The step failed.,02:06:11,16,3621,,,,0"

This error is usually caused by the inserting data (VARCHAR or CHAR data type) is longer than the length of the column in the destination table. In our case, this statement in SP [DBXXXXX].[dbo].[SPXXX] caused the problem:

INSERT INTO [DBXXXXX].[dbo].[Table001]

(
[column1],[column2],[column3]...

)

(

SELECT 

[column1],[column2],[column3]...

from [SQL002].[DBYYYYY].[dbo].[Table001]
where identity_column > @V1
The length of columnA in destination table is 20, is 2 in source table. The solution is to change the length of columnA  in destination to 20. Then the job ran successfully.