Parse JSON string using C# script in SSIS

Question

I am working on retrieving USD -> GBP exchange rates from CurrencyLayer using a C# script task in SSIS. I have used the following code:

string url = Dts.Variables["User::CurrencyLayerURL"].Value.ToString();
WebClient wc = new WebClient();
var jsonString = wc.DownloadString(url);

To successfully retrieve the following JSON string:

{  
   "success":true,
   "terms":"https:\/\/currencylayer.com\/terms",
   "privacy":"https:\/\/currencylayer.com\/privacy",
   "historical":true,
   "date":"2015-11-28",
   "timestamp":1448755199,
   "source":"USD",
   "quotes":{  
      "USDGBP":0.66527
   }
}

However, I am not sure at this point how to retrieve just the 0.66527 value that corresponds to the "USDGBP" rate and pass it to a variable. I have seen a number of suggestions to use the JSON.net library, but I am not able to add any third-party libraries to this project. Any help would be appreciated.

You could keep it simple and just use string parsing to get it out. When your package is mostly C# code you might want to reconsider whether it should be a package. — Nick.Mc, Nov 29 '15 at 22:45

swestner · Accepted Answer · 2015-11-29T22:32:19.313

4

You can use the JsonValue class from System.Json Namespace.

JsonValue value = JsonValue.Parse(jsonString);
var quote =  (string)result["quotes"]["USDGBP"];

Or you could use the JavaScriptSerializer from System.Web.Script.Serialization

var serializer = new JavaScriptSerializer();
var result = serializer.Deserialize<ExpandoObject>(jsonString);
var quote = result.quotes.USDGBP;

Or in Json.Decode from System.Web.Helpers

Var result = Json.Decode(jsonString);
var quote = result.quotes.USDGBP;

edited Nov 29 '15 at 22:32

answered Nov 29 '15 at 22:22

swestner

1,881
15
19

1

I am not able to find either System.Json Namespace or System.Web.Script.Serialization any chance you can remember what reference you added? – Linda Lawton - DaImTo Mar 17 '17 at 12:37

score 0 · Answer 2 · answered Nov 29 '15 at 20:35

0

Parse your json object using JSON.NET:

dynamic d = JObject.Parse(jsonString );
Console.WriteLine(d.quotes.USDGBP);

answered Nov 29 '15 at 20:35

KiwiPiet

1,304
14
21

I cannot add any third party libraries, so am not able to use JSON.NET – EvanMPW Nov 29 '15 at 21:20

score 0 · Answer 3 · edited Dec 20 '17 at 10:24

0

You can use JavaScriptSerializer You can add Syatem.Web.Extensions namespace reference.

    var serializer = new JavaScriptSerializer();
     //Serialize
     var serializedResult = serializer.Serialize(Object);
     //Deserialize
   var deserializedResult = serializer.Deserialize<OutputObjectType>(jsonString);

edited Dec 20 '17 at 10:24

Kalamarico

5,466
22
53
70

answered Dec 20 '17 at 10:05

Rohit Naik Sawal

23
1
5

Parse JSON string using C# script in SSIS

3 Answers3

Linked