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
