SSIS-从Sharepoint下载Excel并将其加载到SQL数据库 [英] SSIS- Download and Load Excel from Sharepoint to SQL Database

问题描述

我目前遇到一种情况，共享点站点上有一个excel文件.我需要将该excel文件加载到数据库中. Excel文件有多张纸.我已经尝试过零运气的以下方法.

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.

方法1-我转到了共享点站点，然后转到了库"选项卡 并选择使用浏览器打开链接.我已经将该链接用作 文件在excel连接管理器中的位置.我成功地 在本地运行ssis软件包，但是在ssis软件包上运行时失败 服务器(权限不足).后来我发现我们无法 直接连接到sharepoint进行ETL.

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.

方法2-我尝试使用运气为零的http连接管理器.

Method 2- I tried using http connection manager with zero luck.

我认为最好的方法是使用SSIS中的脚本任务.但是我对脚本任务还是相当陌生，因此如果有人可以提供代码和使用它的方式，那将是很棒的.我确实在网上看过一些示例代码，但是我找不到一个可以正常工作的代码(很可能是我使用了不正确的代码).该文件将需要覆盖任何同名文件.

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.

在此示例中，请在下面使用它们

For this example lets use these below

Sharepoint Path= http://example.com/example/excel.xlsx
Local Path= C:\

推荐答案

您可以从sharepoint将Excel下载到本地驱动器( https: //stackoverflow.com/a/45553754/8430404 )，然后映射sis中的每个excel选项卡以加载到数据库中

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

或还有一种C#方法来加载所有excel选项卡数据. ( http://www.techbrothersit.com/2016/04/c-import-excel-sheet-data-with-excel.html?m=1 )

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)

请确保目标表中具有主键/索引，否则可能会导致重复. excel和目标表的标题也应该匹配(完全匹配)

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)

这篇关于SSIS-从Sharepoint下载Excel并将其加载到SQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！