Business Problem: Once a day we would like to read multiple rows from a database(with a specific criteria), iterate over those rows and send out an email to the email in the row and then update that row stating that the email has been sent (to prevent emailing that person every day or multiple times a day).
All of our databases, servers, wep applications, etc. are using windows azure. Seeing as we have app services, we began looking into this problem with the idea of creating a logic app.
Simple Logic App Flow: Recurrence(once a day) => SqlConnector(StoredProcedure or select statement) => Foreach row (emailapi(row.email) => SqlConnector(Update row))
Complications:
The logic app needs to read from one of our sql databases. So we approached this by creating a sql connector and from that connector we can expose stored procedures, tables, etc. The main issue with the sql connector is that the stored procedure we are wanting to call just selects the data from the table based on a where that involves a sql function, and the sql connector cannot generate the metadata necessary for the logic app to read in the rows returned by the select statement. The sql connector can only generate metadata for the out parameters or return values, we are unable to return multiple rows through either.
The next idea and the 2nd complication is that since we realize we cannot call this stored procedure and get the rows back we then attempt to get our rows using a select statement through the sql connector. The problem with this method is that our where clause MUST have a sql function in it and this is not supported.
Ignoring Complications:
Assuming that we can read these specific rows from a database, we then want to iterate over these rows, which should be available through the "repeat" operation in the logic app, and send out an email. We are choosing to use our own custom api to send out the email(azure does not provide a managed api for our email service SendWithUs). The email piece works completely fine and we are able to see and call our api endpoint from our azure logic app. Our concerns with this api endpoint to send emails is that it is not the most secure.
My question/s: Can we accomplish the task that we are trying to accomplish with the sql connector or should we be looking into alternatives?
Alternative Approach: Put everything we want to do in the custom api app that is providing the email endpoint. This would entail connecting to the database, calling a stored procedure, looping over the results from the stored procedure to send out emails, and then updating the database records that had emails sent out. Our logic app at this point would just have a recurrence trigger, and an api call that does ALL the work. This api endpoint though, would need to be as secure as possible while still being accessible from the logic app.
