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

Dependencies not correct with temporary tables --> replication is failing

Hello all,

here is a stored procedure I have:

CREATE PROCEDURE spU_GUI_AppliqueConditionFinancementPourGuichet
(
@.GuichetId int,
@.Validateur nvarchar(40)
)
AS
CREATE TABLE #tReservations (ReservationId int)

IF (dbo.GetSiGuichetEnRegle(@.GuichetId) = 0)
INSERT #tReservations
EXECUTE spU_GUI_AppliquePerteFinancement @.GuichetID, @.Validateur
ELSE
INSERT #tReservations
EXECUTE spU_GUI_AppliquePerteAgrement @.GuichetID, @.Validateur

SELECT GR.Id,
dbo.FormateNoms(GR.Name) AS Names
FROM #tReservations
LEFT JOIN AnotherTable GR ON GR.Id = AnotherTable.id
DROP TABLE #tReservations
GO

The creation is ok but when I look to the dependencies, I see that it depends on GetSiGuichetEnRegle only.

For me, it shall also depend on

AnotherTable

spU_GUI_AppliquePerteFinancement

spU_GUI_AppliquePerteAgrement

FormateNoms

Apparently the dependencies are not calculated correctly because I'm using a temporary table.

My problem is that I have updated this stored procedures (and the two other that I call) to add a new parameter. As a consequence, when I do a replication, this is failing saying that I have an extra parameter. I imagine that because my dependencies are not correct, the replication is not occuring in the correct order and so it's still using the old definition of the stored procedure.

Do you have any idea on how I can force the dependencies to be calculated correctly ?

Thanks

Did the error happen when you are applying a snapshot or when the distribution agent is applying a DDL change for the stored procedure update? If this happened while the snapshot was being delivered to the subscriber, I have the following questions for you:

1) Are you using 'drop' as the pre-creation command for the stored procedure articles? If so, you should theoretically not be seeing the error that you saw when the snapshot was delivered to the subscriber since the referenced procedures would either have been drop (or recreated with the new definition).

2) Are the referenced stored procedures (spU_GUI_AppliquePerteFinancement and spU_GUI_AppliquePerteAgrement) included in the publication as well? (It never hurts to ask the obvious.)

3) It would be great if you can post history messages of the distribution agent when the snapshot was applied, I just want to see the relative order of how the objects that you mentioned are applied. I will also be interested to see the history messages from the snapshot agent.

There is an undocumented -EnableStoredProcedureDependenciesReevaluation 1 switch in the snapshot agent which forces the snapshot agent to compute dependency ordering for stored procedure articles in a more accurate manner but I have a feeling that the real problem is something else.

-Raymond

|||

Hello Raymond,

Indeed there seems to be problems with the dependencies between stored procedures and tables, but the main issue here seems to be to be related to the latest problem we spoke together

After isolating the problem, I've found that the stored procedure that could not be replicated contains a "INSERT INTO" statement.

I will try a work-around by playing with post-replication scripts, while waiting for SP2 ;-)

Anyway, I will investigate the solutions you give for the dependencies

Pierre-Emmanuel