0

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 Source alone doesn't fulfill our requirements because there is no OData 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.
Goku
  • 197
  • 1
  • 7

1 Answers1

2

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.

Goku
  • 197
  • 1
  • 7
  • Can you please explain what you mean by LocateYourPublicTokenKeyMayStartWith? – gilesrpa Mar 22 '22 at 13:15
  • @gilesrpa if you go to c:\windows\assembly you'll find System.EnterpriseServices along with the Public Key Token - leave version at 4 though. I found step 3 wasn't necessary as the DLL was already registered in the GAC. Steps 1 and 2 were enough for me, thanks @Goku! – t0mgerman May 09 '23 at 15:46