Unexpected EOF during bulk insert

Question

USE [campaigns]
DECLARE @FILENAME varchar(400) = 'W:\PlayerListing\List.txt'
    ,@EMAIL_SOURCE varchar(50) = 'listing'
    ,@SQLCMD varchar(8000)

PRINT (CAST(GETDATE() AS varchar(30)) + '     BULK INSERT TABLE CREATION STARTING')
SELECT @SQLCMD = '
CREATE TABLE BULK_INSERT_TABLE(
[id] [int] NOT NULL,
[e-mail] [varchar](60) NOT NULL,
[first_name] [varchar](60) NOT NULL,
[last_name] [varchar](60) NOT NULL,
[zip] [varchar](60) NULL)'
EXECUTE (@SQLCMD)
PRINT (CAST(GETDATE() AS varchar(30)) + '     BULK INSERT TABLE CREATION COMPLETE')
PRINT (CAST(GETDATE() AS varchar(30)) + '     BULK INSERT LOAD STARTING')
SELECT @SQLCMD = '
BULK
 INSERT BULK_INSERT_TABLE
 FROM ''' + @FILENAME + ''' 
 WITH
 (
 FIELDTERMINATOR = ''|'',
 ROWTERMINATOR = ''
'', FIRE_TRIGGERS 
 )'
EXECUTE (@SQLCMD)
print ('(' + CAST(@@RowCount AS varchar(20)) + ' row(s) affected)')


PRINT (CAST(GETDATE() AS varchar(30)) + '     BULK INSERT LOAD COMPLETE')
PRINT (CAST(GETDATE() AS varchar(30)) + '     Update ' + @EMAIL_SOURCE + ' STARTING')
PRINT (CAST(GETDATE() AS varchar(30)) + '     Update Production from Staging (opt-out)     STARTING')
SELECT @SQLCMD = '
-- Does not exist on Staging
UPDATE  ' + @EMAIL_SOURCE + '  set isActive = 0 where [ID] not in (select [ID] from     BULK_INSERT_TABLE)
'
EXECUTE (@SQLCMD)
print ('(' + CAST(@@RowCount AS varchar(20)) + ' row(s) affected)')
PRINT (CAST(GETDATE() AS varchar(30)) + '     Update Production from Staging (opt-out)     COMPLETE')
PRINT (CAST(GETDATE() AS varchar(30)) + '     Update Production from Staging (opt-in)     STARTING')
-- Does not exist on Production
SELECT @SQLCMD = '
insert into  ' + @EMAIL_SOURCE + '  ([id],[E-mail],[first_name],[last_name],[zip])
SELECT [id],[E-mail],[first_name],[last_name],[zip] FROM BULK_INSERT_TABLE WHERE [ID] NOT IN (select [ID] from  ' + @EMAIL_SOURCE + ' )'
    EXECUTE (@SQLCMD)
print ('(' + CAST(@@RowCount AS varchar(20)) + ' row(s) affected)')
PRINT (CAST(GETDATE() AS varchar(30)) + '     Update Production from Staging (opt-in) COMPLETE')
-- Mismatch of data
PRINT (CAST(GETDATE() AS varchar(30)) + '     Update Production from Staging (user data changes) STARTING')
SELECT @SQLCMD = 'CREATE TABLE CHECKSUM (CheckSum1 int, CheckSum2 int, [id] int)'
EXECUTE (@SQLCMD)
print ('(' + CAST(@@RowCount AS varchar(20)) + ' row(s) affected)')

SELECT @SQLCMD = '
INSERT INTO CHECKSUM
    SELECT BINARY_CHECKSUM(t1.[id],t1.[e-mail],t1.[first_name],t1.[last_name],t1.[zip]) AS ''CheckSum1'' 
    ,BINARY_CHECKSUM(t2.[id],t2.[e-mail],t2.[first_name],t2.[last_name],t2.[zip]) AS ''CheckSum2''
    ,t1.[id] 
    FROM   ' + @EMAIL_SOURCE + '   t1 
    JOIN BULK_INSERT_TABLE as t2 ON t1.id =t2.id'
EXECUTE (@SQLCMD)
print ('(' + CAST(@@RowCount AS varchar(20)) + ' row(s) affected)')

SELECT @SQLCMD = '
        UPDATE   ' + @EMAIL_SOURCE + '   
        set [e-mail] = BI.[e-mail]
            ,[first_name] = BI.[first_name]
            ,[last_name] = BI.[last_name]
            ,[zip] = BI.[zip]
            ,[isActive] = 1 
        FROM   ' + @EMAIL_SOURCE + '   EL
        INNER JOIN BULK_INSERT_TABLE as BI on BI.[id] = EL.[id]
        INNER JOIN CHECKSUM as C on C.[id] = EL.[id]'
EXECUTE (@SQLCMD)
print ('(' + CAST(@@RowCount AS varchar(20)) + ' row(s) affected)')

SELECT @SQLCMD = 'DROP TABLE CHECKSUM'
EXECUTE (@SQLCMD)
print ('(' + CAST(@@RowCount AS varchar(20)) + ' row(s) affected)')
PRINT (CAST(GETDATE() AS varchar(30)) + '     Update Production from Staging (user data changes) COMPLETE')
PRINT (CAST(GETDATE() AS varchar(30)) + '     Update ' + @EMAIL_SOURCE + ' COMPLETE')

SELECT @SQLCMD = '
DROP TABLE BULK_INSERT_TABLE'
EXECUTE (@SQLCMD)

Errors:

Nov 11 2013  4:59PM     BULK INSERT TABLE CREATION STARTING
Nov 11 2013  4:59PM     BULK INSERT TABLE CREATION COMPLETE
Nov 11 2013  4:59PM     BULK INSERT LOAD STARTING

Msg 4832, Level 16, State 1, Line 2
Bulk load: An unexpected end of file was encountered in the data file.

Msg 7399, Level 16, State 1, Line 2
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider     did not give any information about the error.

Msg 7330, Level 16, State 2, Line 2
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Here are some lines from my txt file:

596385|[email protected]|first|last
243662|[email protected]|first|last
43010|[email protected]|first|last
214963|[email protected]|first|last

Answer 1

In BULK INSERT statement

give ROWTERMINATOR = '0x0a'

Instead of ROWTERMINATOR = ''