0

I have a file of strings like:

"Row 1: Location=1922&JobType=11&JobName=3719&SkillLevel:isnull=true&JobStatus=0"

"Row 2: OffenderId=1447983&Location=0&OrderBy= "

"Row 3: User=tnu11607&Agency=767&ReportYear=2015&ReportMonth=2"

There are multiple patterns depending on which of 200+ reports were run, but the parameters can be different for each report.

I'd like to parse this out into something like:

 ParamLocation  ParamOffender  ParamOrderBy  ParamUser  ...

  1. 1922 --- --- --- ...

  2. --- 1447983 NULL --- ...

  3. --- --- --- tnu11607 ...

(Hard to get the spacing to show adequately)

Etcetera.

I'm not even sure quite how to begin. Any Suggestions would be greatly appreciated. I'll be looking at this over the weekend.

Lynne Davidson
  • 183
  • 1
  • 1
  • 14

1 Answers1

0

Try digging into Regular Expressions, and in SSIS, you could use a Script Task. Drawing on answers from this SO post, you could use something like the following, and you can see it in action using something LINQPad or Visual Studio.

string queryString = "Row 1: Location=1922&JobType=11&JobName=3719&SkillLevel:isnull=true&JobStatus=0";
Dictionary<string, string> matches = Regex.Matches(queryString, "@"(\w+:?\w*)(=(\w+))&?"").Cast<Match>().ToDictionary(x => x.Groups[1].Value, x => x.Groups[3].Value);
Console.WriteLine(matches["Location"]);

To automate that by rows in SSIS, just make the queryString variable your row text.

Community
  • 1
  • 1
sorrell
  • 1,801
  • 1
  • 16
  • 27
  • Great! I've been looking for an excuse to dig into regular expressions. I'll let you know when I've had some time to explore. Thanks. – Lynne Davidson Mar 20 '15 at 20:48
  • My regex-fu is fairly weak, so I updated the answer. You can check out online tools like [this](http://regexr.com/3al7k) and [this](http://rubular.com/) to visually see what pattern the regex is indicating. – sorrell Mar 20 '15 at 21:29