SharePointListAdapter for SQL2014 (beta) is released!

Coordinator
Jun 26, 2015 at 2:29 AM
Hi all,

Sorry for the long wait. Now it's coming!

I am pleased to announce that SharePointListAdapter for SQL2014 (beta) is released. Now you can leverage on this adapter to design packages connecting to SharePoint list running on SQL Server 2014! You can get the msi at here:
https://sqlsrvintegrationsrv.codeplex.com/downloads/get/1465238

However it's still a beta version and has a known issue: packages using SharePointListAdapter for lower SSIS version cannot be successfully upgraded to SSIS 2014 format. Seems this issue has already existed. However you can workaround this issue by manually edit the dtsx file by changing the assembly version from "1.2012.0.0" to "1.2014.0.0". I'll fix this issue in next release.

Please let me know if you have any feedback or requirements. Enjoy!
Jun 26, 2015 at 8:31 AM
Edited Jun 26, 2015 at 9:26 AM
Hello,

is it now possible to use the Adapter on SSIS Visual Studio 2013? I am not able to find the SharePoint Destination in the "SSIS Toolbox" in Visual Studio after installing this Version. Unfortunatly there is no "Choose Toolbox Items" MenuItem available. Please help.

EDIT
I found the destination and the source in the SSIS Toolbox, group "Common", sorry my mistake.

But I was not able to connect to the list since sharepoint is configured to use NTLM not Kerberos.
Exception: "The HTTP request is unauthorized with client authentication scheme 'Negotiate'. The authentication header received from the server was 'NTLM'."

Since i have no chance to ever change the sharepoint configuration: Is it possible to make the adapter use NTLM?
Is it possible to use another sharepoint culture than en-US now? I need to use de-DE.

Thanks for your help!

Andreas
Coordinator
Jun 29, 2015 at 1:36 AM
Derrick0815 wrote:
Hello,

is it now possible to use the Adapter on SSIS Visual Studio 2013? I am not able to find the SharePoint Destination in the "SSIS Toolbox" in Visual Studio after installing this Version. Unfortunatly there is no "Choose Toolbox Items" MenuItem available. Please help.

EDIT
I found the destination and the source in the SSIS Toolbox, group "Common", sorry my mistake.

But I was not able to connect to the list since sharepoint is configured to use NTLM not Kerberos.
Exception: "The HTTP request is unauthorized with client authentication scheme 'Negotiate'. The authentication header received from the server was 'NTLM'."

Since i have no chance to ever change the sharepoint configuration: Is it possible to make the adapter use NTLM?
Is it possible to use another sharepoint culture than en-US now? I need to use de-DE.

Thanks for your help!

Andreas
Hi Andreas,

Thanks a lot for your feedback. I've logged down your two requirements:
  1. Make the adapter support NTLM
  2. Make the adapter support other cultures
However, I'm still new to this project and I'm also working on other more critical features of SSIS, so your requirements may not come true, or maybe take a long time, but I promise you don't need to wait another two years to get the answer whether your requirements are doable :)

Thanks again!
Bo
Jul 22, 2015 at 12:56 PM
Hi fan103,

I am facing the same problem as Derrick0815 ("The HTTP request is unauthorized with client authentication scheme 'Negotiate'. The authentication header received from the server was 'NTLM'.").

Until now I've been working with the 2012-beta. Worked fine with SQL 2012 and SharePoint 2010. No we've got SQL 2014 and SharePoint 2013. I still used the 2012-beta, worked fine in Visual Studio, I even could run the package in debug-mode. But when I tried to publish the project (or tried to import my package in Mangement Studio / Integration Services), I ran into an error.

I was very happy as I saw that a new beta is available now (thanks a lot for it!!), hoping that it would solve my problem. Unfortunately I can't use it, because of the authentication problem. I reinstalled the 2012-beta again, so i can run my package in Visual Studio at least.

It would be realy great, when the adapter could support NTLM (again).

Thanks a lot for your help.

