The ole db provider bulk for linked server null reported an error

I am trying to import a simple CSV file into a table on SQL Server 2017. The CSV file and table match each other in number of columns and data types. This is an extract of the CSV file:

UID,customerID,DateT,TypeID,AssociatedID,AttributeID,LandingPage,jsonT,ClientIPAddress
9150,2345,2020-09-01T00:00:36Z,1,,859,campaign01.html,{'product': 'string01', 'type': '', 'string011'},192.168.200.2
9151,2356,2020-09-01T00:02:30Z,1,,640,campaign02.html,{'product': 'string02', 'type': '', 'string022'},192.168.200.1

This is my table:

CREATE TABLE [dbo].[activity](
    [UID] [bigint] NOT NULL,
    [customerID] [bigint] NOT NULL,
    [DateT] [datetime] NULL,
    [TypeID] [bigint] NULL,
    [AssociatedID] [float] NULL,
    [AttributeID] [bigint] NULL,
    [LandingPage] [varchar](256) NULL,
    [jsonT] [varchar](1024) NULL,
    [ClientIPAddress] [varchar](64) NULL,
)
GO

This is my import statement:

BULK INSERT dbo.activity
FROM 'C:tmpactivity.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '0x0a',
    BATCHSIZE = 1000,
    MAXERRORS = 2
)
GO

I keep getting an error saying the last column (the client IP) is to be truncated. I checked all the values and they have nothing but IPs. Also the error is not on all rows and I cannot see for my life any difference between one IP format or the other, they are just IPs! I tried simply making the ClientIPAddress field bigger (just for the sake of it) but I just keep getting the same error.

Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 2, column 9 (ClientIPAddress).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 5, column 9 (ClientIPAddress).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 6, column 9 (ClientIPAddress).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (2) was exceeded.
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)".

Any idea on what I could check or what I am doing wrong would be highly appreciated.

Thank you!

BULK INSERT TASK IN SSIS

Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”
August 13, 2009
[Bulk Insert Task] Error: 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.”.

Solution:

Wrong setting of ColumnDelimiter= tab when actually it was ColumnDelimiter= comma.
You should choose ColumnDelimiter= comma [for bulk insert] as see below

Destionation TAble:

create table  Cricketerinfo(cid int,cname varchar(30),cposition varchar(30),cage int)

select * from Cricketerinfo

Source File:

1,sachin,opener,35

2,dravid,middle,32

3,ponting,middle,3

4,azhar,lower,36

That is it in SSIS

you can know in T-SQL to do same task as see below:

BULK INSERT dbo.Cricketerinfo 
FROM ‘D:SQLMy SSISCricketerinfo.txt’ 
WITH ( FIELDTERMINATOR =’,’, FIRSTROW = 2 )


Demo from web:

BULK INSERT Task used to copy the large amount data from text files to SQL Server Tables.

EX: You have one (1) million records of data present in your text (.txt) file and if you want to import into SQL Server Table, the easiest way to achieve this is by using BULK INSERT Task.

You can configure BULK INSERT Task Like Below:

1. Drag and drop BULK INSERT TASK from Toolbox to CONTROL FLOW

2.Double Click on Bulk Insert Task to open task editor  . After Opening your Task should look like this

3. Click on “Connections” from the left side menu and choose your SQL DATABASE and Related TABLE By configuring OLEDB Connection Manger.

4. Select “source file” connection path under “source” option.

5. click on the “options” in the left tab of the editor, and select Starting row(First row). Also specify actions to perform on the destinations table or view when the task inserts the data.

6. At last run the package and your source(.txt) data will copy into Destination (SQL Server Table).

Bulk Insert task is easy to use and configure. But it has some limitations. They are:

1.It only allows to append the data into  the table  and you cannot perform truncate and

load.

2. Only FaltFile can be used as source and not any other types

3. Only SQL Server Databases Can be used as destination. It doesn’t Support any other

Files/RDBS Systems.

4. A  failure in the Bulk insert task does not automatically load back successfully loaded

batches

5. only members of SYSADMIN fixed server role can run a package that contains Bulk.