Loading text files using DTS

[Mike Husler]

We tried to load a file that contained 1 column of datetimes of the format
yyyy-MM-dd hh:mm:ss into a table that had the column set as a datetime type.
We did this using a DTS package. The error from DTS is attached. There seems
to be strange characters hidden in the file because a visual scan of all the data yielded
that they were all of valid datetime format. The files were transfered from a UNIX machine
to Windows via FTP. The file is ~107000 lines long. The 1st corrupt record could be
thousands of lines down. i.e. never the 1st record in the text file. We tried to set the
Row delimiter in the Text File Properties to {LF} instead of {CR}{LF} and that did not
work. We also tried to use a semicolon delimiter and that did not work either.
We tried both asc and bin methods of transfer in FTP and neither worked.
If someone has encountered this problem bofore, any help would be much appreciated.

Regards,
Mike Husler

*************** *************** *************** *************** *************** *************** **********

Execution Started: 4/14/2004 11:23:11 AM
Error during Transformation 'DTSTransformat ion__1' for Row number 1. Errors encountered so far in this task: 1.\0\0

Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Error Description:Tra nsformDateTimeS tring 'DTSTransformat ion__1', column pair 1 (source column 'Col001' (DBTYPE_STR), destination column 'time_tag' (DBTYPE_DBTIMES TAMP)): Cannot parse input data string beginning at '00:08'
Error Help File:sqldts80.h lp
Error Help Context ID:31231
\0\03003-05-21 13:00:08|

Execution Completed: 4/14/2004 11:23:11 AM

*************** *************** *************** *************** *************** *************** **********

[Allan Mitchell]

From the error it looks like SQL Server could not explicitly CAST the String
(DBTYPE_STR) datatype value in your source to your DateTime
(DBTYPE_DBTIMES TAMP). I dislike going straight from Text file to DB table
and usually stage the data in a working table first. You can then run TSQL
over the top to do some scrubbing.

You could on your DataPump set the Fetch and commit sizes to 1 and set your
allowable errors count to number > lines in your file (9999 is the GUI max
so you will need to do this in code if you want more)

If using 2000 you can log errors to a text file (last tab of the datapump)

If time is not important for your destination then here is a way of doing
things also

Formatting Character Data into Datetime fields
(http://www.sqldts.com/default.aspx?249)

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Mike Husler" <Mi************ **@noaa.gov> wrote in message
news:40******** ******@noaa.gov ...

We tried to load a file that contained 1 column of datetimes of the format
yyyy-MM-dd hh:mm:ss into a table that had the column set as a datetime type. We did this using a DTS package. The error from DTS is attached. There seems to be strange characters hidden in the file because a visual scan of all the data yielded that they were all of valid datetime format. The files were transfered from a UNIX machine to Windows via FTP. The file is ~107000 lines long. The 1st corrupt record could be thousands of lines down. i.e. never the 1st record in the text file. We tried to set the Row delimiter in the Text File Properties to {LF} instead of {CR}{LF} and that did not work. We also tried to use a semicolon delimiter and that did not work either. We tried both asc and bin methods of transfer in FTP and neither worked.
If someone has encountered this problem bofore, any help would be much appreciated.
Regards,
Mike Husler

----------------------------------------------------------------------------
----
*************** *************** *************** *************** *************** *
*************** *********

Execution Started: 4/14/2004 11:23:11 AM
Error during Transformation 'DTSTransformat ion__1' for Row number 1. Errors
encountered so far in this task: 1.

Error Source: Microsoft Data Transformation Services (DTS) Data Pump
Error Description:Tra nsformDateTimeS tring 'DTSTransformat ion__1', column
pair 1 (source column 'Col001' (DBTYPE_STR), destination column 'time_tag'
(DBTYPE_DBTIMES TAMP)): Cannot parse input data string beginning at '00:08'
Error Help File:sqldts80.h lp
Error Help Context ID:31231
3003-05-21 13:00:08|

Execution Completed: 4/14/2004 11:23:11 AM

*************** *************** *************** *************** *************** *
*************** *********