Derived Column Task failing with error 0xC0049067

I have a package that fails as soon as it hits the first Data Flow that contains a Derived Column task. The task takes three date columns and looks for a date of 6/6/2079. If it is there, it is replaced with a NULL. This task worked fine until I installed the Non-CTP version of SQL 2005 SP1, earlier today. (I went from RTM 9.0.1399 to SP1 9.0.2047)Does anyone have any ideas?

Here is the error I am trapping:

An error occurred while evaluating the function.

The "component "Update Max Date Value to NULL" (346)" failed because error code 0xC0049067 occurred, and the error row disposition on "input column "WeekEndingDate" (455)" specifies failure on error. An error occurred on the specified object of the specified component.

The ProcessInput method on component "Update Max Date Value to NULL" (346) 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.

Thread "WorkThread0" has exited with error code 0xC0209029.

I tried to configure the error output to ignore the failure. The task succeeded. It did not insert anything, however, for the three columns in question. It then failed on the next Data Flow that contained a Derived Column task. I tried to pre-populate the columns with a default date, but no go. Any help would be appreciated.

Nothing wrong with the concept and I can do a similar thing in SP1. What is your expression and column types?|||

The date columns come out of an Access database. I convert them to a string in a Data Conversion task and then validate that they are valid dates in a Script Component task. If they are invalid, I assign them a date of 6/6/2079. When they exit the Script task they are converted back to a date. The Derived Column task is next and it looks for the year 2079 to convert it to a NULL. Here is an example of one of the expressions:

YEAR([Check for Valid Dates].LastWorkedDate) == 2079 ? NULL(DT_DBTIMESTAMP) : [Check for Valid Dates].LastWorkedDate

This is where it fails. One thing I failed to mention before is that if I execute the task by itself it works properly. However, if I run the entire package (100+ tasks) it fails as soon as it hits the first task that contains a Derived Column task.

|||Turns out it is not the first Data Flow with a Derived Column task that is failing. I have one about 8 steps prior that runs just fine. I am wondering if it has something to do with the expression language. I saw a post where someone got the same error if they tried to use an expression language date function with a date value outside of SQL range (1/1/1753 to 12/31/9999). This post however was pre-SP1. I wonder whether that issue was addressed and if so how. Any suggestions from anyone would be greatly appreciated. I have been stuck on this for a couple of days and I really need to move on. I am dreading the thought of uninstalling\reinstalling SQL2000 and 2005 on my machine but will if I have to. Please help!!!!|||I uninstalled and reinstalled SQL 2005 and then attempted to run my package and it worked. There must be something that changed about the way it evaluates expressions in Derived Column tasks or maybe it is the expression language itself. Either way I am going to have to move on without SP1 until residual issues like these are dealt with. I am going to submit a bug to Microsoft about it. If anyone comes up with anything, please post a response.|||

I have been unable to reproduce this either on SQL Server 2005 RTM or SP1.

Could you perhaps hook up the error output of the derived column and redirect error rows, and then see what value(s) of LastWorkedDate it's failing on?

Thanks,
mark

|||I apologize for taking so long to get back to you. Unfortunately, I have already reinstalled SQL to RTM -SP1. I lost two days trying to troubleshoot the problem and had to give up for now. As soon as I have a chance I will reinstall SP1 and try again. For now however I have to live without it. Thank you very much for trying to reproduce the error. I will post more about this once I have the time.|||

DatabaseOgre wrote:

I saw a post where someone got the same error if they tried to use an expression language date function with a date value outside of SQL range (1/1/1753 to 12/31/9999). This post however was pre-SP1. I wonder whether that issue was addressed and if so how.

I suspect that what you suggested is indeed the problem. YEAR will fail if the date is outside of the SQL range.

If you have a chance to try redirecting rows to see if the values are indeed outside of that range, that would help us be sure that we have figured this one out. In the meantime, I will update the bug with this information.

|||

Sorry. I went back to the post and reade it a little slower this time. The post refers specifically to the DATEPART function with the YEAR argument. Here is a quote:

Can someone confirm this for me? The expression language in SSIS has the same limitations on date ranges as Sql Server? That limitation is that valid date ranges are from Jan 1, 1753 to Dec 31, 9999.

When ever I try to do a date function (DATEPART, for example) in a Derived Column Transformation on a date less than 1/1/1753, I get an error. I initially discovered this when bringing data over from Oracle to Sql Server. Just as a test, I created a text file filled with various dates and tried to import it. Whenever a date is less than 1/1/1753, it blows up.

For example, this expression code - DATEPART("YEAR",Date) will yield this error - [Derived Column [24]] Error: The "component "Derived Column" (24)" failed because error code 0xC0049067 occurred, and the error row disposition on "output column "YEAR" (80)" specifies failure on error. An error occurred on the specified object of the specified component.

As a workaround, I've been using a Script Component to do date checking, but this is obviously not ideal.

Jamie Thomson verified it for him and it was submitted as a bug for not being able to handle dates outside of the T-SQL date range. I checked my data to look for any dates that were outside of the range (pre-package) and did not find any. I also tried handling any such dates inside of a Script Task (as a just in case) which ran prior to the Derived Column Task and still received the error. When I get some time I will take a look at my source data again and see if there is anything there. I doubt it however because I am using the same package and same data source with SQL2005 RTM and I am not getting any errors. I probably wouldn't change much about the bug I submitted just yet.

|||

The value being out of range was my best guess for the cause of the error. If you know the values are all in range, then something else is wrong.

The best thing now, I think, would be for you to try redirecting the error rows and see what values are causing the problem. Once we know that, I will be able to try to reproduce the problem.

Thanks!
Mark