Insert large size of data from txt, csv file to SQL Server table with few invalid rows

I need to insert data from data file into a SQL Server table. The datafile 开发者_如何学Pythonmay contains thousands of rows but there is a possibility that some lines in the file are wrong (e.g BIRTH_DATE column value is in wrong format or string cannot be converted to int). I may use bulk insert feature but as some rows in the file are not valid no rows will be inserted because of few invalid ones. I would like to ignore wrong lines and ideally get line number of each invalid row. I would like to do with the highest performance. Any ideas?

Thanks in advance

-Petro

---

I usually handle this kind of situation in one of two ways:

1. Bulk copy the data into a staging table. The staging table might be all VARCHAR columns or it might be one long VARCHAR column for each row depending on the format and quality of the data. Once it's in the staging table I can then do checks and extract out any rows with issues to be handled later with human intervention.

2. SSIS includes nice facilities to redirect rows with errors. Check the appropriate documentation in MSDN online and if you have any specific issues you can create a new question here and I'm sure that people can help you through it.