There are serious performance issues with loading data into very wide columns. The client I’m doing some consultancy for has a generic load routine which loads text files into nvarchar max columns and then does data type checking.
The rational behind this is that they can report any records with invalid data type mappings. However, reducing the width of the fields has a significant impact on the performance.
As an example – input record with 132 columns – reduce the size down to twice the expected size of each field gives a five fold improvement in the load procedure. Significant when you are dealing with 30Gb files!
My recommdation to them was to do stricter data typing allthough i couldn’t convince them to do the ultimate and just use an SSIS error record to mark any invalid records.