Yet another date problem 1

[Alan0568]

I'm trying to do what should be the simplest of things but I'm banging my head against the wall.

I have a flat file with fixed width (8) date as yyyymmdd. I want to import this into a table with smalldatetime data type. I would have thought as its yyyymmdd I could just map it and it would be read in correctly but I get the following errors.

"Invalid character value for cast specification".

"The value could not be converted because of a potential loss of data."

I had set up column in the flat file connection manager as string [DT_STR] output length(8). So I have tried changing this to all the available date types with no joy. I have also tried adding a derived column task and casting the field to all the different date types, still no joy.

There are no duff characters or blanks in the flat file. If I import the data into a varchar(8) column I can then convert that column to smalldatetime without any error. All the dates are read correctly.

Help would be appreciated.

 

[mrdenny]

You'll need to put a Data Conversion transformation between the source and destination within the Data Flow.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Alan0568]

Denny, Thanks for the reply. I had already tried a data conversion to
DT_DATE, DT_DBDATE & DT_DBTIMESTAMP but they all fail when trying to insert the YYYYMMDD from flat file to a smalldatetime column. I have got it to work now by using a derived column transformation with...

SUBSTRING([Column 0],1,4) + "/" + SUBSTRING([Column 0],5,2) + "/" + SUBSTRING([Column 0],7,2)

It would appear the "/" are required. If anyone can confirm that this is the only way to import YYYYMMDD from flat file to a smalldatetime column I would be grateful as I will eventually have a job with dozens of such columns. I am using English US (1252) in all properties/settings.

Thanks

 

[gradley]

Yes that is what I had to do as well. A derived column using the "/" seems to work best. You should also consider what happens when you have NULL date values. Make sure you select the checkbox "Retain null values from the source as null values in the dataflow" in the flat file source definition. You will want to omit these from the derived column process.

I've found dates to be a real pain in the SSIS world.