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 |
|
Stamey
Starting Member
14 Posts |
 Posted - 2011-02-01 : 14:39:38
|
| I have a fixed-length file I am importing to SQL Server. A few fields are Int on the SQL Server. I initially import the entire file into a VarChar(Max) field, one record per line in the file, then add calculated VarChar fields for each necessary field in the file layout I have been supplied with. I then use a Data Flow task to move the data via OLEDB from the Staging table to the Prod table, where the regular fields are in the tables, being VarChar, Int, or Numeric fields. In the case of the Int or Numeric fields, I use a Derived Column transformation to convert the data from String to Int or Numeric (when a decimal is involved).My problem arose when I attempted to convert a VarChar field containing 5 spaces, " ", to an Int I tried (DT_I4)Trim([Field]) and (DT_I4)[Field] And (DT_I8)[Field] and (DT_I8)Trim([Field]) And (DT_DECIMAL, 0)Trim([Field]), but no matter what I have tried, I still get an error regarding this one field. The Source field is Calculated VarChar(5) and the destination is Int.I get errors about it not being able to convert the data, such as "invalid character value for cast specification", or "The value could not be converted because of a potential loss of data."The way I have worked around it is to change the destination table data type to VarChar(5), but I would like to figure this out.Thanks,Chris |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-01 : 17:47:17
|
You need to null out those spaces instead of trimming them. You can use an expression to do this It happens when you try to convert empty strings to values.the expression would be something likeTRIM([column 1]) == "" ? NULL(DT_I4) : [column 1]case ? true : false
Poor planning on your part does not constitute an emergency on my part. |
 |
|
|
Stamey
Starting Member
14 Posts |
Posted - 2011-02-02 : 07:34:28
|
| Wow, that did work. I was a bit skeptical. Thanks!What's strange is that I have never had to do this before. What I mean is I have values coming through to other tables where I use "(DT_I8)DTE_LAST_UPDATE", where the value is something like 20010128, and no problem going from a String to an Int using the above. I suppose that's because there is some kind of usable value in the field, not just a string of spaces. With as many fixed-length files as I have designed packages to load, I would think I would have run into this before.In any case, thanks for the help on this. The package runs nicely now.Chris |
|
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2011-02-02 : 11:38:45
|
That is becaue 20010128 is the ISO standard date format. SQL always understands that to be a date if it is going into a date field.However, if you tried to send an empty string into the date field, it would fail for the same reason you encountered here.
Poor planning on your part does not constitute an emergency on my part. |
|
|
|
vithaljs
Starting Member
12 Posts |
Posted - 2011-06-23 : 06:57:10
|
| Hi there,i'm also getting the same problem. 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 columnlittle help would be greatly appreciated. vithal |
|
|
|
|
|
|
|
|
Data Type Conversion Error