Help w/ data errors

HI,

Periodically, I receive these errors in my package:

The "component "Set date to NULL or format" (111)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "SettlementDate - derived" (544)" specifies failure on error. An error occurred on the specified object of the specified component.

It has to do with the fact that I have a derived column that uses an expression to format a date field. The expression is as follows:

ISNULL(TRIM(SettlementDate)) || TRIM(SettlementDate) == "" || LEN(TRIM(SettlementDate)) < 8 || TRIM(SettlementDate) == "89999999" ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)(SUBSTRING(SettlementDate,5,2) + "/" + SUBSTRING(SettlementDate,7,2) + "/" + SUBSTRING(SettlementDate,1,4))

It looks complicated, but it isn't. Basically, I'm just checking to see if the date value is NULL, a blank, less than 8 chars, or if it equals 899999999. If it is, set the date to null.

Anyways, the problem is, that the date field value changes sometimes. That is, I thought I covered the problem by checking for 89999999, but apparently other values are showing up as well, and when this happens it breaks the package.

How can I account for varying values, without having to know in advance what they are? Apparently, this problem is going to continue.

Thanks much

How about

"... || TRIM(SettlementDate) > "21000000" ? NULL(DT_DBTIMESTAMP) : ......."|||

Hmm, don't know. I went into my package and ran the step manually, and viola it worked.

I honestly don't know why it broke at 3:30 am this morning, but ran okay five hours later... I loaded the exact same data file.

This makes no sense to me.

|||Are you sure the package that runs via a schedule is the same as the one you opened up manually to execute the step?|||

yeah, that's probably it. the one that ran at 3:30 ran from a job, using the msdb package store.

the one i ran manually was from BIDS

i probably forgot to update it in msdb

i notice the "create date" column in sysdtspackages90 does not reflect the date the package is loaded into msdb, but the original creation date of the package, so there's no way to keep track of when the package was loaded into msdb

unless this data is somewhere else?