Loading text files using DTS

**Allan Mitchell** · Jul 20 '05, 03:27 AM

Re: Loading text files using DTS

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" <Michael.P.Husl er@noaa.gov> wrote in message
news:407DAFE8.F D6BA66@noaa.gov ...[color=blue]
> 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[/color]
type.[color=blue]
> We did this using a DTS package. The error from DTS is attached. There[/color]
seems[color=blue]
> to be strange characters hidden in the file because a visual scan of all[/color]
the data yielded[color=blue]
> that they were all of valid datetime format. The files were transfered[/color]
from a UNIX machine[color=blue]
> to Windows via FTP. The file is ~107000 lines long. The 1st corrupt[/color]
record could be[color=blue]
> thousands of lines down. i.e. never the 1st record in the text file. We[/color]
tried to set the[color=blue]
> Row delimiter in the Text File Properties to {LF} instead of {CR}{LF} and[/color]
that did not[color=blue]
> work. We also tried to use a semicolon delimiter and that did not work[/color]
either.[color=blue]
> 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[/color]
appreciated.[color=blue]
>
> Regards,
> Mike Husler
>[/color]

----------------------------------------------------------------------------
----

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

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

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

Loading text files using DTS

Loading text files using DTS

Comment