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, from MSFT's standpoint, at least) date object, I'm not sure how I'm going to work around this problem. Anybody have any ideas? Thanks in advance.

BTW, I did verify that this is due to the 5/1/111 date by running a test where I limited the Oracle query to that one record by adding a "Primary key = value" clause to the query. I then made that clause != and verified the rest of the records processed through properly.

so your solution was to make sure the data was clean before it came in? just for others to reference when it happens to them :)Ted Krueger Blog on lessthandot.com @onpnt on twitter Please click the Mark as Answer button if a post solves your problem!