Sample Workflows: SQL Query DB & FTP Upload Excel Report and SharePoint, Excel, Access Automation

Posted on February 26, 2016

automate SQL sample workflow

We handle data in all kinds of formats on a daily basis. We retrieve, process and deliver data from different sources both internally and externally. Making sure data can be processed and delivered properly is a critical part of many organizations. And with the different types of applications and platforms we work with, it’s essential that we deliver data in a compatible format in the most efficient way.

This month’s sample workflow demonstrates how to schedule an automated process to connect and retrieve data from a database, generate a date-stamped Excel report from the retrieved data and upload it to an FTP server. The sample workflow utilizes several groups of Automate actions and it also demonstrates how to send an email notification to the administrators on error.

automate SQL sample workflow

Automate’s built-in actions support the ability to integrate and process data from different applications. Automate can integrate with any standard databases and process data from any standard file formats, such as Excel and Text. In addition, Automate Plus also offers the capability to automate a business process in a graphical workflow, letting users design flow processes to execute different tasks and handle exceptions and errors using BPA

Enterprise application integration is today’s new normal for businesses. So, for this month’s sample workflow, we would like to demonstrate how to launch data integration between different applications in order to share information between multiple departments.

SharePoint Workflow Examples

We are huge advocates of moving data across applications and platforms. Many companies use Microsoft SharePoint and a storage database such as Oracle or SQL to manually share their data between departments and organizations. Instead of relying on this time-consuming practice, Automate can process and integrate this same information with ease. While application integration easily works between SharePoint and Oracle, sometimes pesky Excel and Access still need to be included in that process. So for this example we will look at the application integration between SharePoint, Excel and Access.

The workflow objects in this sample include two event triggers: a schedule event to run daily at 7:00 AM and a Hot Key event generated when pressing “Ctrl+Shift+B”.  The “SharePoint Download File”* task downloads an Excel Worksheet called “parts.xlsx” from the SharePoint site to a local folder called C:\test.

Access Automation Workflow Examples

If the file download is successful, the information is extracted directly from Excel and imported into a Microsoft Access database with SQL query actions in the “Import Excel into Access”* task.  If the SharePoint download or “Import Excel into Access” task generates an error, the “Send Workflow Failure Email” task sends an email to the administrator and the workflow ends.  Once the workflow runs successfully, an email notification is sent to the Administrator, stating the task is complete. 

As mentioned earlier, although SharePoint, Excel and Access are used in this workflow, Automate can be used to integrate a multitude of other applications.  We have more than 525+ activities available for automation.  Additionally, the SQL Query actions used for importing the information into Access also applies to SQL or Oracle in the exact same way, utilizing an insert query statement. 

Ready to Start Automating?

Try Automate free for 30 days. You can set up bots to automate FTP uploads and many other tasks in your trial.

Start a Free Trial