Link to home
Start Free TrialLog in
Avatar of bar0822
bar0822

asked on

Invalid character value for cast specification on Flat File Import

importing flat file to sql - error on datetime column in ssis 2005.  
An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available.  Source: "Microsoft SQL Native Client"  Hresult: 0x80004005  Description: "Invalid character value for cast specification".  The datetime field is imported using a temporary table as char(10) then sent to oledb SQL table.  I changed the field in sql table to to char(10) - this worked in 2000 DTS but receiving error now.   There are blanks in this field ... could this be the cause?
Please let me know how I can resolve this asap. thanx, barb

ASKER CERTIFIED SOLUTION
Avatar of MuffyBunny
MuffyBunny
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bar0822
bar0822

ASKER

I am so new to SSIS - not sure what to use for appropriate datetime - is is dt_date or dt_timestamp?

I've been changing the format but always get an error on this one particular column.
If you are wanting to put the data into a char(10) field, you will have to convert it to String. If I'm misunderstanding something and your goal is to put it in a datetime field, then yes, you would use dt_date.

No apologies for being new. Everyone starts somewhere and that's when they need the most help.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bar0822

ASKER

The dates in the flat file are mm/dd/yyyy and in order to get all the data out of the flat file, I used a SQL temp table with all the fields set to char then copy from the SQL temp table into a SQL destination table with the fields in Sql destination as smalldatetime (this is where it fails - cannot convert due to possible loss of data). However, not all the rows have a date inserted - this field allows nulls (so does SQL table) but they are coming in as empty strings and not as a null because when I used the wizard to import the file and queried for nulls, I received no records but when I queried for ' ', I received the empty rows). I am also having trouble with other columns as well with char(10) into numeric field (8,2).    
thx,
OK,
you can double click on flat file source, and in Connection Manager tab, check the "Retain null values from source as null values in data flow"
 User generated image
Use NULLIF(colname,'') too if you you want as an added weapon to ensure that sql doesn't end up trying to convert '' to a date (FAIL)
For your date issue, are some of the dates < 1900? smalldatetime only works for 1/1/1900 and greater.
Convert date to YYY-MM-DD and save it to table using derived column