Conversion between SSIS Integers and SQL Server Strings

When you carry out database migration, one of main headaches is the conversion of different data types between sources and destinations. If you are using SSIS, be careful about the conversion between strings and int. Sometimes SSIS will fail because the integer type defined is not big enough to hold the original data. It is also known as overflow problem. So here is a summary that indicates which kind of integer data types you should use for SSIS for strings in the source table. Hope it helps!

SSIS	SQL Server	Range	Maximum String Converted
DT_I1	tinyint	0 to 255	varchar(2)
DT_I2	smallint	-2^15 (-32,768) to 2^15-1 (32,767)	varchar(4)
DT_I4	int	-2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)	varchar(9)
DT_I8	bigint	-2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807)	varchar(18)

Note that for an empty string, you can use derived column task in SSIS with expression below to convert an empty string into a NULL integer in SSIS.

LEN(strCol) == 0 ? NULL(DT_I4) : (DT_I4)(RTRIM(strCol))

You can also refer to BOL: int, bigint, smallint, and tinyint (Transact-SQL)