3

We have many SSIS imports in our environment all created with SQL / Visual Studio 2008 version. We run a lot of imports from SharePoint and for that we have been using SharePoint List Adapters that we got from CodePlex long time ago. It works great in SSIS 2008. However we are trying to upgrade all of our jobs to run with newer version and using SQL Server Data Tools 2015.

I installed SQL Server Data Tools 2015 on the same machine as SQL Server 2008 Developer Tools. I am still able to use the 2008 version and see the SharePoint list adapters there but they do not show up in the 2015 version. Also the 2015 version doesn't have the SSIS Data Flow Items tab in the "Choose Toolbox Items"

Is there any way for me to add the same SharePoint data flow item components to the 2015 version? I searched and found some suggestions. Tried these steps which did not work unfortunately:

  1. Copied

    • FROM "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents\SharePointListAdapters.dll"
    • TO "C:\Program Files (x86)\Microsoft SQL Server\130\DTS\PipelineComponents\SharePointListAdapters.dll"

  2. Ran this command from administrator CMD prompt

C:\Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools> gacutil -i "C:\Program Files (x86)\Microsoft SQL Server\130\DTS\PipelineComponents\SharePointListAdapters.dll"

  1. Opened SQL Server Data Tools 2005 and refreshed the SSIS toolbox but the SharePoint components did not appear.

Is there anything else that I can try?

Thanks

Hadi
  • 36,233
  • 13
  • 65
  • 124
JetRocket11
  • 302
  • 4
  • 17
  • 1
    you did not indicate what CodePlex (that is now retired) components you used, I suspect if there is a newer version of it that can work with SSDT 2015 you should get it, if not you are simply out of luck with them and need to resort to other options (e.g. CozyRoc or similar) – Arthur Mar 12 '19 at 19:59
  • 1
    I think its the version of .net that these components rely upon. Also, try right click on the SSIS toolbox and see if you can refresh/add new components. Back in 2012, I developed a custom component and at that time, this was the way to make them appear in the SSIS toolbox. But 2012 is eons ago. – rvphx Mar 12 '19 at 20:03

3 Answers3

3

The CodePlex Sharepoint List Adapters for SSIS are obsolete. They cannot be used in newer version of SSIS.

ODATA Components

In newer versions you can use the ODATA components which can be found in SQL Server feature pack. (In SQL server 2016+ they are installed with SSIS)

You can use ODATA Source to read from Sharepoint list.

Third-party Components

Another way is to use third-party components which can be found in Visual studio marketplace.

Helpful links

Update based on the OP Comments

In the comments below the OP mentioned that he found a new version of SSIS Sharepoint List Adapters published on GitHub:

Hadi
  • 36,233
  • 13
  • 65
  • 124
  • 1
    All the responses provided pointed me in the right direction. I found the newer version of SharePointListAdapters that work in SSDT2015 on GitHub. This version requires that the old one is uninstalled first. So the components appeared in the new 2015 version but broke in VS2008 on the same machine. What I did though is copied the SharePointListAdapters.dll from machine with old version to new one + the assembly\GAC_MSIL folders. Final step was GACUTIL to install old DLL and now the components show up in old + new version of SSIS – JetRocket11 Mar 12 '19 at 21:21
  • 1
    GitHub SharePointListAdapters-2017-11-27 -- https://github.com/fan130/mssql-ssis-community-samples/releases/tag/SharepointListAdapters-2017-11-20 – JetRocket11 Mar 12 '19 at 21:22
  • @JetRocket11 i think you should right these comments as an answer. – Hadi Mar 12 '19 at 21:29
  • @JetRocket11 since you didn't write this as your own answer i will update my answer to mention this information because it may helps other users since reading from Sharepoint is an popular task – Hadi Mar 12 '19 at 21:41
2

Here is what I did to get the SharePoint List Adapter SSIS components to work in both 2008 and new 2015 version on the same machine (Windows 10).

I already had SQL Server 2008 Client Tools Developer and SSIS SharePoint List Adapters installed and working. After installing SQL Server Data Tools 2015 on the same device, the following was done to get the SharePoint components working in both:

  1. Make backup of existing GAC_MSIL assemblies. Old version on my computer was 1.0.0.0_f4b3011e1ece9247

    C:\Windows\assembly\GAC_MSIL\SharePointListAdapters
C:\Windows\assembly\GAC_MSIL\SharePointListConnectionManager

  2. Make a backup of SharePointListAdapters.dll from:

    C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents

  3. Uninstall old version of SharePoint List Adapters for SSIS

  4. Download and install new version from:

After installing - open SSDT 2015 and refresh the SSIS toolbox to make sure they SharePoint Source and Destination components appear under the Common section.

  1. To get these components working in VS2008 on same device:

    • Copy the GAC_MSIL assemblies which were backed up earlier back to the folders which now should only have the 1.2.x folder in it. End result is that the GAC_MSIL folders should have the old 1.0.x folder and the 1.2.x

    • Copy the backed up SharePointListAdapters.dll back to C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents You can re-name or overwrite the newer version that got installed there. I renamed it.

    • Open CMD prompt as administrator
    • Switch to C:\ prompt first and then change directory using this command: CD Program Files (x86)\Microsoft SDKs\Windows\v8.1A\bin\NETFX 4.5.1 Tools

    • Run the following command:

      gacutil -i "C:\Program Files (x86)\Microsoft SQL Server\100\DTS\PipelineComponents\SharePointListAdapters.dll"

At this point the SharePoint List Adapters in VS2008 should be functional once again. The SP adapters in SSDT 2015 should be available as well at the same time.

Hope this helps

Hadi
  • 36,233
  • 13
  • 65
  • 124
JetRocket11
  • 302
  • 4
  • 17
1

Not sure if anyone else has ran into this issue with SQL Server 2019, but as of last night, finally got this Sharepoint List Adapter 2017 adapter to work with SSIS SQL Server 2019 :-). I'll be working on documenting the process for it, but it works 100%. Using SSDT 2017, I was able to target SQL Server 2017/2019 and in both scenarios the package ran successfully. Stay tuned, more to come soon; hopefully it helps someone. Update with Documentation link/answer: Confirmed it also works if you are using Visual Studio Pro 2019. How to install Codeplex 2017 Sharepoint List Adapter onto SSIS SQL Server 2019 (dev/prod environment)

Goku
  • 197
  • 1
  • 7