March 6, 2012 at 3:49 am
Hi All
I have a strange issue which is proving quite tricky.
I have a flat file (.csv) which contains a several columns one of the columns of which looks like this:
<text>/<text>/<text>/<text>
for instance col1, col2, col4 do contain data normally but this is mapping is straight forward.
The flat file columsn are , seperated
<col1>,<col2>,<Derby/London/Saturday/Sunday>,<col4?
What I need to do is to take the first part of col3 eg Derby and put this (map it to) its own column, I then need to go on and extract London and put this into a seperate column and so on
The results of the extraction would then go into a field in a table
<field1> Derby
<field2>London
<field3>Saturday
<field4>Sunday
The only thing consistent about the col3 will be each value is seperated by the /
I tried using a derived column as this.
SUBSTRING(FieldOne,1,(FINDSTRING(FieldOne,"/",1) - 1))
which works fine for the first value but I can't seem to find a way to get to the second and third, especially as the value of the first will be different each time.
Any help greatly appreciated.
March 6, 2012 at 8:51 am
Sounds to me like you will need to use a script component in your data flow. In the script component you can use the Split function on that specific column and then iterate through the array created by the SPLIT function to populate the additional columns in your data flow.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 7, 2012 at 1:48 am
Thanks Jack, I thought as much. I have written a t-sql function which I will call outside of SSIS which will seperate the filed first before bringing into SSIS. I kind of hoped there would be a more elegant derived column solution.
Thanks for the response.
Cheers
Paul
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply