Split column information based on keywords in SSIS

Question

Hello Team,

I have come across a requirement where an existing information(text field) need to split into 3 columns based on the keywords mentioned in the sentence.

See the screenshot below.

The ask is to split the whole sentences into multiple parts as mentioned below.

The learner will be able to = first set of data

Delivery Guidance = either 'Delivery Guidance' or 'Delivery to include'

Assessment Criteria = 'Assessment Criteria'

Assessment Specification = 'Assessment Specification'

The Environment is : MS Dynamics

Tool for updation is SSIS.

Kindly assist.

Thanks in advance

Prashant Pandey.

Answer 1

Hi @PANDEY Prashant,

Assuming that my assumptions in the comments are correct...

In my tests I created a variable User::Tokens with the following value:

"Leading Paragraph.

Assessment Criteria some text.

Assessment Specification description text."

TOKEN(@[User::Tokens], ".", TOKENCOUNT( @[User::Tokens] ,".")) will give you the following: "Assessment Specification description text"

TOKEN(@[User::Tokens], ".", TOKENCOUNT( @[User::Tokens] ,".") - 1) will give you the following: " Assessment Criteria some text"

You would need to use similar expressions in the Derived Column Transformation in Data Flow Task.

Answer 2

Hi @PANDEY Prashant,

Like Yitzhak said that you may use token function in SSIS to do that.

References

• TOKEN (SSIS Expression)
• TOKENCOUNT (SSIS Expression)
• FINDSTRING (SSIS Expression)

Regards，

Zoe Hui

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

I have come across a requirement where an existing information(text field) need to split into 3 columns based on the keywords mentioned in the sentence.

SSIS /any ETL isn't made for such requirements.

You may can solve it with a SSIS Script task (.NET), but that means some more of programming.