In this blog, we’ll walk through a practical approach to inserting data from Business Central (SaaS) to SQL Server (on-premises) using Power Automate, and SQL Server connector.
This guide is especially useful for businesses running Business Central in the cloud but still maintaining an on-prem SQL database for operations, reporting, or legacy integrations.
Pre-Requisites:
- Access to Microsoft Power Automate.
- Access to Business Central (Cloud SaaS version).
- SQL Server with a OnPrem database.
We will use Power Automate Flow to achieve this . Below are the steps to be implemented.
1. Go to Power Automate Portal: https://make.powerautomate.com
2. Click on + New Flow → Automated Cloud Flow.
3. Provide Flow Name: Insert Business Central Data to SQL Server.
4. Choose trigger:
- Search for Business Central.
- Select trigger “When a record is modified (V3)” or “When a record is created (V3)”.
- Set the environment, company, and table (e.g., Customer Card).
5. Insert New step (+) and click on Add an action. Add Business Central’s Get Record trigger to fetch data from Business Central SaaS table.
6. Insert new step (+), search for Compose and add Last Modified date as shown below:
7. Install & Configure On-Premises Data Gateway
– Download the On-Premises Data Gateway from Microsoft.
– Sign in with the same account as used in Power Automate.
– Connect it to your on-prem SQL Server (set gateway name, etc.)
8. Again, Insert New step (+) and click on Add an action. Search for SQL Server and select Insert Row:
9. Map all the required fields:
10. Save the flow.
11. Modify or create a record in Shipment Method in Business Central.
12. Validate data is inserted into SQL Table via SSMS.
Conclusion
By following these steps, you can easily insert data from Microsoft Dynamics 365 Business Central SaaS into an on-prem SQL Server database using Power Automate and the SQL Server connector.
This integration enables businesses to bridge the gap between cloud ERP and on-prem systems—helping with real-time synchronization, reporting, and hybrid infrastructure needs.
Looking to optimize your Microsoft Dynamics 365 Business Central integrations? Our experts can help you streamline your hybrid data workflows. Contact us today to get started!
