deriving a new column from another derived column

In a Derived Column data flow transformation, why can't I refer to a derived column (added in that same transformation) in the expression for another derived column? It seems I am forced to chain 2 DC data flows, just to do something as conceptually simple as:

a = x + y; b = a2

On a related note: Can I define a variable that is scoped to each row? Can I bind a variable in an expression to avoid creating a new row, e.g.

let a = x + y; a2

as the expression for new row b?

Kevin Rodgers wrote:

In a Derived Column data flow transformation, why can't I refer to a derived column (added in that same transformation) in the expression for another derived column? It seems I am forced to chain 2 DC data flows, just to do something as conceptually simple as:

a = x + y; b = a2

Kevin,

God yeah. I so wish you could do that. Seems like such simple funcitonality doesn't it?

I have requested it here: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127010

Please please please click through and vote for it.

Kevin Rodgers wrote:

On a related note: Can I define a variable that is scoped to each row? Can I bind a variable in an expression to avoid creating a new row, e.g.

let a = x + y; a2

as the expression for new row b?

No, you can't do that!

-Jamie

|||You can't do it in derived column, but it is very easy to do in script component - the component generates the row accessors, so the amount of code you need to write is almost the same as in derived column transform.|||

I haven't used the script component before -- I haven't even written any scripts. Could you show me a simple example of generating row accessors etc. as you've suggested?

Thanks!

|||

It's a good idea Jamie.

Do keep in mind that it adds another layer of complexity. Along with some other difficulties, the user would have to specify an order of execution for the expressions, and the UI would need a way too enable and maintain that. Chaining multiplie derived columns, while not as convenient or as pretty to look at, makes it abundantly clear what order the intermediate expressions occur in and eases some usability concerns.

It's something to look at for the future, though, certainly. Keep the suggestions coming!

Thanks
Mark

|||

Kevin Rodgers wrote:

I haven't used the script component before -- I haven't even written any scripts. Could you show me a simple example of generating row accessors etc. as you've suggested?

Thanks!

Just add a script transform where you would use Derived Column transform, select Transform in the first dialog. Check the columns you want to use in your expressions. Go to Inputs and Outputs tab, add output columns to Output 0.

Now edit the script, and type your code inside Input0_ProcessInputRow function. I quickly setup a "validation" for AdwentureWorks DB (the transform adds two colums CalculatedColumn and TotalOK to each row):

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Dim t As Decimal
t = Row.OrderQty * Row.UnitPrice * (1 - Row.UnitPriceDiscount)
Row.CalculatedColumn = t

If (t = Row.LineTotal) Then
Row.TotalOK = True
Else
Row.TotalOK = False
End If

End Sub

So the main difference in expressions is that you have to add "Row." prefix to column name :) Of course, the syntax is different, as the script transform uses full VB.NET language, which gives you ability to create temporary variables among other things.

sql

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

Derived column in SQL Server 2005 SSIS

Hi,

I am trying to upload a csv file into a destination SQL Server table using Data FLow objects in MS SQL Server 2005 SSIS. My destination table X has a date column while my source data file (for which i have a flat file connection) does not have a date column. I created a Derived column for date using the system date function in my Source data object and ran the package but it returned errors. I changed the data type of the derived column but I still get errors.

is there any other way i can get the date on the fly i.e generate a date for the incoming source file and map that (insert into) to the destination table ?

Thanks.

Post your errors here and we can then better help you. Also ensure that you have the correct date types flowing into the given date column.|||

Devived column name - Name for your date field

Devived column - should be <add as new column>

The expression columns should be GETDATE()

On the advanced Editor - Make sure to select the date name to be used in the output columns.

this should work for you.

Hammer.

Derived Column from a Condition statement

I've found that there is no such thing as a Case Statement in the Derived Column task in the data flow objects. I've written a ternary statement instead. I can't seem to get it to work exactly how I want it to. For example

AccountCategory == "E" ? 1 : 2

Works fine but the following doesn't

AccountCategory == "E" ? CreditAmount : DebitAmount

The CreditAmount and DebitAmount Fields are spelled correctly, the field type of AccountCategory is String and the Field type of CreditAmount and DebitAmount is numeric, but that seems to be the same situation that I have in the first example that works. Am I missing something? I'd appreciate any advice anyone has to offer. Thanks,

Bill

