vba SSIS- 从 Sharepoint 下载 Excel 并将其加载到 SQL 数据库
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45555620/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SSIS- Download and Load Excel from Sharepoint to SQL Database
提问by Mutai
I currently have a situation where there is a excel file on a sharepoint site. I need to load that excel file to a database. The Excel file has mulitple sheets. I have tried the follow methods with zero luck.
我目前的情况是，sharepoint 站点上有一个 excel 文件。我需要将该excel文件加载到数据库中。Excel 文件有多个工作表。我尝试了以下方法，运气为零。
Method 1- I went to the sharepoint site and went to the library tab and selected the open with explorer link. I have used that link as the file location in the excel connection manager. I was successfully able to run the ssis package locally but it fails when it runs on the server (insufficent permission). I later found out that we are unable to directly connect to sharepoint to do ETL.
Method 2- I tried using http connection manager with zero luck.
方法 1- 我转到 sharepoint 站点并转到库选项卡并选择使用资源管理器链接打开。我已将该链接用作 Excel 连接管理器中的文件位置。我能够在本地成功运行 ssis 包，但在服务器上运行时失败（权限不足）。后来我发现我们无法直接连接到sharepoint来做ETL。
方法 2- 我尝试使用零运气的 http 连接管理器。
I think the best way to do this is using the script task in SSIS. However i am fairly new to script tasks so if anyone can provide a code and a way to use it that would be great. I did look at some sample code online but I was unable to find one that worked correctly (it might very well be me that was using it incorrectly)The file will need to overwrite any files with the same name.
我认为最好的方法是在 SSIS 中使用脚本任务。但是，我对脚本任务还很陌生，所以如果有人可以提供代码和使用方法，那就太好了。我确实在网上查看了一些示例代码，但我无法找到一个可以正常工作的代码（很可能是我错误地使用了它）该文件需要覆盖任何具有相同名称的文件。
For this example lets use these below
对于这个例子，让我们在下面使用这些
Sharepoint Path= http://example.com/example/excel.xlsx
Local Path= C:\
采纳答案by BestDataPros
You can download excel to a local drive from sharepoint ( https://stackoverflow.com/a/45553754/8430404) then map each excel tab in ssis to load into database
您可以从 sharepoint ( https://stackoverflow.com/a/45553754/8430404)将 excel 下载到本地驱动器，然后映射 ssis 中的每个 excel 选项卡以加载到数据库中
or theres also a c# approach to load all excel tab data. (http://www.techbrothersit.com/2016/04/c-import-excel-sheet-data-with-excel.html?m=1)
或者还有 ac# 方法来加载所有 excel 选项卡数据。( http://www.techbrothersit.com/2016/04/c-import-excel-sheet-data-with-excel.html?m=1)
Be sure to have primary key/index in your destination table or you may end up with dupes. Also the headers of excel and destination tables should match (exact)
确保在目标表中有主键/索引，否则最终可能会被欺骗。excel 和目标表的标题也应该匹配（精确）
