Import Failure on dates 1

[Narizz28]

This one has me confused.

I am using DTS to import a text file nightly in a table. I have 4 columns designed in the Table as Datetime (Col14, Col19, Col20, COl21). When the update runs, no records are brought in and all exception out with a data type mismatch error. It states that source Col14 is type string, and dest date is type datetime. All of the dates in the text file are formated dd/mm/yyyy.

When I change the column type in the table to varchar one column at a time, it succeeds. I by chance put col19 back to datetime and it still succeeds (excpet in records that have a null for the data in col19, the output file uses \N for null representation, but that's a different problem).

The really odd part is that the data in the source file for Col19 is formatted the same way as the other datetime fields.

Has anyone ever seen this behavior or have any suggestion for getting the data to import as a date? Any help is appreciated.

Source file is Pipe delimited, LF Row delimiter, no text qualifier.

 

[LogicalmanUS]

When importing from text files (I have a number of such jobs running nightly) I tend to populate a basic varchar table (one having ALL columns of varchar data type), and then simply call a SProc to format the data and transfer it into the correct table(s).

This has two bonuses (for me). One being that if there is no data to transfer then I know not to truncate the data table first (a number of these text files are complete data sources from an AS400 system and would cause duplicates if processed directly) and the second being to avoid the errors you are experiencing and clean up the data first.

Logicalman

 

[Narizz28]

That's exactly what I was thinking logicalmanUS, my biggest problem is that I have never used SP's before (I know, WHAT?!?). I've just always found ways to keep from it. (Small company, not very DB demanding before now) Like your AS400 scenario, I'm getting a pure system dump. My DTS parent package starts by trunc'ing the tables and dropping and recreating the indexes, and then running a table-by-table import. I've been debating using a (forgive the Excel pun) Pivot-table to drop the data in and then conditionally reformat it, but wanted to explore the possibility that I was simply overlooking something before heading in a direction I've never been in from past experiences.

Thanks again for your ideas and contribution to my dilema. Any other thoughts/words of guidance/ideas are fully welcome.

 

[Narizz28]

Just an update, I got this to work by setting the column type to varchar(12), running the import, and then parsing out bad dates and altering the column type to a Datetime. This works because we drop and recreate the tables on every run of the parent package, but could also be done by starting the package to change the column to a varchar(12) at the beginning, importing the data, and parsing bad date formats and changing back to a datetime at the package end.

Thanks again for your input LogicalMan.