I am looking for a solution to my task. I am importing 800 000 records in CSV format into SQL server. The data is comma separated and the text qualifier is double quotes. But some data in columns contain comma. When I tried it using SSIS package, getting the following error
Error: Data conversion failed. The data conversion for column "xyz" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
Therefore I googled and found one solution as Bulk Insert. The data is uploaded but with errors. The column containing comma are splitting into another columns. Below is the query.
BULK INSERT  FROM 'E:\5022019.csv' WITH ( FIRSTROW=2, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) GO
Can any one help me out to eliminate the splitting of text into another columns.