2

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.

Hadi
  • 36,233
  • 13
  • 65
  • 124
AlanPear
  • 737
  • 1
  • 11
  • 32

2 Answers2

2

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.

rickj_65
  • 66
  • 4
1

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:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    Thank you so much for this. I can most likely accomplish this via VB.net or powershell. However, my biggest concern is how to get the excel file located on sharepoint, and passing it through to sql server using SSIS. I can't seem to find an answer online anywhere else. – AlanPear Jan 07 '18 at 19:38
  • https://social.msdn.microsoft.com/Forums/sqlserver/en-US/7a48f447-f2c5-42f2-9615-227c7cfdc4ad/how-to-read-excel-file-in-document-library-of-sharepoint-with-ssis?forum=sqlintegrationservices check this link it may help you – Hadi Jan 07 '18 at 19:45
  • If reading from sharepoint is not achieved, try to automate the file download process – Hadi Jan 07 '18 at 19:46
  • 1
    In my question I specified I don't want to download the files locally. This would mean the process is running through my personal machine. – AlanPear Jan 07 '18 at 19:50
  • https://marketplace.visualstudio.com/items?itemName=CDATASOFTWARE.SSISDataFlowSourceDestinationforSharePointExcelSer i think this is what you are looking for. – Hadi Jan 07 '18 at 19:54
  • That does seem to be what I'm looking for. However it's paid, and says only compatible with visual studio 2015 – AlanPear Jan 07 '18 at 20:14
  • @AlanPear what is the visual studio version you are working with – Hadi Jan 07 '18 at 20:47
  • The latest 2017 version – AlanPear Jan 07 '18 at 20:53
  • @AlanPear i don't think you will find anything else. it is good to take a look at this link it contains tutorials and adapters for connecting to Sharepoint using SSIS hope this helps https://msdn.microsoft.com/en-us/library/hh368261.aspx – Hadi Jan 07 '18 at 20:53
  • Thanks for taking the time to answer my question. However, I'm wondering if this method is not the best for my situation. I posted another question here: https://stackoverflow.com/questions/48142572/will-ssis-fill-my-needs. This go into more depth of what I need and if SSIS will help. Thanks – AlanPear Jan 07 '18 at 23:38