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 |
|
VT_Bassman
Starting Member
6 Posts |
 Posted - 2011-03-10 : 15:23:33
|
| I am trying to load a flat file to an existing SQL 2008 database. I get an error on a date field that is a DB DT in SSIS and is defined as Date dfata type in the database. The date in my flat file is in format YYYY-MM-DD including the 2 dashes. The error says: "The value could not be converted because of a potential loss of data."Is my data in the wrong format? |
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-03-10 : 20:12:04
|
| I have seen this type of error message in various types of situations - for example:a) If there is a data overflow (which I doubt unless your date is before Christ or after the year 10,000?). Usually that type of error happens with character data that is too long, not with dates.b) If there is some kind of bad data in the flat file. For example, in CSV files if there are unescaped commas that are part of the data, that will offset the columns to the right and cause this type of error.c) If the end-of-line character is not what SSIS expects it to be. I have seen this in files generated on some linux systemsCan't think of any others now, but I am sure there are a few more. |
 |
|
|
VT_Bassman
Starting Member
6 Posts |
Posted - 2011-03-11 : 08:43:13
|
| Thanks for your reply. None of those scenarios are true for my problem. Anyone else got any ideas??????????????? |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-03-11 : 09:48:25
|
| Try to use the SSIS data type DT_DBDATE instead. I think what you are seeing is caused by double implicit conversion. The DT_DATE type has hours/mins/seconds also so SSIS converts the data in the file to the format 'yyyy-mm-dd hh:mm:ss' and then when you try to import it to the database you get a truncation error because the DATE datatype only supports 'yyyy-mm-dd'.- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
VT_Bassman
Starting Member
6 Posts |
Posted - 2011-03-11 : 10:17:11
|
| I am using DT_DBDATE already. This error makes no sense to me. I am using a SQL Server destination as well. Should I switch to an OLE DB Destination? |
|
|
|
jim_jim
Constraint Violating Yak Guru
306 Posts |
Posted - 2011-03-11 : 13:09:30
|
| I have a similar problem where Iam having trouble to load dada from a flat file |
|
|
|
sunitabeck
Master Smack Fu Yak Hacker
5155 Posts |
Posted - 2011-03-11 : 14:09:10
|
| Seems like Lumbago's explanation makes most sense - because the error message is about potential data loss. (That is not to suggest that in my previous post I was shooting from the hip. I NEVER do! honest!! :--).You can prove out Lumbago's theory if you have a test environment where you can alter the column to change the data type from Date to DateTime2. |
|
|
|
Lumbago
Norsk Yak Master
3271 Posts |
Posted - 2011-03-14 : 04:49:15
|
| Add some data viewers between the flat file import and the target database to see the exact format of the data that is being transferred. It will be a lot faster than guessing what's going on :)- LumbagoMy blog-> http://thefirstsql.com/2011/02/07/regular-expressions-advanced-string-matching-and-new-split-function-sql-server-2008-r2/ |
|
|
|
|
|
|
Flat file load error