Automate Access database to SQL server data load

Pratim Das, Partha C 306 Reputation points
2023-04-25T07:34:02.7266667+00:00

Hi, My requirement is to load data from Access database, placed in Network drive to Azure SQL. I know there is option to use Data Factory but for that I need to install backdated Access ODBC driver in self hosted IR which is being blocked by security policy of our organization. So I'm planning to push the data in Blob storage as CSV file and then use ADF to load CSV into Azure SQL table. Finally I want to automate it. Please guide how I can achieve that. Regards, Partha

Azure SQL Database
Azure
Azure
A cloud computing platform and infrastructure for building, deploying and managing applications and services through a worldwide network of Microsoft-managed datacenters.
1,048 questions
Azure Data Factory
Azure Data Factory
An Azure service for ingesting, preparing, and transforming data at scale.
9,929 questions
Access
Access
A family of Microsoft relational database management systems designed for ease of use.
325 questions
0 comments No comments
{count} votes

Accepted answer
  1. RevelinoB 2,775 Reputation points
    2023-04-25T07:46:52.5866667+00:00

    Hi Partha, This issue looks familiar, based on what you described, it seems like using Blob storage and ADF to load CSV into Azure SQL table would be a viable solution. To automate the process, you could create an Azure Logic App that triggers when a new CSV file is uploaded to Blob storage. The Logic App could then use the "Copy Data" activity in ADF to load the data from the CSV file into your Azure SQL table. To create the Logic App, you can follow these steps: Create a new Logic App in the Azure portal Add a "Blob storage - When a blob is added or modified" trigger Add a "Copy Data" activity from ADF as an action in the Logic App Configure the "Copy Data" activity to load data from the CSV file in Blob storage into your Azure SQL table Once you have created the Logic App, you can set up a schedule or trigger to run the app on a regular basis to automate the data loading process. I hope this helps? Let me know if you have any further questions.


0 additional answers

Sort by: Most helpful