Back to forum list… Back to How Do I?...

Complex SQL results. Is this the best way? (No replies)

John G
1 year ago
John G 1 year ago

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?

Contact

Realisable Software Ltd provides code-free, cost-effective applications integration solutions for SMEs. Our core IMan product is designed to integrate almost any application with a number of Sage solutions and online payment processors.

Looking to purchase IMan, please see our resellers here.

Realisable Software
Ph: +44 (0) 208 123 1017

Copyright © Realisable. All rights reserved.
Realisable is a registered trademark

Close

Request Demo

Realisable Software Ltd provides code-free, cost-effective applications integration solutions for SMEs. Our core IMan product is designed to integrate almost any application with a number of Sage solutions and online payment processors.

Looking to purchase IMan, please see our resellers here.

Realisable Software
Ph: +44 (0) 208 123 1017

Copyright © Realisable. All rights reserved.
Realisable is a registered trademark

Close

Access Downloads

Realisable Software Ltd provides code-free, cost-effective applications integration solutions for SMEs. Our core IMan product is designed to integrate almost any application with a number of Sage solutions and online payment processors.

Looking to purchase IMan, please see our resellers here.

Realisable Software
Ph: +44 (0) 208 123 1017

Copyright © Realisable. All rights reserved.
Realisable is a registered trademark

Close