Here is an example of my code which is working... The Error line you see below has a comman delimiter so I can produce the error because the CSV file is tab delimited. thus producing this error message

IDErrorNumberErrorSeverityErrorStateErrorProcedureErrorLineErrorMessageDateTimeStamp

17330162usp_ProcessDailyCEData_Error60CSV File Bulk_Insert:Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".2/6/2014 8:55:22 AM

-- Here is my code

Truncate table [dbo].[DailyReportData];

Begin Try

begin

--- The CSV file must have all the columns

BULK INSERT [dbo].[DailyReportData]

FROM 'C:\CEData\Daily_CE_Dispenser_Status_Report.csv'

-- WITH (MAXERRORS = 0,FIELDTERMINATOR = '\,', ROWTERMINATOR = '', FIRSTROW = 4); --error line

WITH (MAXERRORS = 0,FIELDTERMINATOR = '\t', ROWTERMINATOR = '', FIRSTROW = 4);

PRINT '.. Completed: Bulk Insert Daily_CE_Dispenser_Status_Report.csv into [dbo].[DailyReportData].'

end

End Try

Begin CATCH

begin

INSERT INTO UserExitData.dbo.ProcessCSV_Error

(ErrorNumber, ErrorSeverity, ErrorState, ErrorProcedure, ErrorLine, ErrorMessage, DateTimeStamp)

SELECT

ERROR_NUMBER() AS ErrorNumber,

ERROR_SEVERITY() AS ErrorSeverity,

ERROR_STATE() as ErrorState,

ERROR_PROCEDURE() as ErrorProcedure,

ERROR_LINE() as ErrorLine,

'CSV File Bulk_Insert: ' + ERROR_MESSAGE() as ErrorMessage,

GetDate() as DateTimeStamp;

Print('** Bulk Insert Process error: see table dbo.ProcessCSV_Error for details.**')

goto ExitOnError

end

end catch