Only thing you did not mention is what is the data type set for the derived column itself, it should be numeric to match the Credit and Debit columns. The scale and precison must match for all three too.

That is all just guess work, and the syntax looks fine, but what would really help is the actual error message you get.

|||It looks like I stumbled on to how to fix this, I don't know if it is a bug or it is designed to work this way. If I copied the second statement and then deleted the whole row for that particular derived column then copied the statement back into the expression column before naming the column it works. Go figure.|||

Glad to hear you have it working.

Most likely, as Darren suggests, it was a datatype mismatch. However, if you could repro and report the error message, we could give you better advice.

Donald

Derive columns from cube member?

I have a cube that I read in a data flow section using OLE DB. To get this to work I had to do an ad hoc query through a sql server database ( this is problem that is described in post 219068).

So I now have the data and I want to derive new columns based on that data using an if statement:

if ( ISNULL(" [Facility].[REGION].[NATCODE].[MEMBER_CAPTION] " ) comptype = "NAT" else comptype = "REG"

comptype is the new derived column I am creating.

But when I try to save this I get an error: The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a requred element such as parenthesis

I selected the cube element from the columns list in derived element, when it initially put it in it looked like:

[[Facility].[REGION].[NATCODE].[MEMBER_CAPTION] ]

and gave error:

The token "[" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

Below is the statement I use to get data back from AS

select * from openrowset('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;',
'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name
SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Case Count],
[Measures].[A100 Bathing],[Measures].[A100 Bed Chair Wheelchair],
[Measures].[A100 Bladder],[Measures].[A100 Bowel],
[Measures].[A100 Dressing Lower],[Measures].[A100 Dressing Upper],
[Measures].[A100 Eating],[Measures].[A100 Grooming],
[Measures].[A100 Stairs],[Measures].[A100 Toilet],
[Measures].[A100 Toileting],[Measures].[A100 Tub Shower],
[Measures].[A100 Walk Wheelchair],[Measures].[A200 Comprehension],
[Measures].[A200 Expression],[Measures].[A200 Interaction],
[Measures].[A200 Memory],[Measures].[A200 Problem Solving],
[Measures].[D100 Bathing],[Measures].[D100 Bed Chair Wheelchair],
[Measures].[D100 Bladder],[Measures].[D100 Bowel],
[Measures].[D100 Dressing Lower],[Measures].[D100 Dressing Upper],
[Measures].[D100 Eating],[Measures].[D100 Grooming],
[Measures].[D100 Stairs],[Measures].[D100 Toilet],
[Measures].[D100 Toileting],[Measures].[D100 Tub Shower],
[Measures].[D100 Walk Wheelchair],[Measures].[D200 Comprehension],
[Measures].[D200 Expression],[Measures].[D200 Interaction],
[Measures].[D200 Memory],[Measures].[D200 Problem Solving] } ON COLUMNS,
({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} *
[RIC].[CMGGRPCD].ALLMEMBERS ) ON ROWS FROM [CMG Demographics]
WHERE [Calendar].[Quarter 2 (2007)]')

So can some one help me on how I can do this derived colum.

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

To get <fieldname> expand the 'columns' folder in the derived column transformation editor.

|||Use a derived column to create new columns.

New Column Name: comptype
Expression: ISNULL([InputColumn]) ? "NAT" : "REG"|||

Anthony Martin wrote:

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

Anthony, just simplify that statement. ISNULL() returns a boolean value, so there's no need to doubly check for a true status or not. Just drop the "== true" part.|||

Thanks, good point. Just a habit I guess.

|||

I have figured out what I had to do, but I do not know why I had to do this way, but first while I saw after I posted that used used bad assignment of derived column comptype = <value>. that was one of my many different attempts around the question I posted about.

1st) I could never get the if else statment to work in derived field, I had to use conditional if (? logic for my ifs

2nd) I could not referenct the the MDX columns in my conditional if. It did not like all of the '[' and ']', so I had to convert all of the columns from MDX column name to a more standard sql column name, basically renamed column to not have '[', ']' in them

Does any one know why this does not work neatly, MDX is supposed to have been integrated with SSIS, but I had to do multiple tricks to get the data, right from start where I had to use ad hoc query though a sql database to execute the MDX.

This also results in fact that during selection of MDX it did not maintain any of the data types and converted everything to chararacter field of size 4000, which gave me trunctaion warnings in the selection of MDX into SSIS

Derive columns from cube member?

I have a cube that I read in a data flow section using OLE DB. To get this to work I had to do an ad hoc query through a sql server database ( this is problem that is described in post 219068).

So I now have the data and I want to derive new columns based on that data using an if statement:

if ( ISNULL(" [Facility].[REGION].[NATCODE].[MEMBER_CAPTION] " ) comptype = "NAT" else comptype = "REG"

comptype is the new derived column I am creating.

But when I try to save this I get an error: The expression might contain an invalid token, an incomplete token, or an invalid element. It might not be well-formed, or might be missing part of a requred element such as parenthesis

I selected the cube element from the columns list in derived element, when it initially put it in it looked like:

[[Facility].[REGION].[NATCODE].[MEMBER_CAPTION] ]

and gave error:

The token "[" was not recognized. The expression cannot be parsed because it contains invalid elements at the location specified.

Below is the statement I use to get data back from AS

select * from openrowset('MSOLAP', 'DATASOURCE=local; Initial Catalog=Patient Demographics 2005;',
'with member [Measures].[TimeDisplayName] as [Calendar].CurrentMember.Name
SELECT NON EMPTY { [Measures].[TimeDisplayName],[Measures].[Case Count],
[Measures].[A100 Bathing],[Measures].[A100 Bed Chair Wheelchair],
[Measures].[A100 Bladder],[Measures].[A100 Bowel],
[Measures].[A100 Dressing Lower],[Measures].[A100 Dressing Upper],
[Measures].[A100 Eating],[Measures].[A100 Grooming],
[Measures].[A100 Stairs],[Measures].[A100 Toilet],
[Measures].[A100 Toileting],[Measures].[A100 Tub Shower],
[Measures].[A100 Walk Wheelchair],[Measures].[A200 Comprehension],
[Measures].[A200 Expression],[Measures].[A200 Interaction],
[Measures].[A200 Memory],[Measures].[A200 Problem Solving],
[Measures].[D100 Bathing],[Measures].[D100 Bed Chair Wheelchair],
[Measures].[D100 Bladder],[Measures].[D100 Bowel],
[Measures].[D100 Dressing Lower],[Measures].[D100 Dressing Upper],
[Measures].[D100 Eating],[Measures].[D100 Grooming],
[Measures].[D100 Stairs],[Measures].[D100 Toilet],
[Measures].[D100 Toileting],[Measures].[D100 Tub Shower],
[Measures].[D100 Walk Wheelchair],[Measures].[D200 Comprehension],
[Measures].[D200 Expression],[Measures].[D200 Interaction],
[Measures].[D200 Memory],[Measures].[D200 Problem Solving] } ON COLUMNS,
({[Facility].[REGION].[NATCODE], [Facility].[REGION].[REGCODE]} *
[RIC].[CMGGRPCD].ALLMEMBERS ) ON ROWS FROM [CMG Demographics]
WHERE [Calendar].[Quarter 2 (2007)]')

So can some one help me on how I can do this derived colum.

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

To get <fieldname> expand the 'columns' folder in the derived column transformation editor.

|||Use a derived column to create new columns.

New Column Name: comptype
Expression: ISNULL([InputColumn]) ? "NAT" : "REG"|||

Anthony Martin wrote:

Try this...

isnull(<fieldname>) == true ? "NAT" : "REG"

Anthony, just simplify that statement. ISNULL() returns a boolean value, so there's no need to doubly check for a true status or not. Just drop the "== true" part.|||

Thanks, good point. Just a habit I guess.

|||

I have figured out what I had to do, but I do not know why I had to do this way, but first while I saw after I posted that used used bad assignment of derived column comptype = <value>. that was one of my many different attempts around the question I posted about.

1st) I could never get the if else statment to work in derived field, I had to use conditional if (? logic for my ifs

2nd) I could not referenct the the MDX columns in my conditional if. It did not like all of the '[' and ']', so I had to convert all of the columns from MDX column name to a more standard sql column name, basically renamed column to not have '[', ']' in them

Does any one know why this does not work neatly, MDX is supposed to have been integrated with SSIS, but I had to do multiple tricks to get the data, right from start where I had to use ad hoc query though a sql database to execute the MDX.

This also results in fact that during selection of MDX it did not maintain any of the data types and converted everything to chararacter field of size 4000, which gave me trunctaion warnings in the selection of MDX into SSIS