This is partially a "How Do I..." post and partially a "This is how I..." post.

I am designing a Web API integration that accepts JSON containing an array of items and returns a JSON object containing an array of objects with values related to each input item. There are several related values for each item. The SQL to calculate the return values is too complex for a lookup.

The client's requirement is for the result to be returned within two seconds, so I don't have much time to spare. Using VBScript to execute a stored procedure from a Script task seems cumbersome and slow.

What I have come up with is this:

• Create a SQL Table-valued Function that takes the input and calculates the related values, and returns the values in a single row.
• Create a table containing columns for the input values and the related output values, as well as a Job ID. The Job ID is derived from an IMan counter.
• Create an AFTER INSERT trigger that calls the Table-valued function and updates the inserted row from the row returned by the function.
• Create a Lookup that retrieves the return values added to the inserted row, with the results cached.
• In a DB Writer, insert a row into the table for each input item. Include the Job ID in the insert.
• In a Map transform, call the Lookup to retrieve the return values.

What would you do?