CODEWITHSUNDEEP
sqlsql-serverssisdataflow
J.S.Orris
J.S.Orris

Reputation: 4821

Spitting Value From One Field Into Two Values to be Used in Two Different Fields in SQL Server Using SSIS Package

I have fields in a column with values like the following:

ABCDEFG:1234

I have a SSIS package that needs to split this value into two values like the following:

ABCDEFG

1234

The ABCDEFG will be imported into Field1 and 1234 into Field2

I have the following script on DerivedColumn1 process in DataFlow:

LEFT([PN IN],FINDSTRING([PN IN],":",1))

and the following script in DerivedColumn2 process in DataFlow:

REPLACE([PN IN],":","")

Which gives me the following output:

ABCDEFG

My question is this...Because I'm already using a derived column for this field, It is not letting me do another DerivedColumn process in my DataFlow to pull the RIGHT of the ":" to give me ABCDEFG...What protocol do I take to grab the RIGHT as well? Can I nest this into one script?

Upvotes: 0

Views: 155

Answers (2)

Regbac
Regbac

Reputation: 11

you can split them like that:

left column:

SUBSTRING(col1,1,FINDSTRING(col1,":",1) - 1)

right column:

SUBSTRING(col1,FINDSTRING(col1,":",1) + 1,LEN(col1) - FINDSTRING(col1,":",1))

or, if you have SSIS 2012, you can use the TOKEN function

left column: TOKEN(col1,":",1)
right column: TOKEN(col1,":",2)

Upvotes: 1

Avarkx
Avarkx

Reputation: 1065

Derived Columns

I didn't even find this LEFT function in my SSIS, but if you could elaborate on what it is not letting you do, maybe somebody can help you out...

Upvotes: 0

Related Questions