I've inherited an SSIS package which attempts to get data from an Oracle database over which I have no control, and insert it into SQL Server. As part of this, we format a date as string mm/dd/yyyy through a column transformation. Just today, Oracle started sending us May 1, 111 as a date on one particular record (yes, year = 111). So, I'm trying to add an edit to my transformation to say: "If the year is less than 1900, make my output string mm/dd/ from the input, and current year". Here's my attempt: ISNULL(PROGRAM_STATUS_DATE) ? "" : (YEAR(PROGRAM_STATUS_DATE) < 1900 ? (DT_WSTR,2)DATEPART("month",PROGRAM_STATUS_DATE) + "/" + (DT_WSTR,2)DATEPART("day",PROGRAM_STATUS_DATE) + "/" + (DT_WSTR,4)DATEPART("year",GETDATE()) : (DT_WSTR,2)DATEPART("month",PROGRAM_STATUS_DATE) + "/" + (DT_WSTR,2)DATEPART("day",PROGRAM_STATUS_DATE) + "/" + (DT_WSTR,4)DATEPART("year",PROGRAM_STATUS_DATE)) In testing, it's failing with the following: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR. The "component "alter columns" (583)" failed because error code 0xC0049063 occurred, and the error row disposition on "output column "FORMATTED_PROGRAM_STATUS_DATE" (5125)" specifies failure on error. An error occurred on the specified object of the specified component. There may be error messages posted before this with more information about the failure. SSIS Error Code DTS_E_PROCESSINPUTFAILED. The ProcessInput method on component "alter columns" (583) failed with error code 0xC0209029. The identified component returned an error from the ProcessInput method. The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running. There may be error messages posted before this with more information about the failure. SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread0" has exited with error code 0xC0209029. There may be error messages posted before this with more information on why the thread has exited. I rather suspect the failure is due to Microsoft's problems with years < 1743 (? I think that's the min year), but if I can't look into a valid (but presumably wrong) date object, I'm not sure how I'm going to work around this problem. Anybody have any ideas? Thanks in advance.

You cannot use DATEPART on a date that is not formed correctly so that may be your problem. You could change it by formatting it with string manipulations to get your end result in the condition. something like... (right function probably not the answer, just an example) YEAR((DT_DATE)@[User::Dater]) < 1900 ? (DT_WSTR,2)DATEPART("month",(DT_DATE)@[User::Dater]) + "/" + (DT_WSTR,2)DATEPART("day",(DT_DATE)@[User::Dater]) + "/" + (DT_WSTR,4)DATEPART("year",GETDATE()) : RIGHT(@[User::Dater],2) Ted Krueger Blog on lessthandot.com @onpnt on twitter Please click the Mark as Answer button if a post solves your problem!