SSIS 2008 - Need help with expression in derived column.

Question

SSIS 2008 - Need help with expression in derived column.

p.shabbir

SSCrazy

Points: 2404
More actions
October 22, 2014 at 4:28 pm

#291199

Hi All,
Can anybody help me on this.
I need to extract the part of the string from whole string.
for example
I have data like below and I have to extract only 1st Part (ABCDE) ,2nd part (ABCDF) and 3rd part(BCDEF)
ABCDE|ABCDF|BCDEF
BCDEF|ABCDF|BCDEF
ABCDEF|ABCDF|BCDEF
ABCDE|ABCDFE|BCDEF
I am writing below expression in Derived column
SUBSTRING(CDATA_6,0,FINDSTRING(cdata_6,'|',1)) but getting error like "The start index value must be an integer greater than 0"
If I write like below will not get desired output.
SUBSTRING(CDATA_6,1,FINDSTRING(cdata_6,'|',1))
Any help on this please!
Thanks.

Viewing 15 posts - 1 through 15 (of 16 total)

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

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 1

SUBSTRING(CDATA_6,1,FINDSTRING(cdata_6,'|',1)-1)

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

p.shabbir SSCrazy Points: 2404 More actions · Answer 2

Thanks for the reply Koen.

Can you let me know how to get 2nd and 3rd string?

ABCDE|ABCDF|BCDEF

result 2nd string

----------------

ABCDF

result 3rd string

----------------

BCDEF

Thanks for your help.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

You're looking for a splitter function. Try this search for a start.

John

Edit: oops - I just realised that you were asking for an expression in SSIS to do this. You may be able to use a script component, or it may be easier just to bring the data into a staging table and use the splitter function there.

Phil Parkin SSC Guru Points: 246978 More actions · Answer 4

Are there always three items?

Are the columns always the same length?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

p.shabbir SSCrazy Points: 2404 More actions · Answer 5

Thanks for the reply phil.

Yes, always there will be 3 items and column length is not fixed.

It may be varied for example

ABCDE|ABCDF|BCDEF

BCDEF|ABCDF|BCDEF

ABCDEF|ABCDF|BCDEF

ABCDE|ABCDFE|BCDEF

Thanks

Phil Parkin SSC Guru Points: 246978 More actions · Answer 6

p.shabbir (10/23/2014)
Thanks for the reply phil.
Yes, always there will be 3 items and column length is not fixed.
It may be varied for example
ABCDE|ABCDF|BCDEF
BCDEF|ABCDF|BCDEF
ABCDEF|ABCDF|BCDEF
ABCDE|ABCDFE|BCDEF
Thanks

Is the data coming from a flat file?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

p.shabbir SSCrazy Points: 2404 More actions · Answer 7

p.shabbir

SSCrazy

Points: 2404

October 23, 2014 at 9:58 am

#1754405

Yes, data coming from flat file.

Phil Parkin SSC Guru Points: 246978 More actions · Answer 8

Configure your flat file source connection with | as the column delimiter and let SSIS do the work for you. See here[/url] for an example.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 9

Phil Parkin (10/23/2014)
Configure your flat file source connection with | as the column delimiter and let SSIS do the work for you. See here[/url] for an example.

Or just create a very simple script component that uses the .NET split function.

Very easy to work with and much easier code to write.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Phil Parkin SSC Guru Points: 246978 More actions · Answer 10

Koen Verbeeck (10/23/2014)
Phil Parkin (10/23/2014)
Configure your flat file source connection with | as the column delimiter and let SSIS do the work for you. See here[/url] for an example.
Or just create a very simple script component that uses the .NET split function.
Very easy to work with and much easier code to write.

Easier than 'none'? How so?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

Koen Verbeeck SSC Guru Points: 259115 More actions · Answer 11

Phil Parkin (10/23/2014)
Koen Verbeeck (10/23/2014)
Phil Parkin (10/23/2014)
Configure your flat file source connection with | as the column delimiter and let SSIS do the work for you. See here[/url] for an example.
Or just create a very simple script component that uses the .NET split function.
Very easy to work with and much easier code to write.
Easier than 'none'? How so?

OK yeah when it is the only delimiter of course not 😀

I was referring to the case where you have a column with different delimiters that needs to be splitted as well.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

cafescott SSCarpal Tunnel Points: 4724 More actions · Answer 12

I've said this before, so hopefully I don't come off as a broken record. However, I would never use derived columns in SSIS. Instead, I would use SQL. One advantage of this approach is I can see the data as I test. The other advantage is I am keeping my business logic in the same place as whatever is the source of the rest of the query.

Phil Parkin SSC Guru Points: 246978 More actions · Answer 13

cafescott (10/24/2014)
I've said this before, so hopefully I don't come off as a broken record. However, I would never use derived columns in SSIS. Instead, I would use SQL. One advantage of this approach is I can see the data as I test. The other advantage is I am keeping my business logic in the same place as whatever is the source of the rest of the query.

Try doing that when transferring data from a flat file to an Excel spreadsheet 🙂

You trying to change SSIS into just an EL tool?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

cafescott SSCarpal Tunnel Points: 4724 More actions · Answer 14

Well, if you keep all your querying logic in one place rather than splitting some of it off in SSIS, it makes it easier to find it later on. Then again, using derived columns does introduce some measure of job security, so maybe it isn't that bad of an approach. 😛