从Sharepoint到SQL Server的SSIS Excel/CSV [英] SSIS Excel/CSV from Sharepoint to SQL Server
问题描述
我需要从Sharepoint提取Excel/CSV文件并将其放入SQL Server上的表中.
我希望这些软件包能够自动部署，因此，只要有人将新的excel文件放入特定的文件夹/子文件夹中，它就会将其射入我的数据库中.
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.
有没有一种方法，而不必从本地下载文件，而只是从共享点本身进行传输?我已经知道如何在本地获取excel文件并通过SSIS导入它.
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.
这个想法是让对SQL Server和SSIS一无所知的人仅通过将excel文件上传到sharepoint来导入数据.
The idea is to get people who know nothing about SQL Server and SSIS to import data just by uploading excel files to sharepoint.
推荐答案
如果您处于基于云的环境中，则可以通过Microsoft Flow在Office 365中或使用Logic App在Azure中处理您的要求(两者都可以)使用相同的引擎).它本质上是连接器和任务的无代码配置.
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.
-
在MS Flow中，添加SharePoint连接器并将其设置为在SharePoint目录中添加或更新文件时触发流.这在界面中将非常直观.
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.
然后，您需要添加一个步骤，将文件下载到用户的OneDrive，以便可以打开和访问该文件.我将其直接下载到OneDrive根目录，然后在处理流中的数据之后删除了该文件.这有点circuit回，但目前尚不具备通过Flow或Logic Apps(我知道)直接访问SharePoint文件的功能.
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).
创建一个步骤，该步骤使用GetRows操作处理OneDrive文件，以处理文件中的每一行.
Create a step that processes the OneDrive file using a GetRows action to process each row in the file.
在GetRows操作的循环中，您将建立SQL Server连接并建立INSERT查询或调用Stored Proc来加载SQL表.
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.
如果需要，您还可以将通过/失败电子邮件发送给用户列表.
You can also send Pass/Fail e-mails to a list of users if need be.
我正在简化解决方案，但是一旦您熟悉Flow/Logic Apps界面，它就会相对直观.理解界面的某些特质需要花些力气，但它也有其局限性，但最终我克服了它，它像一种魅力一样工作.另外，由于Flow/Logic Apps每5分钟左右轮询一次SharePoint文件夹中的新文件/修改过的文件，因此有时可能需要5分钟才能处理完该流，因此如果需要即时结果，这将是一个缺点.
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.
这篇关于从Sharepoint到SQL Server的SSIS Excel/CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
