SSIS - SQL Results with Individual Columns and Convert to JSON column to save in Destination table that has single JSON column

Question

I am currently working on a SSIS package that gets results from two SQL tables and then performs a Merge JOIN to get a result set. This result will have around 50K rows and 10 columns. After this, I have to add a JSON column to each row because my destination table just has one JSON column to save the result. So how would I create a derived JSON column from individual columns and then save to destination? Is there any other way of achieving this?

I do not want to store the result set to a TEMP table and then fetch from temp table and perform SQL Execute Task to serialize to JSON before storing to destination - This process is taking lot of time.

Another option I tried is Script Component which also does not perform very well and takes even more time than creating TEMP Table.

Answer 1

since there really isn't any depth to the Json and only 10 columns, how about just creating it manually?

string json = "{" + "ColOneName:\"" + ColOne.Value.ToString() + "\"";
json += "," + "Col2Name:\"" + Col2.Value.ToString() + "\"";
....
json += "," + ColLastName:\"" + ColLast.Value.ToString() + + "\"}"; 

Row.Json = json;

Notes:

1. You need to pass in all columns into the script as read and create a new column (match your DB on type).
2. I treated the columns as strings. You may need to treat each column as to the data type that it actually is (no quotes for numbers for example).