Solved

Invalid character value for cast specification on Flat File Import

Posted on 2011-02-16
10
1,907 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
  • 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 39

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
 
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
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now