The value could not be converted to BIGINT

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SSIS and Import/Export (2008)

The value could not be converted to BIGINT

Author

Topic

vithaljs
Starting Member

12 Posts

Posted - 2011-06-23 : 07:25:02

Hi there,

I'm getting "The value could not be converted because of a potential loss of data.". error
i'm trying import the CSV file Using the SSIS dataflow task. i'm trying to convert the VARCHAR column (which contains some blank space) to the BIGINT but i'm getting following error

[OLE DB Destination [558]] Error: There was an error with input column "Column 10" (1053) on input "OLE DB Destination Input" (571). The column status returned was: "The value could not be converted because of a potential loss of data.".

the sample data is

SCCS:ISUP,CC1,005.002.024,17,246.142.071,A01,5102,5102,
SCCS:ISUP,CC1,005.002.024,73,005.001.184,A01,,,

the i'm getting the error at the 7th column

little help would be greatly appreciated.

vithal

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-23 : 08:02:30

There is no blank space in your shown "sample data".
Are you able to show the value that is generating the error?

No, you're never too old to Yak'n'Roll if you're too young to die.

vithaljs
Starting Member

12 Posts

Posted - 2011-06-23 : 08:15:07

thanks for the reply,

the 7th column in the first line of the sample data is 5102 (...A01,5102,)
where as 7th column in the 2 nd line is blank space (...A01,,,)

i'm reading 7th column as VARCHAR and converting it to BIGINT using derived column. During conversion i'm getting the error mentioned above.

vithal

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-23 : 08:19:59

select convert(bigint,'')
or
select convert(bigint,' ')
isn't giving an error.

No, you're never too old to Yak'n'Roll if you're too young to die.

vithaljs
Starting Member

12 Posts

Posted - 2011-06-23 : 08:25:25

yeah if you convert it seperately it wont give error.

i'm trying to convert VARCHAR to BIGINT in SSIS using Derived Column task.
Actually my job is to read the data from the CSV file and store into database table (in this table the 7th column is declared as a BIGINT).
i'm using datafolw task with the flat file source connection to do this job.

vithal

vithaljs
Starting Member

12 Posts

Posted - 2011-06-23 : 08:30:23

hi,

i'm using following expression in the derived column task to convert blank space to NULL
LTRIM(RTRIM([Column 6])) == "" ? (DT_STR,50,1252)NULL(DT_STR,50,1252) : (DT_STR,50,1252)LTRIM(RTRIM([Column 6]))

and later i'm using data conversion task to convert to BIGINT.

i'm getting error at Data Conversion task.

vithal

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-06-23 : 09:30:33

Maybe this? But I am not sure..

quote:
Originally posted by vithaljs

hi,

i'm using following expression in the derived column task to convert blank space to NULL
LTRIM(RTRIM([Column 6])) == "" ? ~~(DT_STR,50,1252)~~NULL(DT_STR,50,1252) : (DT_STR,50,1252)LTRIM(RTRIM([Column 6]))

and later i'm using data conversion task to convert to BIGINT.

i'm getting error at Data Conversion task.

vithal

No, you're never too old to Yak'n'Roll if you're too young to die.

vithaljs
Starting Member

12 Posts

Posted - 2011-06-24 : 00:11:53

No this one is also giving error!
the same error!

vithal

latch
Yak Posting Veteran

62 Posts

Posted - 2011-06-27 : 09:44:33

use these:

only 1 derived column is needed instead of two transformation.

len(ltrim(rtrim([Column 6])))==0?NULL(DT_I8): (DT_I8)LTRIM(RTRIM([Column 6]))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources