I am using SSIS 2008 R2. I have rows of data from a Source that I cannot modify (in any way). The source rows have 11 date columns and I need to add a new column which is the maximum of those 11 dates. The dates can also be NULL, in which case they need to be seen as 1 Jan 1900. I know how to do this using .Net in a script component and that it can be done as an expression in a derived column tranformation, but the expression would be massive (each value has to checked for NULL before being compared to the other 11 values). I am hoping there is a simple array type function that I am not aware of. I am trying to get some ideas as to what all the options are and then evaluate them against the expected performance and maintainability of the code. Any ideas would be much appreciatedCraig Bryden - Please mark correct answers

Craig, SQL could be the easiest, but I sense this problem is solvable with a recursion, similar to what has been described here: http://www.jasonstrate.com/2011/01/31-days-of-ssis-recursive-ssis-package-631/ IMHO, I would stick to the a derived column transformation approach, to ease with the SSIS Expression creation you can use SSIS Expression Tester: http://expressioneditor.codeplex.com/Wikipage?ProjectName=expressioneditorArthur My Blog

what is source type? sql server or any database? flat file? excel ? ...?http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

I would use a .NET script component. You can easily loop over the columns using an iterator and find the max date. A derived column would be too complex and hard to maintain.MCTS, MCITP - Please mark posts as answered where appropriate.

A more dynamic way is to : first combine all source columns into one column, you can do this with derived column transform, for example combine columns with comma. and then set this new column as input column to script transformation. then you can write .net script simpler and more dynamic, in this way, you don't need to bring column names in the script, just you should split input column by comma into string array like this; string[] ColumnArray=Row.InputColumn.Split(','); then you can loop through columnArray items and find maximum value. and fill it in an output column. advantage of this solution is that your .net code has highly maintainability, with adding new columns to source you don't need to change script. but disadvantage is that you need an extra process to combine columns first, and find maximum later. If number of rows is not very huge I think this way is good one because script doesn't change over times.http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

@Reza, you still have to manually combine all the columns in the derived column, so the maintainability there is still a nightmare. In the .NET script component, you can simply loop over the columns collection using a simple loop. You don't have to know the column names or the number of columns, you just use for example columns[i] (don't remember the right syntax).MCTS, MCITP - Please mark posts as answered where appropriate.

Check out this MSDN thread on how to loop over the columns in the script component: http://social.msdn.microsoft.com/Forums/pl-PL/sqlintegrationservices/thread/40e5e4ad-fa14-442c-b0c4-f6287cc818b6 (look for the reply of SSISJoost) Check if a column is a date, if yes, check if it is bigger than the current max value.MCTS, MCITP - Please mark posts as answered where appropriate.

@Reza, you still have to manually combine all the columns in the derived column, so the maintainability there is still a nightmare. In the .NET script component, you can simply loop over the columns collection using a simple loop. You don't have to know the column names or the number of columns, you just use for example columns[i] (don't remember the right syntax). MCTS, MCITP - Please mark posts as answered where appropriate. This is the reason of I asked about what is the source in my first post, This will be good solution if source is flat file, just need to don't set any column delimiter, and about the columns[i], I can not find any Columns property for the Row in script component transform.http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

Check out this MSDN thread on how to loop over the columns in the script component: http://social.msdn.microsoft.com/Forums/pl-PL/sqlintegrationservices/thread/40e5e4ad-fa14-442c-b0c4-f6287cc818b6 (look for the reply of SSISJoost) Check if a column is a date, if yes, check if it is bigger than the current max value. MCTS, MCITP - Please mark posts as answered where appropriate. That's OK, good thread, thanks for that. So I am agree that looping through input columns is dynamic in this way.http://www.rad.pasfu.com My Submitted sessions at sqlbits.com

and about the columns[i], I can not find any Columns property for the Row in script component transform. I know :) I confused the syntax with the columns in a dataset :) But the link I posted contains a description on how to loop over the columns using reflection (by SSISJoost).MCTS, MCITP - Please mark posts as answered where appropriate.

Looking over the suggestions by the other guys, the script is probably best. But... you could also use the Unpivot transform to change those columns into rows, then use a Derived Column to turn the NULLs into your min value, then an Aggregate transform to get the max, then an OLE DB Command (or whatever) to persist the changes. Talk to me now on

Thanks all for the suggestions. My default position was to use the .Net Script transformation. Todd's reply looks like it is probably the most "pure" SSIS solution, but I've decided to stick with the .Net Script transformation. Thanks again Craig Bryden - Please mark correct answers