What?
Some methods of removing trailing spaces, tabs, carriage returns and line feeds (new lines).

How?
First in Transact-SQL: 
SELECT 
     RTRIM( REPLACE( REPLACE( REPLACE( @myString, CHAR(9), ''), CHAR(10), ''), CHAR(13), '') )

-- CHAR(9) = Tab
-- CHAR(10) = Line Feed
-- CHAR(13) = Carriage Return

And in SSIS: 
TRIM( LOWER( REPLACE( REPLACE( REPLACE( myString, "\x0009", "" ), "\x000A", "" ), "\x000D", "" ) ) ) 

-- \x0009 = Tab
-- \x000A = Line Feed
-- \x000D = Carriage Return

-- or split for clarity
TRIM(
	LOWER(
		REPLACE(
			REPLACE(
				REPLACE(
					myString, 
					"\x0009", 
					""
				), 
				"\x000A", 
				""
			), 
			"\x000D", 
			""
		)
	)
)


Using it to compare to a value?
There are cases in T-SQL where the trim is unecessary and a similar equation can be written: 
Table: DMExtractEmployeeTable
ID     ForeName      SurName
---------------------------------
1      Joel          Lipman
2      JOE           THE GREATEST



DECLARE @EmployeeStr = 'JOE ' + CHAR(9) + CHAR(10) + CHAR(13)


-- Before
SELECT COUNT(*) FROM Employees WHERE Employee.ForeName = 'JOE'
-- yields 1


-- What we want (equivalent?)
SELECT COUNT(*) FROM Employees WHERE Employee.ForeName = @EmployeeStr
-- yields 0


-- After (replacing the = with LIKE)
SELECT COUNT(*) FROM Employees WHERE Employee.ForeName LIKE @EmployeeStr
-- yields 1