Sending SQL Query Result via an Email in ADF

  • New to Azure Data Factory, and as a starting point, we're trying to see if we can use it to to run some of the menial tasks. We have a couple of SSIS jobs that we'd like to get rid of and ADF is being pushed as a likely replacement.

    One such task requires it to run a simple query and email the output from that query, on a schedule.

    I have a Script activity, and a Web Activity. The Web Activity is attached to a Logic App, which is using the 'When a HTTP request is received' and 'Send Email (V3)' Actions.

    It's apparent that the JSON settings play a major part in passing information around, but I haven't found a decent example of how that works.

    Currently, I know the query is completing, but I get an email that has no attachment. If I have to change the query to output an XML string, then that would probably suffice, but I still don't understand how that result would be passed from the Script Activity, to the Web Activity and ultimately to the 'Send Email (V3)' Action.

    Any assistance would be appreciated.

    Steve.

     

  • It would be pretty tricky to get it working like this, and would probably be more complex than you would like...in my opinion.

    You can send data via an API request in the request URL, header or body. If the query returns a lot of data, you would have to convert it to JSON first and then add it to the body of the API request. Assuming your Logic App can interpret the contents of the request body, it would then need to create an email attachment from the JSON it received and then attach that to an email message. Pretty gnarly...

    I'd recommend an easier approach. Use a Copy Data task in ADF to store the results of the query in a text file (JSON if you prefer) hosted in blob storage. Then send the API request to your logic app to let it know it's done. Your logic app should be able to pick that file up from blob storage and attach it to an email. That's what I would do in this case.

    Hope it helps.

  • Yeah, I was beginning to suspect that it was going to be more fun than the articles hinted at. So, I have tried another version, where it outputs to a csv file and an email picks that up.

    So much easier.

    Thanks for taking the time, and confirming my suspicions about my original plan of attack.

    Steve.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply