Chris Wooding wrote:
We do the same as others have mentioned. Load into a table where the columns are wide nvarchar and then validate afterwards. We use TRY_CAST and TRY_PARSE to check whether things are the datatypes they're supposed to be. We pass rows where all columns are valid on to the next staging table and rows where one or more columns failed into an errors table from which we can report on the problems. The advantage of this approach is that you can add validation other than pure datatype checking quite easily.
I wasn't aware of TRY_CAST and TRY_PARSE! Thank you for mentioning - will read up on those, they look very useful. I can think of at least one daily job where those might come in handy 🙂
"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?