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.
| Author |
Topic |
|
rameshpalla
Starting Member
16 Posts |
 Posted - 2005-06-01 : 21:02:03
|
| Hi every one. I got a problem here. I am trying to transform data from text file to database table.when I am testing the column individually, I am getting successfully done message and I am able to view the results also.I have checked manually and the data is also correct in that field.its vehicalyear (int length 4) column to which I have to transform matching column from text file.When I am executing the package I am getting the following error.Error Source : Microsoft data Transformation Services (DTS) Data PumpError Description : The number of failing rows exceeds the maximum specified.TransformCopy 'DTSTransformation__4' conversion error: Conversion invalid for datatypes on column pair 1 (source column 'Col004' (DBTYPE_STR), destination column 'Vehyear' (DBTYPE_14)).can any one suggest me regarding this.Thanks in advance.Ramesh. |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-06-01 : 22:43:15
|
Do what I do. DTS everything in as VARCHAR. It's easier to examine the data once it is in the database.Convert datatypes as needed.Rinse and repeat.  |
 |
|
|
rameshpalla
Starting Member
16 Posts |
Posted - 2005-06-01 : 23:05:08
|
| Thank you very much Sam, but it is a proven solution which is working since 2 years perfectly, at the client location.but now only this problem is comming. If there is no other alternative, I shall do like that.but, I wanted to know if I am wrong some where and there might be some solution.Rgds,Ramesh. |
|
|
|
rameshpalla
Starting Member
16 Posts |
Posted - 2005-07-18 : 20:22:43
|
| Hello All, I still have the problem. Can any one help me please.The procedure is like this.We get an excell file from the third person. That excell sheet content is saved as tab delimited text file.after converting it into text file, we import it using DTS.previously it worked fine.but now it saysError Source : Microsoft data Transformation Services (DTS) Data PumpError Description : The number of failing rows exceeds the maximum specified.TransformCopy 'DTSTransformation__4' conversion error: Conversion invalid for datatypes on column pair 1 (source column 'Col004' (DBTYPE_STR), destination column 'Vehyear' (DBTYPE_14)).initially we have float, int, date data types which worked correctly.but due to this error, I have changed the table structure to have all varchar type.but here I have another problem, because the values are separated with commas ( 22,345 $ instead of 22345) and here I am again getting problem while I execute the SP in which I am trying to convert them into float.Although I am successful in importing them to temop table through DTS, I am getting problems in SP while I convert them to float.Can any one help me.Thanks in advance |
|
|
|
thebruins
Starting Member
31 Posts |
Posted - 2005-07-19 : 05:14:52
|
| so the '22,245' is a varchar and you're trying to make a float out of it, but that fails because MSSQL isn't recognizing the number?if it is a varchar, you could add an activex script in the DTS transformation for the column to filter out all ','s, so 22,245 would become 22245 |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2005-07-19 : 08:59:53
|
| You may need to assist in the conversion. Commas strangely seem to confuse FLOAT conversion.PRINT CAST ( '22,345' As FLOAT) -- Will failbutPRINT CAST ( REPLACE('22,345', ',', '') As FLOAT) -- Seems to workYou can also check for valid numeric values in a colum (if you want to try to identify bad fields using ISNUMERIC(MyColumn) = 0 |
|
|
|
|
|
|
|
|
Conversion error (DBTYPE_14 , DBTYPE_STR)