i have an SSIS Package project that was designed in 2015 and was working fine and deployed on the windows server 2012 R2 targeting SQL Server 2014.Now we are migrating to windows server targeting SQL Server 2019.I took the package and opened it in visual studio 2019 and it gave issues with the sharepointlist component not being recognized.
-
i followed the link(How to install Codeplex 2017 Sharepoint List Adapter onto SSIS SQL Server 2019 (dev/prod environment)) to resolve my issue with the component by installing the SQLSharepointListAdapter onto my server and registering the adapter as stated in the link.this worked and my sharepointList component came to life.
-
I then changed the Target server of the Project from SQL Server 2014 to SQL Server 2019 and run the packages it contains in Visual studio 2019 and everything looks good when i build and execute it’s successful reading from sharepoint list and writing data to a files.
-
I then deploy my Project packages to the same server that Visual studio is running on,In MSSMS Under Integrated Services CatalogSSISDB.When i try validating/Executing one package,that is when the error below appears.
MicrosoftSamples.SharepointListSource" could not be loaded. The exception was:Could not load type 'Microsoftsamples.sharepointlistsource' from assembly microsoft.sqlserver.pipelinehost,version 15.0.0.culture=neutral,publicKeyToken=8985dcd8080cc91.
Please Any help or suggestions is appreciated.
My Tools i’m using and installed
- Visual Studio 2019
- Microsoft SQL Server Integration Services Projects version 15.0.2
- .Net Framework 4.6.2 SDK
- SSIS SarepointList Adapters for SQL server integration services versin 1.0.0.0
- Windows Server 2019 standard
i registered my SharepointList Adater using sharepoint script below
Set-location "C:Program Files (x86)Microsoft SQL Server150DTSPipelineComponents"
[System.Reflection.Assembly]::Load("System.EnterpriseServices, Version=4.0.0.0, Culture=neutral,
PublicKeyToken=b03f5f7f11d50a3a")
$publish = New-Object System.EnterpriseServices.Internal.Publish
$publish.GacInstall("C:Program Files (x86)Microsoft SQL
Server150DTSPipelineComponentsSharePointListAdapters.dll")
I tried using the ODataSource component provided by microsoft but it has limitations as there is no ODataDestination component and also does not read lookup Columns from the SharepointList site which makes it ommit data so this is not a quick viable solution now in my case.
I also tried checking the version of microsoft.sqlserver.pipelinehost but i cannot seem to locate this.i believe this could be a versioning issue but i dont know what could potentially be clashing.
Please Any help or suggestions is appreciated.