import csv file to sql

Hi guys

i am trying to import a csv file into the sql, but my problem is that I have a message in one of the columns. suggestions

- Executing (Error)

Messages

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "INTVAL" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)

Error 0xc0209029: Data Flow Task: The "output column "INTVAL" (22)" failed because error code 0xC0209084 occurred, and the error row disposition on "output column "INTVAL" (22)" specifies failure on error. An error occurred on the specified object of the specified component.
(SQL Server Import and Export Wizard)

Error 0xc0202092: Data Flow Task: An error occurred while processing file "E:\minessight\blastinfoattrib.csv" on data row 2.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: The PrimeOutput method on component "Source - blastinfoattrib_csv" (1) returned error code 0xC0202092. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "SourceThread0" has exited with error code 0xC0047038.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: Thread "WorkThread0" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: Thread "WorkThread0" has exited with error code 0xC0047039.
(SQL Server Import and Export Wizard)

It appears that the data that was to be imported into the IntVal column is too big for the datatype of the column.

If you were to post the table DDL, we may be able to provide better help.

|||

Hi,

The script which has the DDL is as shown below;

Code Snippet

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GEOMSEGMENTVFD]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[GEOMSEGMENTVFD]
GO
CREATE TABLE [dbo].[GEOMSEGMENTVFD] (
[OBJECTID] [varchar] (50) NOT NULL ,
[SEGMENTID] [varchar] (50) NOT NULL ,
[VFNAME] [varchar] (50) NOT NULL ,
[TEXTVAL] [varchar] (50) NULL ,
[INTVAL] [int] NULL ,
[DBLVAL] [float] NULL ,
[BLBVAL] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
ALTER TABLE [dbo].[GEOMSEGMENTVFD] WITH NOCHECK ADD
CONSTRAINT [PRIMARYKEYGEOMSEGMENTVFD] PRIMARY KEY CLUSTERED
(
[OBJECTID],
[SEGMENTID],
[VFNAME]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
ALTER TABLE [dbo].[GEOMSEGMENTVFD] WITH NOCHECK ADD
CONSTRAINT FK_GEOMSEGMENTVFD_GEOMSEGMENT
FOREIGN KEY (OBJECTID,SEGMENTID)
REFERENCES [dbo].[GEOMSEGMENT] (OBJECTID,SEGMENTID)
ON DELETE CASCADE
ON UPDATE CASCADE

GO
ALTER TABLE [dbo].[GEOMSEGMENTVFD] WITH NOCHECK ADD
CONSTRAINT FK_GEOMSEGMENTVFD_GEOMVF
FOREIGN KEY (VFNAME)
REFERENCES [dbo].[GEOMVF] (VFNAME)
ON DELETE CASCADE
ON UPDATE CASCADE

GO

Thanks

|||

The next task is to verify that in the import data (csv file), the values to be transferred to the IntVal column are NOT outside the range of:

-2,147,483,648 through 2,147,483,647

If you find data that falls outside that range, you will have to either: correct the import data to be within that range, OR, change the IntVal datatype in the table to be a [bigint] datatype.