[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 464
  • Last Modified:

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)
0
kerkor
Asked:
kerkor
  • 4
  • 3
  • 2
1 Solution
 
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
 
kerkorAuthor Commented:
Oh the source cannot change. I have already tried. The data handlers almost walked out.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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:
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
 
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now