Using Newtonsoft.Json with Biml

Twitter provided an opportunity for a quick blog post

#sqlhelp #biml I would have the metadata in a Json structure. How would you parse the json in the C# BIML Script? I was thinking use Newtonsoft.Json but I don't know how to add the reference to it

Adding external assemblies is a snap but here I'll show how to use the NewtonSoft Json library to parse a Json based metadata structure and then use that in our Biml.

<Biml xmlns="http://schemas.varigence.com/biml.xsd">
<#
// Given the following structure

///{
///  "packages": [
///    "p1",
///    "p2",
///    "p3"
///  ]
///}

// Assume the json file is located as specified
string sourceFile = @"C:\ssisdata\trivial.json";

// Read the data into a string variable
string json = System.IO.File.ReadAllText(sourceFile);
 
// Deserialize the json into a dictionary of strings (packages) and a list of strings (p1, p2, p3)
Dictionary<string, List<string>> metadata = JsonConvert.DeserializeObject<Dictionary<string, List<string>>>(json);
#>
<Packages>
<#
// Shred the dictionary for our values
foreach (string item in metadata["packages"])
{
    //WriteLine(String.Format("<!-- {0} -->", item));
#>
    <Package Name="<#=item #>" />
<#
}
#> 
</Packages>
</Biml>

<#@ import namespace="Newtonsoft.Json" #>
<#* Assuming we have GAC'ed the assembly *#>
<#@ assembly name= "Newtonsoft.Json.dll" #>

The gist is also posted in case I mangled the hand crafted html entities above.

Also, not covered is GAC'ing the assembly but you can use an explicit path to your DLL name="C:\where\did\I\put\this\Newtonsoft.Json.dll"