Replace Null columns in ssis

user_17842942 0
Viewed 78

I want to add a column named CategoryCode which would take the first 3 letters of Category column + the first 3 letters of Subcategory column+ any number. The problem is that I have some values in CategoryCode column which I don't have to change. I just have to operate on values which is null. Can anyone help me with that? It would be of great help.

derived-column etl ssis sql-server
1 Answers

You can simply add a Derived Column that replaces the input CategoryCode column using the following expression:

REPLACENULL([CategoryCode],LEFT([Category],3) + LEFT([SubCategory],3) + "123")

Update 1 - Adding an auto increment number

Unfortunately, SSIS Derived Column cannot be used to add an auto increment or randomly generated number. To achieve that you need to use a Script Component transformation:

if(row.CategoryCode_IsNull == true)
    row.outCategoryCode = row.Category.Substring(0,3) + row.SubCategory.Substring(0,3) + id.toString();
else
    row.outCategoryCode = row.CategoryCode;

More detailed examples:

user_70312300
Related