0

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Rahul
  • 1

1 Answers1

0

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).
KeithL
  • 5,348
  • 3
  • 19
  • 25