TRY_CONVERT, TRY_CAST to Find Failed Data Conversion

Problem: Whenever you have similar error messages during data loading by SSIS, it indicates that you have issues between data type conversion. How to find those outliners / failed conversion out?

• [OLE DB Destination [2]] Error: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
  An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "Invalid character value for cast specification".
• [OLE DB Destination [2]] Error: There was an error with OLE DB Destination.Inputs[OLE DB Destination Input].Columns[NPI] on OLE DB Destination.Inputs[OLE DB Destination Input]. The column status returned was: "The value could not be converted because of a potential loss of data".

Solution: Using TRY_CONVERT or TRY_CAST Functions in SQL Server 2012

TRY_CONVERT and TRY_CAST Functions introduced in SQL SERVER 2012 is very handy tools to dig out those failed data conversion or casting. If the conversion/cast is successful then it will return the value of the specified data type; Else it will return a NULL value. For example, for the following conversion, when using CONVERT function, it will give error message like “Error converting data type varchar to bigint”.

SELECT CONVERT(bigint, '8906UP')

while using TRY_CONVERT function, it will give NULL as a result.

SELECT TRY_CONVERT(bigint, '8906UP')

So the quick way to locate those outliner in NPI column is to run the query shown below:

SELECT NPI FROM Provider
WHERE try_convert(bigint, NPI) IS  NULL

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)