Author name: Ritu Negi

Sending an email notification is the traditional way of checking if the integration has been done properly or not. In ADF for sending an email, we need to configure the logic app in the Azure portal. We can send several notifications on respective events like on Success, Failure or Completion of a pipeline. We can send a result set of a query as an attachment as well. But when we need to send a result set of a query in an email as a body, we don’t get the right format. Here I am explaining how can we show the result set with multiple records in tabular format in an email body using some simple steps. So, for this, we have a query whose result set needs to be sent by email. Here I am using Snowflake as a database but this query can be used in SQL Server too. Now to get it in a single record and give it an HTML table format, we need to modify it as: I have used <tr> and <td> tags in between to give it a tabular format and got it in a single record to process it in the Lookup activity in ADF in a single go else we need to use For each activity to process each record. Now if the logic app is already configured in the Azure portal, then create a pipeline in ADF and take a lookup activity as below and use the above query in its settings:   I have already configured a pipeline for Success Email Notification using Web Activity like the below:   Now add this pipeline to our pipeline as below: In the pipeline PL_SendEmailSuccess settings, write the code to the parameter which is mentioned as Body in LogicApp as below: Now we will execute the pipeline. Though the output of lookup looks messy as below:   But email we get is properly formatted in table form: Conclusion: This type of task can be used to send the status report of the jobs which run on a daily basis, to send an alert on significant metrics, or to get the result of ETL audits with rejected records. So instead of downloading attachments, users can have a quick check using this approach.

 The Write-Back functionality enables end-users to give input and update the data in the data warehouse directly from the BI report. In addition, this helps BI professionals to have a rich user experience in deploying solutions for budgeting, forecasting, planning, and adding comments.  This functionality can be achieved using Power Apps. There is no connector for Azure Synapse in PowerApps, therefore, the write-back to Synapse using Power Apps does not work directly.   This blog is to show the process of write-back on Azure Synapse using Power Apps while making Power Automate a bridge.   Power Automate has a Synapse connector that allows the changes in the database. For this we need a Synapse SQL table in which the data will be stored (Created WriteBackTable table), a power app form that has some input fields and a submit button. Then we will create a flow in power automate that will be then called in Power Apps Submit Button.     Here, the WriteBackTable is being used as a base table on which the Fact table will be created to show the Daily and Monthly Goal for different department in each month. The Department, Monthly Goal and Number of working days will be entered by the users using PowerApps. Below is the script for WriteBackTable:         The RowId is the primary key for unique Department and Month Year, which can be used further to decide weather the record will be inserted or updated. Initially there is no record in the table so the data will be inserted:         Below is the complete flow which is explained further in detail:         For creating the flow, first we need to click on Create and select the Instant Cloud Flow:       Then we need to select the trigger of this flow as PowerApps and give our flow a name and then click on Create:     After that, we need to initialize a variable that we call from PowerApps:     We can Name the variable as we want and when we click on the value, the Dynamic content option pops up and we need to click on Ask in PowerApps:     This step will be repeated as many times as we have input fields in our PowerApps form, for me, its 4 so used 4 Initialize Variables as below.     Once we will initialize all the variables then we will add action Execute a SQL query (V2) under Azure SQL Data Warehouse to see if there are any records in the Synapse table for the key columns entered by PowerApps.   We are using “Azure SQL Data Warehouse” connector that allows us to connect to Azure Synapse and run SQL queries and stored procedures to perform CRUD (create, retrieve, update and delete). But it is available only in Power Automate and not in Power Apps and that is why we are using Power Automate as a bridge here:     In this option, we need to create a connection by clicking the three dots on the top right corner and then click on Add new connection:       Then we need to insert credentials for Azure Synapse Dedicated SQL pool using Basic username and password.       Once you will establish the connection, the Server Name and Database will come in the drop-down list and you can select them and write your query as above.   Note: We need to define the parameters and the data types that we need in our query. Here we need Department and MonthYear as the key columns (on the basis a record will be inserted/updated) so two parameters are created. Then assign the value by selecting the corresponding variable (coming from the PowerApps) from the Dynamic Content:       After this step we need to decide if the record will be inserted or updated in the table so we need to add a new step Condition:       In this step, we need to write an expression to know if the select query in the previous step returns any data or not. Here the expression is as below:   empty(outputs(‘Execute_Select_from_Synapse’)?[‘body/resultsets/Table1’])   But instead of copying the above line of code, you can run the flow and see the output for this step and note in which set you are getting the result, and then use the empty function in Dynamic content and select that set:       Now if the condition is True, i.e., the result set is empty then we need to insert the record else the update will be done. Therefore, for this we need to use the Execute a SQL query (V2) again and use the insert and update query:     After the flow is completed, go to PowerApps and click on the Submit button in the form on which we want to call the Power Automate flow and then click on Action and then Power Automate:     Then select your flow and give the parameters as below:     This App can be called using Power BI and whenever the user will enter values in the form and click on the submit button. The flow will then get triggered and data will be inserted/updated in the Synapse Database.     Conclusion: The PowerApps does not have any connector to Azure Synapse and hence there is no direct way to write back to the synapse database but using Power Automate as a bridge, we can use this functionality without having any trouble.