How do I set up an automated CSV export job from Azure SQL Server?

Question

In an on-prem SQL Server I have the option to set up scheduled Jobs with the SQL Server Agent. This feature is not present in Azure. Is there any way to do this easily in Azure or will I have to rely on automation scripts / powershell scripting for this?

The task I want to accomplish is to export a bunch of SQL views to CSV and send them to a remote FTP server.

Answer 1

In Azure, through Logic Apps, you can achieve this. Please check below steps.

1. Go to Azure Portal ( http://portal.azure.com/ ) and Search Logic Apps.
2. Click Add and fill the details like Logic App Name, Subscription,Resource group, Location and click Create.
3. After refreshing the page, click the Created Logic App. In the home page, choose Blank Logic App.
4. In the Logic Apps designer Page, Search for Schedule or Recurrence and click it.
5. Fill the Interval, Frequency, Time zone (Format is important – 2018-10-16T21:00:00Z ), Start time, at these hours, at these minutes, Check the Preview.
6. Choose an Action, Search for SQL Server and Click Execute Stored Procedure in the list.
7. Click Add New connection at the end if you want to create new connection. Then Click Manually Enter Connection Information at the end if you want to create new connection.
8. Or else use anyone of the Existing connections. Fill the Procedure name with the required SP & below are the input parameters of the SP that you selected.
9. Choose an Action, Search for Create CSV Table and Click it. Fill the From (choose dynamic result set from the right side-Choose first result set alone), Include Headers (Yes), Columns (Automatic).
10. Choose an Action, Search for Office 365 Outlook & Search for Send an Email and Click it. Before proceeding, Please check mail id at the bottom. Change as yours. Fill the To ([email protected];[email protected]), Subject (Demo mail), Body (Please check the test attachment), From & CC & BCC (email id’s for whom you want to send), Important (Normal), Is HTML (NO), Attachment Name {choose expression from the right hand side and type concat('Test_mail',utcnow('dd-MM-yyyy'),'.csv') }, Attachment Content (Choose output from the right hand side).
11. Finally Click Save at the left hand side top. Click Designer option to edit, after edit completes, again save that. Click Run for Demo Run. Click Run to initiate the trigger. Then only the automated mails should come at mentioned intervals. Select the Required Logic App and click Delete to delete it (will ask Logic App name to delete). You should have access to do the above changes and you have to sign in to open the azure portal.