SSIS: Trim field for specific part of string

Question

My Situation:

I have an Employee field which I am getting through a SharePoint list. The current value I am getting is this:

EmployeeID;Employee Firstname Employee Lastname

Say for example:

43;Stacky Stackerflow

What I Need is either the ID alone without the ; or the First and Lastname, but I have no way of telling if the ID is gonna be 1, 2 or 3 Digits Long or in anyway tell how big the Names are gonna be. Is there a way to cut these using any of the Tools in SSIS ? If so how ? And if not, is there another way?

score 3 · Accepted Answer · edited May 23 '17 at 12:31

3

This is a Copy and paste from my answer here :Split a single column of data with comma delimiters into multiple columns in SSIS

You can use the Token expression to isolate strings delimited by well, delimiters.

TOKEN(character_expression, delimiter_string, occurrence)

so

TOKEN(EmployeeField, ";", 1)

will give you your ID

You can also set up a simple transformation script component. Use your "DATA" column as an input and add as many outputs as you need. Use the split method and you're set.

string[] myNewColumns = inputColumn.split(";");

edited May 23 '17 at 12:31

Community

1
1

answered Nov 05 '13 at 15:21

TsSkTo

1,412
13
25

Would you know how i could get the First and Lastname only, that come after the ';' ? – gberisha Nov 05 '13 at 15:40
Use a token expression with semiclolon AND a space for the delimiter string. Take a look at the msdn documentation. They show how to use multiple delimiters with token. Itsin that link in the answer – TsSkTo Nov 05 '13 at 16:13

score 0 · Answer 2 · answered Nov 05 '13 at 15:18

Because this is tagged SQL, I'll answer using that syntax:

select left(Employee, charindex(';', Employee) - 1)

If you want this as an integer, then use:

select (case when isnumeric(left(Employee, charindex(';', Employee) - 1)) = 1
             then cast(left(Employee, charindex(';', Employee) - 1) as int)
        end) as EmployeeId

score 0 · Answer 3 · answered Nov 05 '13 at 15:22

1.- If all the values in the list are separated by ";" then you just need to import the file with a ";" separator, you won't need to specify any lengths.

2.- If only this field has a ";" then you can use a Derived Column on SSIS, with a SUBSTRING SQL function, similar to this:

    select SUBSTRING('first;second', 1, CHARINDEX(';', 'first;second') - 1) AS First,
    SUBSTRING('first;second', CHARINDEX(';', 'first;second') + 1, 8000) AS SecondV

Replace the string with in the above example "first;second" with your source column name.

Hi there, thanks for the answer, but CHARINDEX isn't available in SSIS — gberisha, Nov 05 '13 at 15:31

SSIS: Trim field for specific part of string

3 Answers3