如何将 Codeplex 2017 Sharepoint List Adapter 安装到 SSIS SQL Server 2019(开发/生产环境) [英] How to install Codeplex 2017 Sharepoint List Adapter onto SSIS SQL Server 2019 (dev/prod environment)
问题描述
如何使 Codeplex 2017 SharePoint 列表适配器与运行 SSIS 作业的 SQL Server 2019 开发/生产环境配合使用?-- Codeplex 适配器上次更新是在 2017 年，似乎没有升级它的计划.适配器不会在通过 SQL Server 2019 建立的较新文件夹中安装/注册，因为它们仅安装在以下文件夹 \140\DTS 及以下.
<块引用>C:\Program Files (x86)\Microsoft SQL Server\140\DTS
(SQL Server 2019 有文件夹 \150\DTS):
我们遇到了这个问题，并且在将一些包迁移到 SQL Server 2019 时遇到了困难，因为我们必须对 SharePoint 列表执行插入和更新.尝试编写我们自己的自定义 C# 脚本来处理插入/更新不能满足我们的紧急需求，而且不是该语言的专家.
我们考虑或尝试过的事情:
- CozyRoc 是一种昂贵(约 5,000 美元的服务器许可证)解决方案，仅用于与分享点.
- 安装 Codeplex 2017 SharePoint 列表适配器，但它仍然不起作用，我相信需要更多步骤才能使其起作用.
- 比较了 Codeplex 中存档的适配器源/目标的源代码；令人惊讶的是，2014/2017 版本之间的情况非常相似.
- 研究了其他替代方案，但没有什么能真正满足我们的需求.
OData Source本身不能满足我们的要求，因为没有OData Destination
我们的设置:
- 本地计算机程序 SSDT 2017 [针对 2017/2019 SQL Server]
- 我们运行作业的开发/生产环境 SQL Server 2019.
如果您将 SSIS SQL Server 开发/生产环境升级到 2019 &拥有 2012/2014/2016 年执行 SharePoint 列表记录下载/插入/更新的包；您很快就会注意到 2017 SharePoint 列表适配器安装不针对 SQL Server 2019.您可以使用 OData 源下载列表记录，但有时您的业务需要不止于此.
要克服此问题，请尝试以下步骤.这很好地满足了我们的需求，现在正在运行使用来自 SQL Server 2019 作业的 Codeplex 2017 SharePoint 列表适配器的包.
第 1 步:如果您还没有，请获取 2017 SharePoint 列表适配器 这里
- 安装适配器.如果出现错误，很可能需要安装 .NET Framework 3.5，重试安装程序.
第 2 步: 定位路径 >>>在开发/生产中:C:\Program Files (x86)\Microsoft SQL Server\150\DTS
- 以下文件夹必须可用或已创建:Connections、PipelineComponents、UpgradeMappings
- 将以下文件添加到下面的每个文件夹中:
可以在 *C:\Program Files (x86)\Microsoft SQL Server* 中以前的安装文件夹中找到以下文件，例如 \140\DTS.那是我把它们拉出来的地方.但是在下面的步骤中，我们需要将它们添加到 SQL Server 2019 的 \150\DTS 位置.
<块引用>
C:\Program Files (x86)\Microsoft SQL Server\150\DTS\ 连接
- SharePointListConnectionManager.dll
C:\Program Files (x86)\Microsoft SQL Server\150\DTS\ PipelineComponents
- SharePointListAdapters.dll
C:\Program Files (x86)\Microsoft SQL Server\150\DTS\ UpgradeMappings
- MicrosoftSamples.SharepointListAdapters.UpgradeMappings.xml
- MicrosoftSamples.SharepointListAdapters.xml
第 3 步:使用以下脚本在 Powershell 管理模式
注册适配器设置位置C:\Program Files (x86)\Microsoft SQL Server\150\DTS\PipelineComponents"[System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=4.0.0.0, Culture=neutral,PublicKeyToken= LocateYourPublicTokenKeyMayStartWith a b";")$publish = 新对象 System.EnterpriseServices.Internal.Publish$publish.GacInstall("C:\Program Files (x86)\Microsoft SQLServer\150\DTS\PipelineComponents\SharePointListAdapters.dll")适配器注册成功后，从 SQL Server 代理、作业运行您的 SSIS 包.记得在 32 位模式下运行
您现在应该能够在 2019 服务器的 SharePoint 列表中执行以下操作:
- ✓ 将行下载到 SQL 表
- ✓ 在 SharePoint 列表中插入行
- ✓ 修改/更新 SharePoint 列表行
✕ 从服务器作业中删除 SharePoint 列表行对我不起作用，但我会更新这篇文章，以防我找出原因.
以上所有内容也可以在您的客户端计算机上运行.当您在面向 2017/2019 SQL Server 的计算机上构建包时，您应该能够使用步骤 1 到 2 使适配器工作.
How do you make Codeplex 2017 SharePoint List adapter work with SQL Server 2019 dev/prod environments, where you run your SSIS jobs? -- The Codeplex adapter was last updated in 2017 and there appears to be no plans of upgrading it. The adapter doesn't install/register in the newer folders established through SQL Server 2019 because they only install on the following folders \140\DTS and below.
C:\Program Files (x86)\Microsoft SQL Server\140\DTS
(SQL Server 2019 has folder \150\DTS):
We had experienced this issue and struggled with migrating some packages to SQL Server 2019 because we had to perform inserts and updates onto a SharePoint List. Trying to write our own custom C# script to handle inserts/updates didn't meet our urgency needs and are not experts in that language.
Things we considered or tried:
- CozyRoc is expensive (about $5k server license) solution for just interacting with SharePoint.
- Installing Codeplex 2017 SharePoint List Adapter, but it still didn't work, more steps are needed I believe to make it work.
- Compared the Source Code for the Adapter Source/Destination archived in Codeplex; surprisingly it was very much the same between 2014/2017 versions.
- Researched other alternatives, but nothing really fit our needs.
OData Sourcealone doesn't fulfill our requirements because there is noOData Destination
Our set up:
- Local computer program SSDT 2017 [targeting either 2017/2019 SQL Server]
- Dev/Prod environment SQL Server 2019 where we run our jobs.
If you upgraded your SSIS SQL Server dev/prod environments to 2019 & have packages from 2012/2014/2016 that perform SharePoint List Record downloads/inserts/updates; you will soon notice the 2017 SharePoint List Adapter installation does not target SQL Server 2019. You may use OData Source to download list records, but sometimes your business needs require more than that.
To overcome this, try the following steps. This worked well for our needs and are now running packages that use Codeplex 2017 SharePoint List Adapter from SQL Server 2019, Jobs.
Step 1: If you don't have it already, get the 2017 SharePoint List Adapter here
- Install adapter. If it gives error, most likely need to install .NET Framework 3.5, retry installer.
Step 2: Locate path >>> in dev/prod: C:\Program Files (x86)\Microsoft SQL Server\150\DTS
- The following folders must be available or created: Connections, PipelineComponents, UpgradeMappings
- Add the following files into each folder below:
The following files can be found in previous installation folders within *C:\Program Files (x86)\Microsoft SQL Server* such as \140\DTS. That's where I pulled them from. But in the steps below, we need to add them onto the \150\DTS locations for SQL Server 2019.
C:\Program Files (x86)\Microsoft SQL Server\150\DTS\ Connections
- SharePointListConnectionManager.dll
C:\Program Files (x86)\Microsoft SQL Server\150\DTS\ PipelineComponents
- SharePointListAdapters.dll
C:\Program Files (x86)\Microsoft SQL Server\150\DTS\ UpgradeMappings
- MicrosoftSamples.SharepointListAdapters.UpgradeMappings.xml
- MicrosoftSamples.SharepointListAdapters.xml
Step 3: Register the adapter using the script below with Powershell Admin mode
Set-location "C:\Program Files (x86)\Microsoft SQL Server\150\DTS\PipelineComponents"
[System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=4.0.0.0, Culture=neutral,
PublicKeyToken= LocateYourPublicTokenKeyMayStartWith a "b" ")
$publish = New-Object System.EnterpriseServices.Internal.Publish
$publish.GacInstall("C:\Program Files (x86)\Microsoft SQL
Server\150\DTS\PipelineComponents\SharePointListAdapters.dll")
When adapter registers successfully, run your SSIS Package from SQL Server Agent, Jobs. Remember to run in 32-bit mode
You should able to now perform the following actions in a SharePoint List from your 2019 Server:
- ✓ Download rows to SQL Table
- ✓ Insert rows to SharePoint List
- ✓ Modify/Update SharePoint List rows
✕ Deleting SharePoint List rows from Server Job didn't work for me, but I'll update this post in case I figure out why.
All of the above can also work from your client side computer. You should be able to use steps 1 through 2 to make the adapter work when you build packages on your computer targeting 2017/2019 SQL Server.
这篇关于如何将 Codeplex 2017 Sharepoint List Adapter 安装到 SSIS SQL Server 2019(开发/生产环境)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
