CODEWITHSUNDEEP
sqlsql-serverpowerappspower-automate
Mangésh Méhendalé
Mangésh Méhendalé

Reputation: 47

Insert rows to SQL Server from Microsoft Flow

I have designed a PowerApps App to collect data from the user. A flow will be used to insert a new row into a 2 SQL Server tables. The app has 2 screens with a form each. The first form is InvoiceHeader and the 2nd form is InvoiceDetails. As is probably apparent from the names, there is a parent-child relationship between InvoiceHeader and InvoiceDetails.

The flow will create two actions in parallel - an 'Insert Row' on SQL Server table tblInvH and an insert row on table tblInvD. What I want to ensure is that in case the insert to either table is successful but the other fails, the entire transaction should rollback. Only a rollback mechanism would align to business logic where you cannot have an InvoiceHeader without InvoiceDetails and vice versa.

I have successfully attempted this construct through SSIS where I can wrap both insertrow dataflow tasks under an integrated transaction. This is exactly what I want to achieve with the construct of the flow.

A few google searches indicate that the "scope" control may be what I need.

However, upon a bare reading, it appears that scope control would only return a net failure or success back to the user. There does not appear to be any cure for reversing the data posted to either tblInvH with no matching posting in tblInvD, or vice versa.

Within each table I have ensured implicit transaction control by specifying NOT NULL constraints - it's the cross table transaction control that is eating into me.

Just to preempt any suggestions to stay with SSIS - that's really not an option. This is because PowerApps offers a far more scalable solution than distributing excel templates which the users will otherwise be required to populate. Further, the PowerApps capability of mining into the Office365Users means that the multi-layer invoice approvals can be triggered from within the PowerApps form itself. Plus all validation can happen at the form level rather than post hoc Excel sheet checking. Just recreating this in Excel will be a world of pain.

Upvotes: 1

Views: 2504

Answers (1)

carlosfigueira
carlosfigueira

Reputation: 87218

PowerApps (and Flow) do not support transactions natively. If you want to have two insert operations in a transacted way, you can create a stored procedure in your SQL Server (that receives whatever parameters it needs for the two insert operations), and call that stored procedure from Flow.

Upvotes: 1

Related Questions