Load files from Onedrive as tables in the staging area of Azure SQL database

Question

question

DanishMuhammadHaroon-1241 asked May 12, '22 AnuragSharma-MSFT commented May 19, '22

Load files from Onedrive as tables in the staging area of Azure SQL database

Hello there

Business users in my organization load files in their respective folders on OneDrive. Every time there is a new file in any folder, I would want to have it created as a new table or appended to the existing table in the staging area. In case a new table is created and an existing table with the same prefix already exists, I would want to move the existing one to an archive db or have it renamed.

Kindly suggest, how can this be done.

Danish

azure-sql-database office-onedrive-client-itpro azure-automation

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AnuragSharma-MSFT · May 19 at 08:56 AM

Hi @DanishMuhammadHaroon-1241 , please let us know if you need any other details. If any answer helped, you can mark it 'Accept Answer'.

0 ·

Answer 1 · 2022-05-12T12:02:14.87Z

AlbertoMorillo answered May 12, '22 AlbertoMorillo commented May 12, '22

You can take advantage of triggers on Azure Logic Apps to process any documents stored on OneDrive and get that data imported into Azure SQL. Please read here (connector for OneDrive).

On this other documentation you will get a second connector for OneDrive for Business.

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DanishMuhammadHaroon-1241 · May 12 at 12:37 PM

So, which pipeline is possible, and one that you recommend. Also, if you can explain why. Files in reference are excel and csv files with a max file size of 100 MB.

OneDrive -> Azure blob -> Azure SQL db
OneDrive -> Azure files -> Azure SQL db
OneDrive -> Azure SQL db

0 ·

AlbertoMorillo DanishMuhammadHaroon-1241 · May 12 at 02:01 PM

Since your requirement is when the file is created in onedrive then it should be copied to Azure file storage account (General Purpose) then you can leverage When a file is created action and pass the output (content of the file) to Create file of file storage.

0 ·

question