none
How do you suppress: "Bulk load: An unexpected end of file was encountered in the data file." errors?

    Question

  • I have created a stored procedure that takes a file path, column delimiter, and row delimiter, and uploads all files in the directory. I do this because we typically get datasets that consist of 100 or more text files.

    First, here is my stored procedure;

    CREATE PROCEDURE [dbo].[sp_BATCH_UPLOAD_FROM_DIR]
    (
        @FILEPATH NVARCHAR(2000),     
        @FIELDTERMINATOR NVARCHAR(10),
        @ROWTERMINATOR NVARCHAR(10),
        @STAGINGSCHEMA VARCHAR(50),
        @STAGINGTABLE VARCHAR(50)
    )
    AS
    DECLARE @DIRPATH NVARCHAR(2000), @CURRENT NVARCHAR(2000), @SQL NVARCHAR(2000);
    DECLARE @FILENAMES TABLE ([output] NVARCHAR(2000));
    SET @DIRPATH= 'DIR "'+@FILEPATH+'" /A-D /B'
    INSERT INTO @FILENAMES EXECUTE master.dbo.xp_cmdshell @DIRPATH;
    DECLARE CUR_FILE_UPLOAD CURSOR FOR SELECT [output] FROM @FILENAMES
    OPEN CUR_FILE_UPLOAD
    FETCH NEXT FROM CUR_FILE_UPLOAD INTO @CURRENT
    WHILE @@fetch_status <> -1
    BEGIN
    SET @SQL = 'BULK INSERT ['+@STAGINGSCHEMA+'].['+@STAGINGTABLE+'] FROM '''
    +@FILEPATH+@CURRENT+''' WITH (FIELDTERMINATOR = '''+@FIELDTERMINATOR
    +''', ROWTERMINATOR = '''+@ROWTERMINATOR+''' );'
    EXEC sp_executesql @SQL
    FETCH NEXT FROM CUR_FILE_UPLOAD INTO @CURRENT
    END
    GO

    Now I execute the procedure, passing the network path of the files, comma as the column delimiter, and the hex 0x0d for carriage return;

    EXEC dbo.sp_BATCH_UPLOAD_FROM_DIR
    '\\windows_network_share\data_files\',
    ',','0x0d','dbo','My_STAGING_Table'

    Then I get this error message;

    (53 row(s) affected)
    Msg 4832, Level 16, State 1, Line 1
    Bulk load: An unexpected end of file was encountered in the data file.
    Msg 7399, Level 16, State 1, Line 1
    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 1
    Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".


    ClayM

    Friday, September 07, 2012 8:18 PM

Answers