0

we have an old legacy application with an old Access 2003 DB sitting on an onprem server. There is currently no way to recompile the application so it stays as is. I would like to know how I can do a daily extract of the data from that Access MDB (certain tables) and load them into our Azure SQL DB so that we can use the data in BI reports and other applications.

The data load only needs to happen once at night and its a complete flush and load from access into Azure SQL table. Both source and destination will have the same schema.

I understand that currently Azure SQL doesnt have linked server capabilities.

We have experimented with using a local onprem SQL server and created a linked server (to azure) and SSIS package to load the data but I feel that there must be a simpler and better way.

jarlh
  • 42,561
  • 8
  • 45
  • 63
ezaidi
  • 25
  • 6

1 Answers1

1

You can use Azure Data Factory to load data periodically.

Use Copy Data Activity.

Add onprem Database as Source and Azure Sql database as Sink.

enter image description here

Add Trigger to load data at a specific time and at regular interval as per your requirement.

enter image description here

You can refer this official documentation.

Abhishek K
  • 3,047
  • 1
  • 6
  • 19
  • This works well I think when you have already an SQL server with data on Prem. How do I go about using ADF to connect to an access DB located on some file server though? – ezaidi Nov 25 '21 at 18:40
  • I think im on the right path. I installed Integration Runtime on the local server and registered it with Azure. So far so good. I then created a linked service and tried to use the standard ODBC connection string i always use. For some reason I'm getting: ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified – ezaidi Nov 25 '21 at 20:38
  • @ezaidi These 2 threads can help you to resolve error 1. https://learn.microsoft.com/en-us/answers/questions/227987/34error-im002-microsoftodbc-driver-manager-data-so.html 2. https://stackoverflow.com/questions/17115632/microsoftodbc-driver-manager-data-source-name-not-found-and-no-default-drive – Abhishek K Nov 26 '21 at 03:43
  • I found what the problem was. The access db had a password on it and it needed its top level folder to have "authenticated users" permission group for it to work. The alternative was to remove the db password and it would also connect. I also experimented with different access drivers but just plain office 365 x64 works fine. – ezaidi Dec 02 '21 at 11:43