I have a working solution however maintenance is frought with issues as every time I change a value I need to update many calls with the same change.

I make an SQL call and return one comma seperated string: SM,SEP,18,6,2021-09-16,2021-09-30,2021-09-30,3,273,Thu,272,2021-09-28,Tue,2021-08-28,2021240,2022,2021-09-15,2021-10-15,1900-01-01,,1900-01-01

I next go through and assign each of these values to a User Variable, in this case 21 variables and 21 calls

This regex works in the Set User Variable:
REGEX(MatchGetGroup|SM,SEP,18,6,2021-09-16,2021-09-30,2021-09-30,3,273,Thu,272,2021-09-28,Tue,2021-08-28,2021240,2022,2021-09-15,2021-10-15,1900-01-01,,1900-01-01|(.*),(.*),(.*),(.*),(.*),(.*),(.*),(.*),(.*),(.*),(.*),(.*),(.*),(.*),(.*),(.*),(.*),(.*),(.*),(.*),(.*)|7)}
Result: Variable 'BW_PayCheckQtr' was updated to: 3

As you notice there are 21 groups, each represented by the (.*) and each has to have all the groups listed or it does not work correctly.

This regex also works to do the same thing but more simplified: (?:[^\,]*\,){7}(.*?)\, I also don't need to go back and change the previous set user variables when I add a new value to the string, it can be just replicated and the index (7) adjusted for each call. Much easier to maintain.

The new regex can be verified here: https://regex101.com/r/0DpMBK/1 

Most of my attempts to get this to run, I receive the whole regex in the output. However this one is the closest I can get but it returns the beginning of the whole string to the end of element 7: Variable 'BW_PayCheckQtr' was updated to: SM,SEP,18,6,2021-09-16,2021-09-30,2021-09-30,3,
{REGEX(Match|{TASK(cf8a79b6-9d6d-486d-9ad6-71b065353eeb|StdOut)}|(?:[^\,]*\,){7}(.*?)\,}

Any help would be appreciated.

Bear with me a moment and consider a different approach. Using a regex to parse the string, as you are finding out, gets fugly real fast. It will be a maintenance nightmare for the person that has to maintain this behind you.

What if, instead of constructing SQL that returns a comma delimited string, you construct SQL that returns a JSON string, then use JSON functions to extract what you need by name? No funky regex to edit 21 times, no offset counting, etc.

In Oracle, I'd construct my query like this to output a JSON string. Note instead of 'val#' you would put meaningful variable names and instead of 'col_#' you would use column names from your table. Of course you'd have to see what the syntax would be to output JSON in your RDBMS.


So your SQL task Output would look like this (name:value pairs instead of a delimited list) and would only have to be run once to get all values from the database:


Then to assign values to user variables, use the function "JSON filer with JSONPath". The source would be STDOUT of the SQL query, and just name the value you want to retrieve from the JSON string. Put this in the "value" box of your "set job variable" task. Of course change the task StdOut directID to that of your own task. Note order doesn't matter.


Check out the JSON filter task type as well which would be useful while developing your job.

Let us know if you give this a try.

Thanks for the suggestion it worked very well.
To flesh out this response I'll add the tweaks needed for SQL Server or TSQL:


WITHOUT_ARRAY_WRAPPER is required as SQL Server will put square brackets around the result.
INCLUDE_NULL_VALUES is optional but I needed it as I do validate these and a return of "Nothing was returned" would require me to rewrite all my conditions.

Thanks for your time and I hope this is a one stop answer for anyone following up on a question like this.

Excellent, I'm glad this worked out for you. It sure will be easier to maintain in the future. I love a good regex solution, but your situation is better suited to this solution I believe.