Problems importing a datetime from a flat file

I have a flat file with a datetime field as follows

12419,1,'T','P',229.72,2,'N',2004/may/05 19:47:42.546001
12419,1,'T','R',605.38,76,'N',2004/may/05 19:47:42.546000
12419,2,'T','P',110.49,2,'N',2004/may/05 19:47:42.546003
12419,2,'T','R',215.53,11,'N',2004/may/05 19:47:42.546002
12419,3,'T','F',9.29,1,'N',2004/may/05 19:47:42.546005
12419,3,'T','R',696,38,'N',2004/may/05 19:47:42.546004

I can NOT get last field i.e. the date time field to import into SQL werver 2005 using import wizard.

I can manually enter the value in the table so I know it can handle data in the format it is in.. (table field is date time) but I cant get import wizard to read the file and bring in the data.. It bombs at coloumn 7... which is the date field.. It works fine if I delete the milisecond portion i.e. anything after the dot but I need to keep that information.

Any help will be greatly appreciated.

What type does the SSIS package think the column is? DT_DBTIMESTAMP? DT_DATE? DT_DBDATE? DT_STR? DT_WSTR?

What errors so you get?

Please provide more information.

I strongly suspect that the value is not in a format that SSIS can understand. Your ability to type the value into a column is irrelevant - the informaiton is not stored this way

-Jamie

|||

I have tried with all column options i.e. DT_DBTIMESTAMP? DT_DATE? DT_DBDATE? DT_STR? DT_WSTR?

In every instance it comes back with error message saying

Error 0xc02020a1: Data Flow Task: Data conversion failed. The data conversion for column "Column 4" returned status value 2 and status text "The value could not be converted because of a potential loss of data.".
(SQL Server Import and Export Wizard)

When I remove the miliseconds i.e. anything after the decimal point in date time field it works fine.. It also works fine if I have the datetime in the following format

'2004-05-05 19:47:42.547000000' Instead of 2004/may/05 19:47:46.562003

Hope this helps

Thx

Amit

|||

This behaviour is as expected. SSIS will not recognise yyyy/mon/dd hh:mi:ss:XXXXXX. That is not a valid format.

You will need to import it as a string and do a manual conversion using the Derived Column component.

-Jamie

|||

Thank you for your response Jamie. You confirmed my fears. I find it rather surprising that SSIS will read upto seconds but will not read milliseconds portion.. Anyway.. weird are the ways of microsoft and/or other software vendors.. :) Every software has its quirks.. Wish they didnt..

Thank you though for all your help

Thx

Amit

|||

Well those aren't milliseconds. A millisecond is a thousandth of a second hence it will read to hh:mi:ss:XXX but not hh:mi:ss:XXXXXX and I dare say you won't find any other tool that will either!

-Jamie