Mikel
Jul 23, 2015 at 1:13 AM
Edited Jul 23, 2015 at 1:36 AM
I am also getting " The HTTP request is unauthorized with client authentication scheme 'Negotiate'. The authentication header received from the server was 'NTLM'."
I am connecting to a site on sharepoint.com using a SharePoint List Source.
I have tried using:
They all gave a similar error (error above is from the 2015 beta).

The SharePoint Server 2013 Client Components SDK does not have this problem, maybe you can update the connection to use that?

Also, the 2012 beta name looks like a typo, since it was uploaded on 2013/01/28.
Jul 30, 2015 at 11:19 AM
I too get the the same NTLM issue :-(.
I'm using Visual Studio Data Tools 2013 for SQL Server 2014; connecting the SharePoint 2010 Site.
The HTTP request is unauthorized with client authentication scheme 'Negotiate'. The authentication header received from the server was 'NTLM'.
This issue isn't happening with earlier version of SharePoint List Adaptors whcih works fine with SQL Server Business Intelligence Studio 2008R2.
It would be really great, if the adapter can support this NTLM on this SQL 2014 version.

Thanks in advance,
~Yuva
Coordinator
Jul 31, 2015 at 1:55 AM
Hi Guys,

Sorry for late response. Actually this version is only a rebuild version to support SSIS 2014. I didn't change any existing code. This is really weird...
Anyway, I promise I'll solve this issue later.

Thanks
Aug 4, 2015 at 2:10 PM
Edited Aug 4, 2015 at 2:10 PM
I have two problem:
1) ("The HTTP request is unauthorized with client authentication scheme 'Negotiate'. The authentication header received from the server was 'NTLM'.").
2) Source code after update to 2014 not completely checkedin. Many files in 2014 folder is lost.
Coordinator
Aug 6, 2015 at 5:10 AM
lifecorp wrote:
I have two problem:
1) ("The HTTP request is unauthorized with client authentication scheme 'Negotiate'. The authentication header received from the server was 'NTLM'.").
2) Source code after update to 2014 not completely checkedin. Many files in 2014 folder is lost.
Hi lifecorp,

For your second problem, it is unnecessary to check in files in 2014 folder. If you look at makeinstaller.cmd, you can find that all src files in Deploy folder will be overwritten by files from Src folder
Aug 6, 2015 at 1:38 PM
@fan130, thanks for your work on this. Any updates on the NTLM authentication issue? We cannot use this component until that's fixed, too.

Thanks!
Aug 20, 2015 at 12:23 AM
Hi,

So we know when this issue will be fixed?

I should have checked this before upgrading!

Thanks
Aug 28, 2015 at 4:42 PM
Hello,

Just wondering if there has been any progress with the NTLM issue?

Thanks for all the work!
Aug 31, 2015 at 11:31 AM
Hi,

I am also experincing the NTLM issue with SQL2014. Would love to see a fix for this.

Really appricate the support and work done in the previous versions!
Coordinator
Sep 1, 2015 at 2:29 AM
Hi everyone,

Sorry for the late response. I know you are suffering from the NTLM issue. And I really want to help you solve this issue, but currently the work on SSIS 2016 is really hot, so I don't have enough time to investigate the issue.

However, I'm wondering that the code for 2012 and 2014 is the same, why the issue only show up on 2014. So could you guys help me to do the flowing verification:
  1. Make sure your machine has SSIS 2012 and 2014 installed
  2. Remove all previous version of SharePoint list adapter
  3. Install the latest beta version
  4. Check if the version for 2012 can work with NTLM while the version for 2014 cannot
Thanks a lot!
Sep 3, 2015 at 9:30 AM
Hi,

Assuming the fix is going to work, but the only version of SSIS that I'm going to use is the SSIS 2014. Is there other suggestions you can give to fix the NTLM issue?
If none can I just know just an estimate on when the fix for this can be released?

Thank You!
Sep 7, 2015 at 10:09 AM
Hi fan130,

sorry for the late response - i'm just back from holiday.

I think I can manage the required testing during this week with an virtual machine.

