none
Bulk Insert Task RRS feed

  • Question

  • Dear Team,

    I am getting the below error when trying to load the data from file to table using the bulk insert task but it loaded all the records from the file to table completely.

    Please advice on this.

    BulkInsertError


    Wednesday, December 4, 2019 11:57 AM

Answers

  • Hi Lakshminarayanan SSIS,

    You can investigate what is causing the BULK INSERT error explicitly in SSMS.

    The FIRSTROW and LASTROW parameters will allow you to control what source file lines will be processed.

    To expedite the process, you can select intentionally small range of lines, like I did below: 2 to 3.

    I created the following file e:\temp\StateCity.csv:

    State, City, Established
    FL, Miami, 2019-12-06 13:15:35
    CA, Los Angeles, 2019-12-06 13:15:35
    TX, Houston,
    CO, Denver, 2019-12-06 13:15:35

    T-SQL in SSMS:

    USE tempdb;
    GO
    
    DROP TABLE IF EXISTS dbo.tbl;
    
    CREATE TABLE dbo.tbl ([State] VARCHAR(2), City VARCHAR(30), Established DATETIME);
    
    BULK INSERT dbo.tbl
    FROM 'E:\Temp\StateCity.csv'
    WITH (
    	DATAFILETYPE = 'char' -- { 'char' | 'native' | 'widechar' | 'widenative' } 
    	, FIELDTERMINATOR = ','
    	, ROWTERMINATOR = '\n'
    	, FIRSTROW = 2 
    	, LASTROW = 3
    	, CODEPAGE = '65001');
    
    SELECT * FROM dbo.tbl;

    Output:
    State	City	        Established
    FL	 Miami	        2019-12-06 13:15:35.000
    CA	 Los Angeles	2019-12-06 13:15:35.000
    Friday, December 6, 2019 1:27 PM

All replies

  • You get a clear error message: Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 10 (Load_date)

    So check the Content of the source file for column 10, if it containts valid date (I guess?) values in your regional Settings.


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, December 4, 2019 1:16 PM
  • Hello Friend ,

    It seems to me it's a conversion problem. maybe it has a special character.

    Check this row 1 in column 10 and see the data type and even the data inside it.

    If this answer has helped you, mark it helpful so that someone else with a question or similar problem can find an answer or help more easily. Jefferson Clyton Pereira da Silva - [MCSA | MCP | MCTS | MTA | Database Analyst - Sql Server and Oracle]


    Wednesday, December 4, 2019 1:32 PM
  • Hi,

    Please check the data type and value of row 1, column 10 (Load_date).

    Please check the data type of the mapping column in the Destination.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, December 5, 2019 6:45 AM
  • I understood the error but the problem is the file having 57610148 records including header column and the target also loaded with the same number of records excluding header. As it is loaded all the records into table why it is through an error and it got failed.

    Please find the screenshot for your reference.

    File Count:

    Table Count:

    Please advice on this.

    Thursday, December 5, 2019 7:00 AM
  • Hi,

    Please check if the value of row 1, column 10 (Load_date) in your destination is correct.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, December 6, 2019 9:00 AM
  • We checked that column doesn't have any NULL and also it has the validate date only in that column.
    Friday, December 6, 2019 12:54 PM
  • Hi Lakshminarayanan SSIS,

    You can investigate what is causing the BULK INSERT error explicitly in SSMS.

    The FIRSTROW and LASTROW parameters will allow you to control what source file lines will be processed.

    To expedite the process, you can select intentionally small range of lines, like I did below: 2 to 3.

    I created the following file e:\temp\StateCity.csv:

    State, City, Established
    FL, Miami, 2019-12-06 13:15:35
    CA, Los Angeles, 2019-12-06 13:15:35
    TX, Houston,
    CO, Denver, 2019-12-06 13:15:35

    T-SQL in SSMS:

    USE tempdb;
    GO
    
    DROP TABLE IF EXISTS dbo.tbl;
    
    CREATE TABLE dbo.tbl ([State] VARCHAR(2), City VARCHAR(30), Established DATETIME);
    
    BULK INSERT dbo.tbl
    FROM 'E:\Temp\StateCity.csv'
    WITH (
    	DATAFILETYPE = 'char' -- { 'char' | 'native' | 'widechar' | 'widenative' } 
    	, FIELDTERMINATOR = ','
    	, ROWTERMINATOR = '\n'
    	, FIRSTROW = 2 
    	, LASTROW = 3
    	, CODEPAGE = '65001');
    
    SELECT * FROM dbo.tbl;

    Output:
    State	City	        Established
    FL	 Miami	        2019-12-06 13:15:35.000
    CA	 Los Angeles	2019-12-06 13:15:35.000
    Friday, December 6, 2019 1:27 PM