hi, I am tring to add two column values seperated by comma only if second field is not null as shown below using Derived column transformation in SSIS (dt_wstr,500)((isnull(col1)? " " : col1)+ "," + (isnull(col2)? " " : col2)) eg. col1 col2 columbus OH houston CA Expected results : columbus,OH houston CA
I would recommend doing this in two steps, using two Derived Column tasks. In the first task, do your null-checking and replace the col1 and col2 values like so: ISNULL(col1) ? "" : col1 ISNULL(col2) ? "" : col2 Then, in your second derived column task, you can have a new column with the following expression: col1 + (LEN(col1) > 0 && LEN(col2) > 0 ? ", " : "") + col2 That way, your process will only put a comma in if there is a legitimate (non-empty) value for both of your fields. If there is a legitimate value only for one of the two columns, no comma will appear. Just as a warning, if you run LEN() on a NULL value, your derived column will be null. You could always put an ISNULL check inside the LEN(), but my personal preference is to get rid of nulls as early in the process as possible.
Expressions in SSIS can be are real pain, so I try to simplify them by using conditional splits. I just think it is easier to understand what is going on, and the overhead is minimal as conditional splits are very fast. In your data flow, setup your data source, then add a conditional split as per below, ![alt text] then add 4 derived columns for each output calling each result, and all the same data type bothNULL output expression = NULL(DT_STR,50,1252) (Or whatever datatype you use) col1NULL output expression = col2 col2NULL output expression = col1 neitherNULL output expression = col1 + "," + col2 Then after your derived columns add a Union All transform and connect all the derived columns, then do what you need with the data. The final output should look something like this; ![alt text] : /upfiles/conSplit.bmp : /upfiles/totalPackage.bmp
If you prefer to keep it in one statement as a derived column: (dt_wstr,500)((isnull(col1)? "" : col1) + ((!isnull(col1) && !isnull(col2)) ? "," : "") + (isnull(col2)? "" : col2)) There's only one situation when the comma is needed: when both col1 and col2 have got a value.