1

I have recently installed Integration Runtime for a local server which hosts an Access DB. The idea is to pull data from it and store in in Azure SQL DB. I have done the following:

  1. Integration Runtime Services Installed on local machine hosting the MS Access DB and connected to it using ODBC
  2. Created linked services in ADF to connect to the DB
  3. Created Datasets for source and destination DB for each table required. One for the source msaccess and one for the target in azure sql db
  4. Created a pipeline to copy the data from the source and sink into the asql db mentioned in step #3

Basically, all the connections work however when I trigger my pipeline to load around 10 of these tables, it runs and loads the first two and then fails afterwards by timing out. I must restart the Integration Runtime everytime to get it back up and running otherwise I can no longer query the tables.

To mitigate this I figured there was too much traffic and the server needed to rest between calls so I added wait timers in between each step of the pipelines but not much success. It did help a bit but that might be coincidence.

The error log in the monitor spits out at the failed step is : Error: 2200 ErrorCode=UserErrorFailedToConnectOdbcSource,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=,Source=,'

btw the integration runtime install service running is 5.12.7984.1 and the version of access installed is through office 365 x64. The exact MS access driver is 16.14430.20006. The OS is Windows Server 2019

ezaidi
  • 25
  • 6
  • Basically it has nothing to do it seems with how big the tables are. Its how fast the next step occurs that seems to screw it up. If I wait long enough before processing the next table I think it does work. im just not sure how much time it needs. – ezaidi Dec 03 '21 at 18:53
  • This is a portion of what the pipeline looks like: https://pasteboard.co/0lF9yPFSKzAb.png – ezaidi Dec 03 '21 at 18:56
  • Were you able to figure this out? I'm having the exact same problem. – Daniel Harmon Feb 10 '22 at 15:19
  • Did you find a solution? I'm facing a similar issue where the IR just gets completely stuck after a few days requiring restarts every time – Joost May 11 '22 at 15:05
  • Hi guys, sorry I forgot about this post. The simple answer I found to get this working is to completely remove msaccess 365 from that machine where SHIR was running on and installed the msaccess runtime driver only. Specifically the AccessRuntime_x64_en-us.exe 10.00.17763.01 worked for me. The whole issue is a conflict between two incompatible drivers. I suggest you install SHIR on a seperate machine away from the DB so that you dont need access installed and just SHIR with the access runtime only. – ezaidi May 12 '22 at 18:46

2 Answers2

0

I am getting the exact same error. To start, I did check the event viewer logs and saw some errors to do with access. So I gave the user running the IR more access to the registry keys/general log on as a service rights. This helped a little, but I am still stuck with the same problem.

Benjamin Bingham
  • 87
  • 7
0

When copying from an Access DB located on the SHIR itself to flat files in lake storage, I encountered the same error.

Removing Office 365 from the machine, then re-installing the Access runtime solved the problem.

This answer is from @ezaidi comments above.

Christian Stewart
  • 15,217
  • 20
  • 82
  • 139
RogN
  • 1