Date format changed in error output text file

Question

I have a file to be loaded into SQL Server 2005 using SSIS 2005.

The file has a date field. The values are like '12/01/2010 16:38:51'.

Some of the rows in the file are wrong and will be redirected to a text file. But in the text file, the date will be changed to 2010-01-12 16:38:51 by SSIS.

Anyway to just redirect error rows to the text file without any modification?

SSIS just writes the date the same way the server is configured. Change your regional settings to match the first date format. — Dominic Goulet, Feb 15 '13 at 15:40
I can't change regional setting. I mean the date format in input file can be of different format. I wouldn't change regional setting to suite the import file. What if the date format change in future? I mean can't ssis just treat the redirected row like a string or something and write it to a file? — thotwielder, Feb 15 '13 at 17:20
Yes SSIS can treat it as a string, if you define the metadata so. If it changes format, thats because you mapped the input column as date. — Dominic Goulet, Feb 15 '13 at 18:15
Yeah, I have been playing with that. Like change the input column type to string, etc. But I found the problem is not related to the input column type. Now the input column type is still datebase_timestamp, and the outputed error rows can keep the original date format. I don't know what exactly fixed the problem be cause I have changed lot of things. — thotwielder, Feb 18 '13 at 11:00

Charles380 · Answer 1 · 2013-02-15T20:33:27.060

0

You could always use a derived column and format the datetime using the parts of the date.

(DT_WSTR, 20) (DT_WSTR, 2) RIGHT("0" + (DT_WSTR,2)MONTH(<DATE_IN_QUESTION>),2)  + "/" + 
(DT_WSTR, 2) RIGHT("0" + (DT_WSTR,2)DAY(<DATE_IN_QUESTION>),2) + "/" + 
(DT_WSTR,4)YEAR(<DATE_IN_QUESTION>) + " " + 
(DT_WSTR, 2)DATEPART( "Hh", <DATE_IN_QUESTION>) + ":" +  
(DT_WSTR, 2)DATEPART( "mi", <DATE_IN_QUESTION>) + ":" +  
(DT_WSTR, 2) RIGHT("0" + (DT_WSTR, 2)DATEPART( "ss", <DATE_IN_QUESTION>),2)

edited Feb 15 '13 at 20:33

answered Feb 15 '13 at 18:15

Charles380

1,269
8
19

Yeah I know derived column can be used to do whatever format needed. But my question is the redirected rows should keep the original format without any manual effort. – thotwielder Feb 18 '13 at 11:02

score 0 · Accepted Answer · answered Feb 18 '13 at 11:10

I found if the output columns in the flat file error output of advanced editor of the flat file source is 'Flat File Source Error Output Column' (and the type of the column should be text stream [DT_TEXT] by default) then my problem is solved. This way, the flat file manager for the error output will automatically set the output columns to 'Flat File Source Error Output Column' and of type 'text stream [DT_TEXT]'. This is exactly what I wanted. SSIS treat the whole row as a single column of type 'text stream [DT_TEXT]' and just redirected. And this seems to be the default behaviour of SSIS. But in my original package, I somehow messed up and let each column from input mapped to a column in the error output. Then there was problem because SSIS was doing the date conversion things.

Anyway thanks for the help.

Date format changed in error output text file

2 Answers2