Month: April 2021

I recently encountered a SSIS package that was failing due to an ‘arithmetic overflow error converting IDENTITY to datatype int’;

Conversion/overflow errors aren’t that unusual – normally a data flow broken by some unexpected data (“no, there’s no chance that field would ever have a character in it”), or perhaps a column hitting max size (“INT will be enough for years, like, 5 years. I’ll have left the company by then”)

But that wasn’t the case here – the package and user tables involved were checked by the dev team and there was no possible overflow. I’d checked system databases for maxed-out identity columns and found nothing. Heads were scratched.

In hindsight, there were two major clues pointing to the cause;

1. There were many (5 figures) duplicate errors in the log. So many in fact that the SSMS GUI was throwing a memory exception trying to view them and I had to pull from them from catalog.operation_messages
2. These errors were occuring on validation – it hadn’t even started running!

Clue 1# suggested a systemic issue not related to any particular function of the package. Clue #2 suggested the user tables were not even getting touched so could not be throwing overflow errors. So, we’re looking at system tables.

But I’d checked those? Well, I’d checked system databases…

Let’s loop over all the user databases on the server and run the below script to check for identity columns of type int…

SELECT OBJECT_NAME(c.[object_id]) AS 'TableName',
    c.[name] AS 'ColumnName',
    c.system_type_id,
    t.[name] AS 'Type'
FROM sys.all_columns c
INNER JOIN sys.types t
    ON c.system_type_id = t.system_type_id
WHERE c.is_identity = 1
    AND t.[name] = 'int'

Oh, what’s this? That doesn’t look like a user table…I wonder…

Bingo.

This is the sysssislog table, usually found in msdb. Whoever installed SSIS on this box must have set it to the user database in question. And now it had exhausted its supply of IDENTITY values on the id column, triggering the execution/validation of any package logging to it to instantly fail and flood the operations log with overflow errors.

We didn’t need any of the logs, so truncating the table (which also resets the IDENTITY value) resolved the problem.