2

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
ADITYA NARAYAN
  • 17
  • 4

1 Answers1

1

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:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • But I have to increase the numeric part for every record. It's like Category code would be my primary key. – ADITYA NARAYAN Jan 06 '22 at 00:29
  • What is your data source? Are you reading from SQL? @ADITYANARAYAN – Hadi Jan 06 '22 at 07:39
  • The question is like... There is one category.txt file with CategoryCode, category and subcategory column. There is another Product table with product descriptions, price, category and subcategory column. We have to merge both files on the basis of categoryCode such that Category code becomes primary key in Category table and becomes foreign key reference to Product table. – ADITYA NARAYAN Jan 06 '22 at 08:49
  • @ADITYANARAYAN you need to use a Script Component to add an incremental number. I added several examples to my answer – Hadi Jan 06 '22 at 23:32