Are you simply trying to get entries that have a number in them?
This should therefore work:
SELECT *
FROM [table_name]
WHERE CUSTOMER_NAME LIKE '%[0-9]%';
If you...

Does your hierarchy ever go more than 1 level? Your sample doesn't suggest so, so I haven't done more:
WITH CTE AS(
  SELECT *,
    LEFT(TS.HLabel,...

Is that your solution or? You haven't given us DDL and DLM, as Joe suggested, so the above query doesn't help us.We don't have access to your data, so running...

Using Lowell's as a start maybe:
;WITH MySampleTable([ID],[FIRST_NAME],[LAST_NAME],[START DATE],[END DATE],[status],[comment])
AS
(
SELECT '1234567','JANE','DOE','2016-09-06','2017-01-31','TERMINATED','for this line' UNION ALL
SELECT '1234567','JANE','DOE','2015-09-22','2016-09-05','ACTIVE','I''d like to get this line and display both' UNION ALL

Could you provide what your output is meant to look like. I don't really understand what you mean by "for this line". For this line you want to do what...??...

The context of the whole SP will be run as WindowsUsers1 when declaring WITH EXECUTE As. Thus there are no impersonations to REVERT.

Try this:
CREATE PROCEDURE...

How about:
SELECT COUNT(T.UID),
       DATEADD(second, CASE WHEN DATEPART(second,T.datetimestamp) < 20 THEN 0
                            WHEN DATEPART(second,T.datetimestamp) >=...

Stab in the dark, what happens if you move your DimStaff.ResourceType = '2' to your JOIN statement?

Is not, are you able to provide some DDL and DLM (check...

Instead of declaring a list of tables, use JOINS. Your SQL is malformed, and doesn't include the whole picture, so here's a starting block
SELECT C.CustomerId,
    ...

How does your SQL Server spec compared to the development PC your using? I imagine that it's probably got a lot less applications open (no email, web browser, SSMS, etc)...

Where have you got your OnError event, is it for the whole package? If so, then the event will trigger. Do you, perhaps, have some items (like your connection managers)...

Behaviour definitely seems to differ on browser, as I don't get the problem Jeff has (extra lines) in Firefox 51.0.1 (64Bit). If I copy the text I put above...