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

Problem writing to fixed width text file destination

I am trying to export data from a query in SQL Server 2005 SSIS to a flat file destination. Everything works fine except the rows returned from my query are written to the flat file in one long string (i.e., without line breaks). I have tried appending a new line character to the rows returned from the query but that only throws an error when the package is executed. My rows returned from the query are 133 characters wide (essentially only one column per row) so I have set the properties accordingly for a fixed width file format with 133 character wide rows.

Any suggestions or ideas on how to correct this would be greatly appreciated.

Thank you,

Michael

What viewer are you using when looking at the text file? Are you sure you don't have newlines in the files? Look in notepad and again in word and see if that helps.|||

The Flat File Connection manager has a Format property, and I guess you have selected "Fixed Width", but strictly speaking this format does not include row delimiters. What you actually need is Ragged Right.

The best way to do this is to open your Destination, and click the New connection button. Now read the options carefull, as most people probably select #2, as it says Fixed Width, but #3 is what you want, Fixed Width with Row Delimiters. This builds the appropriate connection using Ragged Right, giving you what you want.