Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  SSIS and Import/Export (2005)  Bulk insert challenge

Author  Topic  mikera Starting Member 1 Post Posted - 2013-11-08 : 13:04:51 I have several txt files that I need to import into a SQL database. I don't control the export and some of the files contain text fields where CR LF or just LF is used as a line break. This is causing a problem because the row delimiter is CR LF. The file does use " as a text qualifier (and embedded delimiters are escaped by doubling up the ""), but I am getting errors when I import the file using SSIS and a bulk insert task. The challenge I'm facing is it is difficult to troubleshoot based on the error since I can't tell exactly which rows are triggering the errors. I'm using Notepad++ to view the txt files and can see the LF and CR LF characters but they still begin new lines so I don't know which row actually contains the offending character(s) as reported in the errors. Can someone help me figure out how to successfully import a txt file that uses " as a text qualifier, <tab> as a column delimiter, and CR LF as the row delimiter where CR LF or just LF is contained within some columns? Here are the errors on the 4 files that are giving me fits: Error: 0xC002F304 at Import APPOINTMENTNOTE_IMPORT Table, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Cannot bulk load because the maximum number of errors (10) was exceeded.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4234, column 2 (NOTES).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4018, column 2 (NOTES).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3870, column 2 (NOTES).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3758, column 2 (NOTES).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3724, column 2 (NOTES).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3451, column 2 (NOTES).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2197, column 2 (NOTES).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2171, column 2 (NOTES).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1944, column 2 (NOTES).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1916, column 2 (NOTES).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 56, column 2 (NOTES).". Error: 0xC002F304 at Import ANNUITYCASEDETAILS_IMPORT Table, Bulk Insert Task: An error occurred with the following error message: "Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8700, column 19 (NOTES).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8694, column 19 (NOTES).". Error: 0xC002F304 at Import ENTITY_IMPORT, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Cannot bulk load because the maximum number of errors (10) was exceeded.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 34365, column 12 (CORPNAME).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 34338, column 12 (CORPNAME).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 34245, column 12 (CORPNAME).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 34134, column 3 (NAME).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 14895, column 12 (CORPNAME).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 13224, column 3 (NAME).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 12846, column 3 (NAME).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9391, column 12 (CORPNAME).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9326, column 12 (CORPNAME).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7188, column 12 (CORPNAME).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1383, column 12 (CORPNAME).". Error: 0xC002F304 at Import PREMIUMDETAILS_IMPORT Table, Bulk Insert Task: An error occurred with the following error message: "Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 119802, column 27 (CRTDATE).".