Split column based on keyword in SSIS and delete from main colum

Question

Hello Experts,

In continuation of earlier raised question.

https://learn.microsoft.com/en-us/answers/questions/1320469/split-column-information-based-on-keywords-in-ssis

With the help of Token and TokenCount I am able to split information from ColumnA(having long sentence) and updating to ColumnB/ColumnC/ColumnD.

Kindly help to update Column A as well once the information is copied to ColumnB/C & D(means whatever sentences are copied to Column B/C/D should be deleted from Column A).

Thanks for understanding.

Prashant Pandey

Prashant Pandey.

Answer 1

Hi @PANDEY Prashant,

Please try the following expression:

LEFT(@[User::Tokens], FINDSTRING(@[User::Tokens] + "Assessment Criteria", "Assessment Criteria", 1) - 1)

Answer 2

Since you didn't provide enough information I am assuming the following :

Create a new derived column (e.g., NewColumnA) that contains the updated value of ColumnA after removing the sentences copied to ColumnB/C/D.

In the expression, you might need to manipulate the string using functions like REPLACE, SUBSTRING, etc., depending on the exact logic you need to remove the sentences.

Then, replace ColumnA with the new derived column you've created.

Or use a script task where you can write custom code to manipulate ColumnA. You may use C# to define exactly how you want to remove the sentences that were copied to ColumnB/C/D and update ColumnA with the modified value.