October 22, 2014 at 4:28 pm
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.
October 23, 2014 at 12:47 am
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
October 23, 2014 at 8:09 am
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.
October 23, 2014 at 8:20 am
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.
October 23, 2014 at 8:31 am
Are there always three items?
Are the columns always the same length?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 23, 2014 at 8:49 am
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
October 23, 2014 at 9:45 am
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 23, 2014 at 9:58 am
Yes, data coming from flat file.
October 23, 2014 at 11:45 am
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 23, 2014 at 12:19 pm
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
October 23, 2014 at 12:26 pm
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 23, 2014 at 1:00 pm
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
October 24, 2014 at 6:54 am
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.
October 24, 2014 at 6:58 am
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?
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
October 24, 2014 at 7:06 am
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. 😛
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply