How to extract chars between the 3rd and 4th underscores in a string variable?

Question

Hi all,

I doubt ssis supports regex, so how do I do this?

Context: filenames are date-stamped, occurring between the 3rd and 4th underscores in the name. I need that info. How to extract?

The starting index in the filename string of the 3rd underscore is NOT constant across all the files.

Thanks for suggestions,

sff

Answer 1

Hello sff,

It does support RegEx to some extend.

I have used it so far in the Script Tasks (via the .Net regex canned library), and also using a RegEx custom component.

Unfortunately, not in a ForEach Loop (only a regualr mask is accepted).

Ironically, and fortunately it looks like you do not need the Regex in your case:

Once you get the file name captured to a variable in the ForEach Loop, you can use a SSIS Epression (must incorporate the SUBSTRING function and FindString compbo). Right now, since I have no examples cannot come with the expression, but it seems doable.

Finally, if you ever fall into issues extracting the string out of the file name, then you can always use one or the other aforementioned method, you just will be operating on a variable.

---

Arthur My Blog

Answer 2

ah so i'm guessing i'll findstring to get the index location number of the underscores, and substring on those numbers to get the chars i need. makes sense - thanks!

alternative was to slam the fully qual'd filename into a db column and tsql out the part that i needed. i prefer to do it all when loading the database in the first place, when possible.

thx again!

sff