SSIS Basics: Data cleansing using derived columns

Question

SSIS Basics: Data cleansing using derived columns

epivaral

SSC-Addicted

Points: 428
More actions
October 30, 2018 at 12:15 am

#372634

Comments posted to this topic are about the item SSIS Basics: Data cleansing using derived columns
MCSE, MCSA SQL Server Database Developer/Administrator

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 1

SSIS has a function named TOKEN that is much easier to utilize than SUBSTRING and FINDSTRING. It can also be utilized to break up the string based on multiple delimiters if needed.

https://docs.microsoft.com/en-us/sql/integration-services/expressions/token-ssis-expression?view=sql-server-2017

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

epivaral SSC-Addicted Points: 428 More actions · Answer 2

Jeffrey Williams 3188 - Tuesday, October 30, 2018 1:30 PM
SSIS has a function named TOKEN that is much easier to utilize than SUBSTRING and FINDSTRING. It can also be utilized to break up the string based on multiple delimiters if needed.
https://docs.microsoft.com/en-us/sql/integration-services/expressions/token-ssis-expression?view=sql-server-2017

thanks for your feedback Jeffrey, to be honest i did not know that function, I will give it a try...

MCSE, MCSA SQL Server Database Developer/Administrator

Larry Huisingh SSC Enthusiast Points: 196 More actions · Answer 3

It looks like your formula for the LastName causes the first character to be a blank. You need to start the substring one place further.
SUBSTRING(Name,FINDSTRING(Name," ",1) + 1,LEN(Name))