Ed Wagner (1/29/2016)
Once you get the syntax of the format file down, the BULK INSERT command is a rock-solid approach. You get to control the data type and maximum length of each and every column, the delimiters and everything else. You can write the whole thing in a stored procedure and the format file controls how the data is brought you into the staging table. I've used it for years and it's very reliable.The format file is covered at https://msdn.microsoft.com/en-us/library/ms190393.aspx.
I'll also add that it will also allow you to sequester bad rows in a file for troubleshooting without it blowing the whole batch off, if you need something like that.
--Jeff Moden
Change is inevitable... Change for the better is not.