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

Who is Participating?
MuffyBunnyConnect With a Mentor Commented:
In your SSIS package, use the build query option long enough to look at the entire dataset you're importing. Thoroughly scan that column for values that wont convert. You may have to use a Data Conversion between your source and destination.
lcohanConnect With a Mentor Database AnalystCommented:
Make sure the table where you inport it in allows NULL values. Also make sure appropriate datetime FORMAT is used to preserver the right dd/mm/yyyy for your source/destination if using datetime or similar datatype.
bar0822Author Commented:
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.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
Reza RadConnect With a Mentor Consultant, TrainerCommented:
could you put sample datetime values from flat file here?
what is your destination field data type?
bar0822Author Commented:
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).    
Reza RadConsultant, TrainerCommented:
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"
 Retain null values from source as null values in data flow
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.
Alpesh PatelAssistant ConsultantCommented:
Convert date to YYY-MM-DD and save it to table using derived column
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.