SSIS Script Task: Split a JSON array into a list

Question

SSIS Script Task: Split a JSON array into a list

Peter Bishop 181

I have a JSON array of the form:

"data": [
	{"tag1": 1,"tag2": 2,"section1": {"tag3": "Dummy"},"tag4": 3,"array1": ["dummy","dummy"]},
	{"tag1": 4,"tag2": 5,"section1": {"tag3": "Dummy"},"tag4": 6,"array1": ["dummy","dummy"]},
	{"tag1": 7,"tag2": 8,"section1": {"tag3": "Dummy"},"tag4": 9,"array1": ["dummy","dummy"]}
]

and I need to split this into a list containing:

Entry1: "{\"tag1\": 1,\"tag2\": 2,\"section1\": {\"tag3\": \"Dummy\"},\"tag4\": 3,\"array1\": [\"dummy\",\"dummy\"]}"
Entry2: "{\"tag1\": 4,\"tag2\": 5,\"section1\": {\"tag3\": \"Dummy\"},\"tag4\": 6,\"array1\": [\"dummy\",\"dummy\"]}"
Entry3: "{\"tag1\": 7,\"tag2\": 8,\"section1\": {\"tag3\": \"Dummy\"},\"tag4\": 9,\"array1\": [\"dummy\",\"dummy\"]}"

I've written a little loop of code to achieve this:

string json = "\"data\": [{\"tag1\": 1,\"tag2\": 2,\"section1\": {\"tag3\": \"Dummy\"},\"tag4\": 3,\"array1\": [\"dummy\",\"dummy\"]},{\"tag1\": 4,\"tag2\": 5,\"section1\": {\"tag3\": \"Dummy\"},\"tag4\": 6,\"array1\": [\"dummy\",\"dummy\"]},{\"tag1\": 7,\"tag2\": 8,\"section1\": {\"tag3\": \"Dummy\"},\"tag4\": 9,\"array1\": [\"dummy\",\"dummy\"]}]"

List<string> addresses = new List<string>();

for (int loop = 0, openSection = 0, openQuote = 0, start = 0; loop < json.Length; loop++)
{
	if (loop != (json.Length - 1))
	{
		// Skip the special characters
		switch (json.Substring(loop, 2))
		{
			case "\\\"":    // quotation mark
			case "\\\\":    // reverse solidus
			case "\\/":     // solidus
			case "\\b":     // backspace
			case "\\f":     // formfeed
			case "\\n":     // newline
			case "\\r":     // carriage return
			case "\\t":     // horizontal tab
				loop += 2;
				break;
			case "\\u":     // 4 hexadecimal digits
				loop += 6;
				break;
		}
	}
	if (json.Substring(loop, 1) == "\"")
	{
		if (openQuote == 0)
			openQuote++;
		else
			openQuote--;
	}
	if (json.Substring(loop, 1) == "{" && openQuote != 1)
	{
		if (openSection == 0)
			start = loop;
		openSection++;
	}
	if (json.Substring(loop, 1) == "}" && openQuote != 1)
	{
		openSection--;
		if (openSection == 0)
			addresses.Add(json.Substring(start, loop - start + 1));
	}
}

Although this works, is there a better way? I know there are JSON handling modules out there (Newtonsoft?) but I'm restricted to what can be installed on the target platform and I'm also not looking to actually manipulate the JSON data - just split the array.

Many thanks.

Yitzhak Khabinsky 26,471 Reputation points

2025-03-11T17:22:47.9033333+00:00

Without JSON API in the .Net Framework 4.* it is not easy to implement what you need in c#.

Just as an idea, you can pass your JSON string to SQL Server stored procedure as a parameter. SQL Server has JSON API starting from SQL Server 2016 onwards.
Peter Bishop 181 Reputation points

2025-03-12T08:36:28.6533333+00:00
My original plan was to do that, but we're getting JSON data in excess of 8000 characters from the source, which blew the (DT_STR, 8000, 1252) limit. I changed the 3rd party component to return this as (DT_TEXT, 1252) and wrote it to a varchar(MAX) column, but the data was corrupted(?) by adding an ASCII(0) character into every other position. So:

"data": [{"tag1": 1,"tag2": 2,"section1": {"tag3": "Dummy"},"tag4": 3,"array1": ["dummy","dummy"]}]

became:

"\0d\0a\0t\0a\0"\0:\0 \0[\0{\0"\0t\0a\0 ...

I couldn't find the root cause (3rd party component?) of these ASCII(0) characters or a resolution to remove them so I wrote the code above to work around it.

1 answer

Your answer

Yitzhak Khabinsky 26,471 Reputation points

2025-03-11T17:22:47.9033333+00:00

Without JSON API in the .Net Framework 4.* it is not easy to implement what you need in c#.

Just as an idea, you can pass your JSON string to SQL Server stored procedure as a parameter. SQL Server has JSON API starting from SQL Server 2016 onwards.
Peter Bishop 181 Reputation points

2025-03-12T08:36:28.6533333+00:00

My original plan was to do that, but we're getting JSON data in excess of 8000 characters from the source, which blew the (DT_STR, 8000, 1252) limit. I changed the 3rd party component to return this as (DT_TEXT, 1252) and wrote it to a varchar(MAX) column, but the data was corrupted(?) by adding an ASCII(0) character into every other position. So:

"data": [{"tag1": 1,"tag2": 2,"section1": {"tag3": "Dummy"},"tag4": 3,"array1": ["dummy","dummy"]}]

became:

"\0d\0a\0t\0a\0"\0:\0 \0[\0{\0"\0t\0a\0 ...

I couldn't find the root cause (3rd party component?) of these ASCII(0) characters or a resolution to remove them so I wrote the code above to work around it.

Answer 1

Hi @Peter Bishop , Welcome to Microsoft Q&A,

You can use System.Text.Json to simplify your code. Here is a .Net 8.o Console project:

using System.Text.Json;

string json = "{ \"data\": [{\"tag1\": 1,\"tag2\": 2,\"section1\": {\"tag3\": \"Dummy\"},\"tag4\": 3,\"array1\": [\"dummy\",\"dummy\"]}," +
                      "{\"tag1\": 4,\"tag2\": 5,\"section1\": {\"tag3\": \"Dummy\"},\"tag4\": 6,\"array1\": [\"dummy\",\"dummy\"]}," +
                      "{\"tag1\": 7,\"tag2\": 8,\"section1\": {\"tag3\": \"Dummy\"},\"tag4\": 9,\"array1\": [\"dummy\",\"dummy\"]}]}";

using JsonDocument doc = JsonDocument.Parse(json);
JsonElement root = doc.RootElement;

if (root.TryGetProperty("data", out JsonElement dataArray) && dataArray.ValueKind == JsonValueKind.Array)
{
    List<string> addresses = new();
    foreach (JsonElement element in dataArray.EnumerateArray())
    {
        addresses.Add(element.GetRawText()); 
    }

    for (int i = 0; i < addresses.Count; i++)
    {
        Console.WriteLine($"Entry{i + 1}: {addresses[i]}");
    }
}
Console.ReadLine();

Best Regards,

Jiale

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Share via

SSIS Script Task: Split a JSON array into a list

1 answer

Your answer