A common issue with the ELT process is that you find out errors after they have been loaded. This is usually not any issue unless the database specifically doesn’t have an option to help identify the error specifically. An example of this is the relief expressed by developers when Microsoft announces the release of the Verbose Truncate messages.
Previously we would only know a truncation error will occur not exactly which row or data is the cause of it. This was fixed by expressing in the error message the exact value that was causing the issue. However another more frequently faced issue was conversion errors. Those familiar with SSID will attest that SSIS redirects rows for both Truncation and Conversion errors.
In SQL Server we can identify the truncate errors by looking for the error message itself. Finding conversion issues is slightly different since you need to call the TRY_CAST, TRY_COVERT functions on the columns you want to troubleshoot.
As demonstrated below:-
create table datedata ( id int identity(1,1) , DateasString varchar(100)) go insert into datedata select '04/04/2020' union select '04/04.2020' go select * from datedata where isdate(DateasString) =1 select cast(dateasstring as date) from datedata select * , try_cast( dateasstring as date ) from datedata go select * , try_convert(date , dateasstring ) from datedata go select * from datedata
This was an issue I recently faced when loading some data from a client system. The source data is highly prone to errors. As a result we had to store all data in varchar data types in staging and cleanse the data before the final load.
Often we would get issues with date format etc. and this was addressed by using the TRY_CAST function.