How to rollback a transaction in TSQL when string data is truncated?
If your on SQL 2005 you can try:
BEGIN TRANSACTIONBEGIN TRY --Run your Statements COMMIT TRANSACTIONEND TRYBEGIN CATCH ROLLBACK TRANSACTION DECLARE @Msg NVARCHAR(MAX) SELECT @Msg=ERROR_MESSAGE() RAISERROR('Error Occured: %s', 20, 101,@msg) WITH LOGEND CATCH
I would also point out that if you are receiving this error often, you need to revise the size of the column you are entering data into or adjust your cleaning process to prep the data before putting it into the prod table. In SSIS, You could also have the data that deosn't meet the standard size go to a bad data table and process the rest.