SSIS Excel/CSV from Sharepoint to SQL Server

Question

I need to take Excel/CSV files from Sharepoint and put them into a table on SQL Server.
I'd like to like these packages to deploy automatically, so anytime somebody puts in a new excel file in a specific folder/subfolder, it will shoot it into my database.

Is there a way to do this without downloading the files locally, and just transferring from share point itself? I already know how to take excel files locally and importing it through SSIS.

The idea is to get people who know nothing about SQL Server and SSIS to import data just by uploading excel files to sharepoint.

Answer 1

If you are in a cloud-based environment, your requirement can be handled in Office 365 by either a Microsoft Flow or in Azure with a Logic App (they both use the same engine). It is essentially a codeless configuration of connectors and tasks.

1. Within MS Flow, add the SharePoint connector and set it to trigger the flow whenever a file is added or updated in your SharePoint directory. This will be fairly intuitive in the interface.

2. You will then need to add a step to download the file to the user's OneDrive so that it can be opened and accessed. I downloaded it directly to the OneDrive root and then later removed the file after processing the data in the flow. This is a bit circuitous, but there currently isn't functionality to access SharePoint files directly with Flow or Logic Apps (that I'm aware of).

3. Create a step that processes the OneDrive file using a GetRows action to process each row in the file.

4. Within the loop of the GetRows action, you establish a SQL Server connection and wire up an INSERT query or call a Stored Proc to load the SQL table.

5. You can also send Pass/Fail e-mails to a list of users if need be.

I'm simplifying the solution, but it is relatively intuitive once you familiarize yourself with the Flow/Logic Apps interface. It takes a bit of head-banging to get through some of the idiosyncrasies of the interface and it has its limitations, but eventually I got through it and it worked like a charm. Also, because Flow/Logic Apps is polling the SharePoint folder for a new/modified file every 5 minutes or so, sometimes it takes up to 5 minutes before the flow is processed, so that is a drawback if you need instant results.

Answer 2

I have made a similar scenario but not with sharepoint, i was listening from a local directory. Hope that this answer puts you on the right way.

I think that you have to use/build an application that listen on a specific folder and when excel file is added it execute the package using dtexec utility.

You can refer to these links for more information:

• Comprehensive C# watch-folder for SharePoint
• FileSystemWatcher Class
• Using FileSystemWatcher to monitor a directory
• How to detect a new file in a shared folder?
• Monitor a SharePoint document library (ACM)
• SSIS Data Flow Source & Destination for SharePoint Excel Services
• dtexec Utility (SSIS Tool)