Let you know the results as soon as possible.
Sep 9, 2015 at 11:25 AM
Edited Sep 16, 2015 at 6:37 AM
Hi fan130,

here are the results of my testing:

Environment:
Windows Server 2008 R2, running on VMware Workstation

Step 1:
I have installed:
SQL Server 2012, including Integration Services, Visual Studio 2010
SharePoint Foundation 2013
SharePoint List Adapters 2012 (2012-01-28)

I made a little SSIS-Package, just writing some data into a simple SharePoint-List.
Development in Visual Studio: ok
Running in Visual Studio: ok
Publish: ok
Running in SQL Agent: ok

Step 2:
Remove SharePoint List Adapters 2012
Install SharePoint List Adapters 2014 (2015-06-25)
Still running SQL 2012.
Restart Server.

Open Visual Studio, open Package.

And I get the NTLM-Error! (Also when I try to run the package in SQL Agent.)

Step 3:
Install SQL Server 2014 – Integration Services (not the DB-Engine)
Install SQL Server Data Tools for VS 2013
Remove SharePoint List Adapters 2014
Install SharePoint List Adapters 2014 (2015-06-25)
Restart Server.

Open VS 2013, create new Project / package …
create SharePoint List Destination in my dataflow
fill out SiteListName etc., klick on “Refresh” – I get the NTLM-Error.

Open Visual Studio 2010, open my package – I get the NTLM-Error.

Step 4:
Remove SharePoint List Adapters 2014
Install SharePoint List Adapters 2012 (2012-01-28)
Restart Server.

Open Visual Studio 2010, open my package.
No Error, I can run the package in Visual Studio and in SQL Agent.

Open Visual Studio 2013, open my package.
I get a different error, because the SharePoint List Adapters and the SPCred Connector are no longer available.
This is funny, because this works on my production server. The production server is now running SQL 2014, Visual Studio 2013 (edited Sep 16, sorry I wrote 2010 first), SharePoint List Adapters 2012, where I can develop and run my package in Visual Studio without any problem, but I am running into an error when trying to publish the package (for running it in sql agent). The main difference: my production machine is just running SQL 2014 with VS 2013, while the server I’ve been testing now is running SQL 2012 and SQL 2014 and VS2010 and VS2013.

I hope this helps.

If you need more information just let me know.

Looking forward to read from you soon.

Mikel
Coordinator
Sep 16, 2015 at 8:11 AM
Hi Mikel,

Sorry for the late response. Thanks a lot for your detailed test, that's really helpful!
I have found that the issue come from the SharePointUtility.dll, after I replace this dll with the one in the old version (2012-01-28), the NTLM issue has gone. I have uploaded the SharePointUtility.dll, please run the command "gacutil /i <SharePointUtility.dll path>" after you install the new version (2015-06-25), after that, the NTLM issue should be fixed.

Currently I still don't have enough time on this project. I'll investigate the root cause of the issue later. Please let me know if the issue still exists for you.

Thanks
Sep 21, 2015 at 8:52 PM
Hi fan130,

