0

Trying to set up an SSIS derived column in BIDS 2008. I have a single-column input [Column 0] which contains a pipe-delimited string. I need a formula to extract one of the "fields" from the input column, however the length is variable. Here is the formula I currently have in place:

SUBSTRING( [Column 0],(FINDSTRING( [Column 0], "|",10 )+1),(FINDSTRING( [Column 0], "|",11)-FINDSTRING( [Column 0],"|",10)-1))

When I enter that formula, it turns red and says the length parameter is invalid. Trying to determine length of the data by finding the position of the next iteration of the delimiter and then subtracting from the position of the current iteration of the delimiter.

When I replace the length part of the formula with a static number the error goes away.

Can someone help me correct the formula above please? Thank you!

LegalEagle
  • 97
  • 3
  • 15
  • **This question was caused by a problem that can no longer be reproduced** or a simple typographical error. While similar questions may be on-topic here, this one was resolved in a manner unlikely to help future readers. This can often be avoided by identifying and closely inspecting the shortest program necessary to reproduce the problem before posting. – Jonny Henly Jul 14 '16 at 22:18

2 Answers2

0

Use the TOKEN function provided by SSIS to split on | and find the "field" you want.

TOKEN (SSIS Expression)

Returns a token (substring) from a string based on the specified delimiters that separate tokens in the string and the number of the token that denotes which token to be returned.

TOKEN(character_expression, delimiter_string, occurrence)
Jonny Henly
  • 4,023
  • 4
  • 26
  • 43
StackOverflowGuest
  • 1
  • This solution doesn't work for me, unfortunately. I am using BIDS 2008 and the TOKEN function wasn't implemented until the 2012 version. – LegalEagle Jul 14 '16 at 19:36
  • In that case, what you're doing should work, just be careful that you're balancing you're parentheses etc. – StackOverflowGuest Jul 14 '16 at 20:04
  • SHOULD work, but doesn't. It tells me the length parameter is invalid. I built the formula one segment at a time then copy/pasted it all together, so all the parentheses should be there. Is there any other reason you know of that it would say the length parameter would be invalid? – LegalEagle Jul 14 '16 at 20:07
  • All I know is that I was able to do as you're trying. This is what's in my expression: `SUBSTRING(myString,FINDSTRING(myString,"|",2) + 1,FINDSTRING(myString,"|",3) - FINDSTRING(myString,"|",2) - 1)` – StackOverflowGuest Jul 14 '16 at 20:22
  • I get the same error when using the formula above. It says "the length is not valid. Length must be between 0 and 4000". – LegalEagle Jul 14 '16 at 20:56
0

FOUND THE ISSUE!! The output column on my flat file data source was set to a width of 5000. I adjusted the width down to 4000 and closed then re-opened the project. It showed me an error on the flat file due to the change in metadata. When I re-saved the file and then re-entered the formula above, all worked as expected.

Thanks everyone!

LegalEagle
  • 97
  • 3
  • 15