DTS package invalid datatype conversion error 1

[Kingkumar]

Hi,
I have a dts package to import the data from an excel file into the sql server table.
It was working fine until ihad this new sheet and now it has started throwing error
"TransformCopy 'DTSTransformation_1' conversion error: General conversion failure on column pair 1 (source column 'Agent SSN' (DBTYPE_WSTR), destination column 'Agent SSN' (DBTYPE_I8))"

i know it has soemthing to do with my data type in the excel sheet being text and the table datatype in sql is float but what i want to know is how to do the conversion that i dont need to change anything in the excel sheet.
thanks.
--king

 

[dbomrrsm]

Can all the data in the 'Agent SSN' be converted to float - what sort of values are in this field

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Kingkumar]

Hi,
In the excel sheet teh data is the text format but the sql table the column has the datatype as float.
I can change for the time being but was wondering if some one is using hte package and doesnt want to do anything on the excel sheet is it possible to change the data type in the dts package instead of doing it in excel sheet
Also the other sheets are imported without anyproblem its only with this particular sheet its causing problem.

 

[dbomrrsm]

even thought the data in the excel sheet is stored as text do all the values meet the criteria of being float values:

a floating point number data from - 1.79E + 308 through 1.79E + 308

if not they will never go into a float datatype field.

You may want to change the table column in sql server to a more acceptable datatype ie varchar.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Kingkumar]

Thanks dbomrrsm,
i found the problem.
some of the values in that column were null thats why it was not able to do the conversion. by null i mean they ahd the value as 'NULL' it seems the excel file was result of osome query so....

One question is it possible to convert varchar to float in the table ?

 

[dbomrrsm]

as long as all the varchar values are convertable to float ie 'This is some text' wont convert but '1.92' should.

declare @text varchar (10)
set @text = 'This is some text'
select convert(float,@text)

declare @text1 varchar (10)
set @text1 = '1.92'
select convert(float,@text1)

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[dbomrrsm]

runthe above as two seperate pieces of code in QA the second works

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[Kingkumar]

Thanks