thanks a lot for your help. The workaround works fine on my testing environement. But I had to install Visual Studio Express for running gacutil.exe (as I'am not a true developer i just have installed the BIDS-Version of Visual Studio from SQL Server).

But I can't use this workaround on my customers production server, as there is no Visual Studio installed and i can't do this on his machine. Is there another possibility for getting the sharepointutility.dll into the GAC without using gacutil.exe?

Thanks, Mikel
Coordinator
Sep 22, 2015 at 3:31 AM
Hi Mikel,

Actually you can simply copy the new dll to the GAC folder. Please use the following commands to backup the old dll and replace it with the new dll:

move /y %windir%\assembly\GAC_MSIL\SharePointUtility\2.0.0.0__f4b3011e1ece9d47\SharePointUtility.dll %windir%\assembly\GAC_MSIL\SharePointUtility\2.0.0.0__f4b3011e1ece9d47\SharePointUtility.dll.old

copy /y <new dll location> %windir%\assembly\GAC_MSIL\SharePointUtility\2.0.0.0__f4b3011e1ece9d47

I'll try to build a new installer later, but currently please workaround the issue by replacing the dll. Sorry for the inconvenience.

Thanks,
Bo
Oct 1, 2015 at 7:34 AM
Hi Bo,

thanks for your help. The move / copy statements worked fine.

The good news: no NTLM-Error anymore. So I can work with the adapters in Visual Studio, I can run my package within Visual Studio. And I can publish my package to the Integration Services Catalog.

The bad news: I can not run my package by executing it within the Integration Services Catalog in Management Studio or by running it from sql agent. I get an error saying (I try to translate the message) that the COM-object can not be converted to the interface-type "Microsoft.SqlServer.DTs.Pipeline.Wrapper.IDTSComonentMetaData100" because that interface is not supported. Here is the whole error-message (it's a german installation):

Error:
System.InvalidCastException: Das COM-Objekt des Typs
"System.__ComObject" kann nicht in den Schnittstellentyp
"Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSCompone
ntMetaData100" umgewandelt werden. Dieser Vorgang
konnte nicht durchgeführt werden, da der QueryInterface-
Aufruf an die COM-Komponente für die Schnittstelle mit
der IID "{887BD061-82D4-4F06-A222-337D42E7F896}"
aufgrund des folgenden Fehlers nicht durchgeführt werden
konnte: Schnittstelle nicht unterstützt (Ausnahme von
HRESULT: 0x80004002 (E_NOINTERFACE)).
bei
System.StubHelpers.StubHelpers.GetCOMIPFromRCW
(Object objSrc, IntPtr pCPCMD, IntPtr& ppTarget,
Boolean& pfNeedsRelease)
bei
Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSComponen
tMetaData100.get_CustomPropertyCollection()
bei
Microsoft.Samples.SqlServer.SSIS.SharePointListAdapter
s.SharePointListSource.PerformUpgrade(Int32
pipelineVersion)
bei
Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHos
t.HostCheckAndPerformUpgrade
(IDTSManagedComponentWrapper100 wrapper, Int32
lPipelineVersion)


The server has been restarted last night, but this did not help.

Any ideas?

Thanx, Mikel
Oct 7, 2015 at 7:57 PM
Hi everybody,

did anyone else try the SP-Adapters for SQL Server 2014 with the new dll? Does everything work fine, including running the package with SQL Agent? Or did anyone face the same problem as I did?

Thanx, Mikel
Coordinator
Oct 10, 2015 at 7:48 AM
Hi Mikel,

Sorry for the late response as I just come back from public vacations. I've tried to execute a package with SP-Adapter on SSIS catalog, but I could not repro your problem. And from the error message, it seems to be more like a common error instead of a specific error related to SP-Adapters. Have you confirmed that this error only happens when a SP source or destination is used in the package?

Thanks,
Bo
Oct 12, 2015 at 8:27 AM
Hi Bo,

thanks for your response.

There are several packages running on this server daily since July, no problems. The error only happens when a SP source is used or a SP destination.

I tried running the package via command line (dtexec.exe), same problem.

I created a new package with just one simple data flow task including SP source, same problem.

I removed the SPAdapters and reinstalled them once again, same problem.

Quite frustrating, I need to get it work till end of october.

Tanks, Mikel
Coordinator
Oct 12, 2015 at 8:53 AM
Hi Mikel,

The GUID "{887BD061-82D4-4F06-A222-337D42E7F896}" is for one of the SSIS interfaces IDTSComponentMetadata100, but is for SQL Server 2012, SQL 2014 uses a different GUID for this interface.

Currently I don't know the exact root cause but I think your environment may have something wrong. Could you firstly check if you have installed SharePointListAdapters.dll with version number 1.2014.0.0? This dll should be installed in three folders:

C:\Program Files (x86)\Microsoft SQL Server\120\DTS\PipelineComponents
C:\Program Files\Microsoft SQL Server\120\DTS\PipelineComponents
C:\Windows\Microsoft.NET\assembly\GAC_MSIL\SharePointListAdapters\v4.0_1.2014.0.0__f4b3011e1ece9d47
Oct 12, 2015 at 11:55 AM
Hi Bo,

thanks for your help - I could solve this issue with your hints.

Files in c:\program files (x86) and c:\program files were ok.

But in c:...GAC_MSIL\SharePointListAdapters I have two folders. One v4.0_1.2014.0.0... and one v4.0_1.2012.0.0... I tried to rename the ..2012... folder, but I couln't (with error "folder is in use by another program..."). I renamed the sharepointlistadapters.dll in the ...2012... folder, started my package in Management Studio/Integration Services Catalog, got the same error. AND: my renamed file in the ...2012... folder was again named sharepointlistadapters.dll. So it was obviously, that the system is using the wrong dll. I renamed the dll again and copied the sharepointlistadapters.dll from the v4.0_1.2014... folder to the v4.0_1.2012...folder. And now it works perfectly.

The installation is not very clean, I will try to remove the ...2012.. folder later (it's not my machine, and some users are working on it, so i can't stop any services now...). But it works.

Thanks a lot for your help.

Mikel
Coordinator
Oct 13, 2015 at 1:28 AM
Hi Mikel,

Happy to know your package finally works, but I still cannot understand why your SQL 2014 SSIS catalog will load dll from 2012 folder. Actually if your machine only have SQL 2014 installed, the SP-Adapter installer will not install the dll for SQL 2012.

If you are interested, I think you can do following further investigation:
  1. Check if your SQL instance is really a SQL 14 instance
  2. Try to install the SP-Adapter again by using "Custom" installation, which will list which versions of the dll will be installed
  3. Using Process Explorer to check which SP-Adapter dll will DTExec.exe or ISServerExec.exe load (from 2012 or 2014 folder)
Thanks,
Bo
Oct 14, 2015 at 7:19 AM
Edited Oct 14, 2015 at 7:20 AM
Hi Bo,

you are right, there is not only SQL 2014 installed on this machine, but also SQL 2012 (at least parts of it). As you know this is not "my" machine (i am not the admin, i did not install SQL Server) i don't know the exact reason for this.

Facts:
In program files (386)\microsoft sql server\110\DTS\PipelineComponents there is the 2012 version of sharepointlistadapters.dll.
In program files (386)\microsoft sql server\120\DTS\PipelineComponents there is the 2014 version of sharepointlistadapters.dll.
In program files\microsoft sql server\110\DTS\PipelineComponents there is no sharepointlistadapters.dll.
In program files\microsoft sql server\120\DTS\PipelineComponents there is the 2014 version of sharepointlistadapters.dll.

The SQL Server instance i am working with is SQL 2014 (12.0.4213.0).
But when I am working with Visual Studio 2012 (BIDS) it seems that VS is using the SQL 2012 environement.

My yesterday's workaround, copying the 2014 version of sharepointlistadapters.dll into the 2012 folder, works only for running the package in sql agent. When i try to open the package in VS i get the same error, for working there i need to activate the 2012 version of the dll in the 2012 folder again. So depending on what i want to do i need the 2014 version of the dll (for running in sql agent) or the 2012 version of the dll (for working in VS).

So actually my problem seems to be the Visual Studio installation / configuration, I have to tell Visual Studio to work with SQL 2014.

The SharepointListAdapters work fine.

Thanks a lot for your help,
Mikel
Nov 4, 2015 at 10:44 AM
Thank you! I managed to get my environment working by manually checking the sharepointlistadapters.dll's are the correct version and in the correct location and updating the SharePointUtility.dll to the latest one provided.
Dec 8, 2015 at 5:40 PM
fan130 wrote:
Hi all,

Sorry for the long wait. Now it's coming!

I am pleased to announce that SharePointListAdapter for SQL2014 (beta) is released. Now you can leverage on this adapter to design packages connecting to SharePoint list running on SQL Server 2014! You can get the msi at here:
https://sqlsrvintegrationsrv.codeplex.com/downloads/get/1465238

However it's still a beta version and has a known issue: packages using SharePointListAdapter for lower SSIS version cannot be successfully upgraded to SSIS 2014 format. Seems this issue has already existed. However you can workaround this issue by manually edit the dtsx file by changing the assembly version from "1.2012.0.0" to "1.2014.0.0". I'll fix this issue in next release.

Please let me know if you have any feedback or requirements. Enjoy!
Hi fan130!
Sorry to hijack the thread a bit, but I have a REALLY important burning community question: are you looking at adding Office 365/Sharepoint Online Authentication support to this project? Right now, these adapters can't be used with O365 due to the authentication method deployed by Microsoft. Before I start looking all around the worldwide web for another solution (or making a lofty attempt at creating my own), I thought I'd ask first. :-)

Thanks so much for your contributions to this project - its made me look like a hero with our On-Prem Sharepoint solution.
Jan 8, 2016 at 8:19 PM
I'm running the September beta and hitting issues. Specifically, the new component is causing Visual Studio to report that it can't read the xml, when trying to run the package under debug. I posted an issue to the issue tracker about i.

Pretty much a show stopper here.
Jan 28, 2016 at 4:32 AM
I'm having the same NTLM problem with my system (not installed to a server).

Visual Studio 2013
SQL Server 2014

But the database being connected to is SQL 2012 and SharePoint used is only 2007. I'm just having a problem installing the adapter without the SQL 2014 being installed.

Every time I try and install just the SSDT, it always comes out as 2014.

Sorry, I'm just new to this and I'm not sure what's the best approach to make this work.

TIA
Jan 28, 2016 at 4:36 AM
bmcomp wrote:
Thank you! I managed to get my environment working by manually checking the sharepointlistadapters.dll's are the correct version and in the correct location and updating the SharePointUtility.dll to the latest one provided.
Hi, can you expand on that? what is the correct version and where should I put the SharePointUtility.dll? I found mine under System32... :(
Feb 4, 2016 at 3:22 PM
I'm running the September beta and hitting issues. Specifically, the new component is causing Visual Studio to report that it can't read the xml, when trying to run the package under debug. I posted an issue to the issue tracker about it.
Following up. Apparently the adapter does not work in 64-bit mode. I ran it in 32-bit mode just fine.

When will there be a 64-bit version available?
Jul 7, 2016 at 2:24 PM
Hello,

Many thanks to you fan130.
Im currently migrating to SP2013.
I've used SharePointListAdpater on a SP2010 farm with SQLServer2008R2.

The new environnement is setup with SP2013 on two serv's on WServer2012 and with a SQL Server v2014.

I've migrated SSIS packages on the dev environnement using Visual Studio 2012 with SharePointListAdapter V2012 with the help of the faq.
Using dev env in VS2012, the SSIS projects are working properly.

I've installed SharePointListAdapter for SQL2014 2015-06-25 beta on the SQL Server 2014 and when executing package in SSMS the NTLM error appears.

So i followed the instructions :
I've downloaded SharePointListAdapter 2012-01-28 beta, extracted from the msi the SharePointutility.dll.
I ran the command prompts
  • move /y %windir%\assembly\GAC_MSIL\SharePointUtility\2.0.0.0__f4b3011e1ece9d47\SharePointUtility.dll %windir%\assembly\GAC_MSIL\SharePointUtility\2.0.0.0__f4b3011e1ece9d47\SharePointUtility.dll.old
    And
  • copy /y <Path to the SharePointUtility dll extracted from 012-01-28 beta> %windir%\assembly\GAC_MSIL\SharePointUtility\2.0.0.0__f4b3011e1ece9d47
Since the dll was replaced by the old one, i could run my SSIS packages properly.
In my case i just need to replace the assembly version in dtsx files from 1.2012.0.0 to 1.2014.0.0 when deploying the packages in SSMS until i install SharePointListAdapter V2014 on my dev env.

Thank you !

Sylvain