SSIS Bulk Insert to table with identity

Question

Bulk Insert task with the following and getting error.

1) Destination Table is [dbo].[table1]

CREATE TABLE [dbo].[table1](
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Column1] nvarchar NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

2) Source Connection - File is file1.csv

field1,field2,field3
value1,value2,value3

3) Format

4) Options - Enable identity insert

5) Error
Error: 0xC002F304 at Bulk Insert Task Identity, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Bulk load: An unexpected end of file was encountered in the data file.".

Answer 1

Hi @Leau Bee Lin ，

Thanks for your reply, I fully understand your need now.

Please create Destination Table with below code.

CREATE TABLE [dbo].[table1](  
[Column1] [nvarchar](max) NULL,  
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]  

Format as below.

In the package, we may need to use Execute SQL Task to add the IDENTITY ID column.

ALTER TABLE table1 ADD ID  [bigint] IDENTITY(1,1) NOT NULL  

And then we could get the table1 in the database as you need.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

Answer 2

Hi @Leau Bee Lin ，

The ColumnDelimiter in Format should be Comma(,) cause the (CSV) file is a delimited text file that uses a comma to separate values.

In addition, if the csv file has three columns, the table1 should also have three columns or the bulk insert will be like below.

Regards，

Zoe

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Hot issues October

Answer 3

This is the correct result. Content of each row in csv will be in Column1. ColumnDelimiter will be Tab.
How do you get the above result as I am getting error? What is missing?

Answer 4

Got the below error after setting ColumnDelimiter to "Comma (,)".

Error: 0xC002F304 at Bulk Insert Task Identity, Bulk Insert Task: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.Cannot bulk load because the maximum number of errors (10) was exceeded.Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 11, column 1 (ID).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 10, column 1 (ID).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 1 (ID).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 8, column 1 (ID).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 7, column 1 (ID).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 6, column 1 (ID).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 5, column 1 (ID).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 4, column 1 (ID).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 1 (ID).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (ID).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (ID).".

Answer 5

Previous error was meant for another csv file.

With file1.csv as below,
field1,field2
value1,value2

I am getting this error.
Error: 0xC002F304 at Bulk Insert Task Identity, Bulk Insert Task: An error occurred with the following error message: "Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 2, column 1 (ID).Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 1 (ID).".