0

I have a task in which i want to send a SQL QUERY OUTPUT as mail body using Power Automate and ADF. i am creating a Lookup activity in ADF where i am passing my query as: select * from adventure table where Animals = 'Lion' and Jungle = 'Amazon'. this table is available in the SQL Database.

the output of the above query i want to send in mail body not as csv attachment but as mail body only in form of table so third party can directly view the data in mail only using Power Automate Flow. this flow should run in every 1 hour daily.

 mail SUbject should look like:
    FROM: People@gmail.com
    TO : Linking@gmail.cok
    Subject: Animals Details
    Hi XXXXX,
    these are the animal details :

output of SQL query: 
in table format

Thanks Retailer

BigData Lover
  • 135
  • 8
  • i am using lookup activity where i am running my Query, just want to clarify is this good way to start the Process or some other activity we should use. – BigData Lover May 17 '23 at 18:01
  • Just want to add one more point we don't want to share the QUERY output as csv file and send as attachment in mail. – BigData Lover May 17 '23 at 18:02
  • I think you are right in using Lookup activty in ADF . Since ADF does not have its own email feature . have you explored Logic app . The below thread should give you some idea . https://learn.microsoft.com/en-us/answers/questions/152528/logic-apps-how-to-send-csv-query-results-through-e – HimanshuSinha May 17 '23 at 20:40
  • Hi @HimanshuSinha as i am new to power automate, can you please help me with the end to end explanation. The link you have shared is totally different from my query – BigData Lover May 17 '23 at 20:54

1 Answers1

1

Below is something you can try to achieve your requirement. After the lookup activity, I used Web Activity and did a post call on power automate as below.

enter image description here

Body - @activity('Lookup1').output.value

Flow in power automate can be structured as below.

enter image description here

Here is the schema generated for the sample data

{
    "items": {
        "properties": {
            "animal": {
                "type": "string"
            },
            "id": {
                "type": "integer"
            }
        },
        "required": [
            "id",
            "animal"
        ],
        "type": "object"
    },
    "type": "array"
}

sample data

[
  {
    "id": 1,
    "animal": "Dog"
  },
  {
    "id": 2,
    "animal": "Cat"
  },
  {
    "id": 3,
    "animal": "Raccoon"
  }
]

Results:

enter image description here

SwethaKandikonda
  • 7,513
  • 2
  • 4
  • 18