DTS Error

I am sometimes getting the following error in MSSQL:

A DTS Package...

< ERRORERRORERRORERROR >

The number of failing rows exceeds the maximum specified.
TransformDateTimeString 'DTSTransformation_3' column pair 1 (source column 'Col003' (DBType_STR) destination column actual_date (DBType_WSTR))

Cannot parse input data string beginning at '1500'

< \ ERRORERRORERRORERROR >

Error is being throw with the source:
20050418131500

It has thrown this error for the last 3 Mondays but has worked in between.

What is the deal here? Thanks.

MORE DTS INFO:

DTS Package Description:
Transformation is of type: DateTimeString
Properties are:
Source yyyyMMddhhmmss (ex. 20050419100241)
Destination MMddyyyy (ex. 04192005)
kerkorAsked:
Who is Participating?
 
PSSUserCommented:
Don't you need HH for 24 hour. I think hh means it's 12 hour.

I'm at home at the moment, I'll check tomorrow when I'm in work.
0
 
rafranciscoCommented:
Just a wild guess, have you checked the data previously when it has thrown an error?  Could it be that it cannot understand the time if it is greater than 120000 (military time).  Maybe as long as the time is less than 120000 it's ok.
0
 
kerkorAuthor Commented:
I took a look at last Monday's time (it failed too) and it was 13 o'clock as well. I checked about 20 successfull imports and they all occured before 13 o'clock.

It looks like you have discovered why the error has occured.
Now...do you have any specific solutions? I am a sql server beginner.

Thanks! Good observation!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
kerkorAuthor Commented:
Oh the source cannot change. I have already tried. The data handlers almost walked out.
0
 
rafranciscoCommented:
You have to load the data on a temporary table first before loading to your final table.  From your temp table, you can convert this using CAST or CONVERT before inserting into your final table.
0
 
kerkorAuthor Commented:
What might the Cast or Convert Statement look like?

Data Flow:

1. Use DTS to bring data as simple string into a temp table.
2. Call Stored Proceedure which copies temp table and cast or converts this field
3 Delete all records from temp table

Thanks!
0
 
rafranciscoCommented:
Those steps are correct.

If you don't want the time portion of the input, you can do this:

SELECT CAST(LEFT(InputDate, 8) AS DATETIME)

If you also want the time, try this:

SELECT cast(left(InputDate, 8) + ' ' + substring(InputDate,9,2) + ':' + substring(InputDate, 11,2) + ':' + substring(InputDate, 13,2) as datetime)
0
 
PSSUserCommented:
Sorry, haven't been able to find anything indicating HH is different from hh. Might be excel formatting I was thinking of.
0
 
rafranciscoCommented:
kerkor, were you able to solve this problem?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.