Solved

Invalid character value for cast specification on Flat File Import

Posted on 2011-02-16
10
2,037 Views
Last Modified: 2013-11-10
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

0
Comment
Question by:bar0822
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 6

Accepted Solution

by:
MuffyBunny earned 167 total points
ID: 34909974
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.
0
 
LVL 40

Assisted Solution

by:lcohan
lcohan earned 166 total points
ID: 34911111
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.
0
 

Author Comment

by:bar0822
ID: 34911586
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.
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 6

Expert Comment

by:MuffyBunny
ID: 34911630
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.
0
 
LVL 30

Assisted Solution

by:Reza Rad
Reza Rad earned 167 total points
ID: 34915119
could you put sample datetime values from flat file here?
what is your destination field data type?
0
 

Author Comment

by:bar0822
ID: 34916763
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,
0
 
LVL 30

Expert Comment

by:Reza Rad
ID: 34924062
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"
 Retain null values from source as null values in data flow
0
 
LVL 6

Expert Comment

by:MuffyBunny
ID: 34942936
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)
0
 
LVL 7

Expert Comment

by:rmm2001
ID: 34989622
For your date issue, are some of the dates < 1900? smalldatetime only works for 1/1/1900 and greater.
0
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35239564
Convert date to YYY-MM-DD and save it to table using derived column
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
Viewers will learn how the fundamental information of how to create a table.

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question