none
SSIS Package to connect to SharePoint Online via OData connector

    Dotaz

  • Hello,

    I'm trying to develop an SSIS package to connect to a SharePoint Online instance via the OData connector. I've been through a fair bit of pain surrounding the OData object to ensure it's for the correct SQL version as the intended production server and the correct bitness (x86 vs x64). I was finally able to develop a solution which worked in my development environment (Visual Studio 2017 on a Windows 10 client). I'm now in the process of trying to deploy the package to the 'file system' and create a SQL Job to trigger the package but I'm running into various issues.

    The first issue was that when using an OData connection authentication method of 'Use this user name and password' (as per my working development environment) and a package ProtectionLevel of DontSaveSensitive I needed to pass in a password each time for the connection to SharePoint. This hasn't worked as I don't know how to pass in the password to the package at runtime. This isn't an ideal solution as I don't particularly want to be working with passwords.

    I then tried changing the OData connection properties so that it authenticated using 'Use Windows Authentication'. I was unable to test this in the development environment as I couldn't get VS to run under the necessary account. This necessitated me creating a SQL Credential and Proxy to run the Job Step under. This would be my preferred solution as I don't have to save a password in the package, but this too failed.

    I then tried changing the OData authentication method back to 'Use Windows Authentication' and the package ProtectionLevel to EncryptSensitiveWithPassword. I altered the Job Step 'Command Line' to include the /DECRYPT keyword. But this too failed.

    Has anyone managed to connect to an Online (Cloud) SharePoint instance from an SSIS package running as a SQL Server job? If so, how did you manage it?

    čtvrtek 31. května 2018 10:20

Odpovědi

Všechny reakce

  • Hi renMike,

    Having the combination of package protection level set to EncryptSensitiveWithPassword and the authentication method 'Use this user name and password' should work without the need of the proxy account.


    Arthur

    MyBlog


    Twitter

    čtvrtek 31. května 2018 13:42
    Moderátor
  • Arthur,

    I to thought this but I struggled to get it to work. I felt like I had to supply the password in the Job Steps 'Command Line' via the /DECRYPT keyword. Whenever I tried this I found that the manual change to the command Line was not saved. The job then failed to run. In your experience, is the supplying of the password necessary or is it already encrypted in the package?

    Thanks,

    Mike

    čtvrtek 31. května 2018 13:48
  • the /Decrypt command line option in the Agent's SSIS Task should should not be used

    If you run it as a scheduled job then use the Rely on Server for Storage protection level.

    Image result for Rely on Server for Storage protection level


    Arthur

    MyBlog


    Twitter

    čtvrtek 31. května 2018 14:46
    Moderátor
  • Ah,

    I'm deploying to the 'File System' and not the msdb, so that option isn't available to me. Does that alter your recommendation?

    čtvrtek 31. května 2018 15:21
  • VS 2017 supports only packages that do NOT install into MSDB.

    There is something wrong perhaps with the tooling then here.

    What SSIS version do you need to target?


    Arthur

    MyBlog


    Twitter

    čtvrtek 31. května 2018 20:53
    Moderátor
  • Hi renMike,

    A workaround is you can execute the package via CMD with CmdExec type in job steps so that you can add /DECRYT parameter to decrypt the package which stored in File system.

    See: DTexec command examples

    Regards,

    Pirlo Zhang 


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    pátek 1. června 2018 3:22
    Moderátor
  • I'm targeting SQL Server 2014.
    pondělí 4. června 2018 9:41
  • I'm still having no joy with this.

    Any other ideas welcomed at this stage.

    středa 6. června 2018 14:00
  • What method did you try last? What is the error? What was the protection level applied?


    Arthur

    MyBlog


    Twitter

    středa 6. června 2018 14:20
    Moderátor
  • The latest version is a VS 2017 solution targeting SQL Server 2014 and creates a package. The protection level is EncryptSensitiveWithPassword. The package is deployed to the file system via a manifest. When running the job from SSMS the result is an error:

    Date  06/06/2018 15:30:34
    Log  Job History (Extract Mobile Numbers from SharePoint)
    Step ID  1
    Server  <<ServerName>>
    Job Name  Extract Mobile Numbers from SharePoint
    Step Name  Run SSIS package
    Duration  00:00:08
    Sql Severity 0
    Sql Message ID 0
    Operator Emailed 
    Operator Net sent 
    Operator Paged 
    Retries Attempted 0
    Message
    Executed as user: <<DomainUser>>. Microsoft (R) SQL Server Execute Package Utility  Version 12.0.4100.1 for 64-bit  Copyright (C) Microsoft Corporation. All rights reserved.    Started:  15:30:34  Error: 2018-06-06 15:30:38.08     Code: 0x00000001     Source: ExtractMobileNumbersFromSharePoint Connection manager "SharePoint Online" (OData Connection Manager)     Description: Failed to initialize SharePoint Client Runtime. The exception message was: Could not load file or assembly 'Microsoft.SharePoint.Client.Runtime, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.  End Error  Error: 2018-06-06 15:30:38.10     Code: 0xC001401D     Source: ExtractMobileNumbersFromSharePoint      Description: Connection "SharePoint Online" failed validation.  End Error  Error: 2018-06-06 15:30:39.68     Code: 0x00000001     Source: ExtractMobileNumbersFromSharePoint Connection manager "SharePoint Online" (OData Connection Manager)     Description: Failed to initialize SharePoint Client Runtime. The exception message was: Could not load file or assembly 'Microsoft.SharePoint.Client.Runtime, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.  End Error  Error: 2018-06-06 15:30:41.99     Code: 0xC020801F     Source: Get Subsid mobile phone data Subsidiary mobile numbers [74]     Description: Cannot acquire a managed connection from the run-time connection manager.  End Error  Error: 2018-06-06 15:30:42.02     Code: 0xC0047017     Source: Get Subsid mobile phone data SSIS.Pipeline     Description: Subsidiary mobile numbers failed validation and returned error code 0xC020801F.  End Error  Error: 2018-06-06 15:30:42.04     Code: 0xC004700C     Source: Get Subsid mobile phone data SSIS.Pipeline     Description: One or more component failed validation.  End Error  Error: 2018-06-06 15:30:42.04     Code: 0xC0024107     Source: Get Subsid mobile phone data      Description: There were errors during task validation.  End Error  DTExec: The package execution returned DTSER_FAILURE (1).  Started:  15:30:34  Finished: 15:30:42  Elapsed:  7.36 seconds.  The package execution failed.  The step failed.

    It seems to be having difficulty starting the Runtime client. I've been through various versions of the OData control and believe that I have a 64bit control for SQL Server 2014 installed (eventually!).

    Do I need another component for SharePoint on the server? It works on my development PC but I don't recall installing anything specific for SharePoint. Perhaps the required components were installed when I installed VS?

    středa 6. června 2018 14:36
  • Hi renMike,

    based on what I see you need to install the OData SP Connector.


    Arthur

    MyBlog


    Twitter

    středa 6. června 2018 15:10
    Moderátor
  • Please excuse my ignorance, but what is that and how does it differ from the OData Connector?
    Where can I get more information/download that object?
    středa 6. června 2018 15:23
  • Do I need to download and install the 'SharePoint Online Client Components SDK' onto the SQL Server which will be running the SSIS package? I'm (obviously) confused as to what is required on the server to allow an SSIS package to connect to SharePoint Online. I had thought that the OData component was the only prerequisite, and this thought was reinforced by developing and successfully testing my package on my client PC using VS.

    What else, other than the package and the OData component, is required on the server?

    čtvrtek 7. června 2018 9:04
  • I took a punt and tried installing the sharepointclientcomponents_16-6906-1200_x64-en-us.msi from the Microsoft Download centre onto the server. This installs the Microsoft.SharePoint.Client.Runtime.dll (and others) into the C:\Program Files\Common Files\ microsoft shared\Web Server Extensions\16\ISAPI\ folder.

    Running my package from a SQL Server job still fails, but now with a much shorter message:

    Date  07/06/2018 10:30:50
    Log  Job History (Extract Mobile Numbers from SharePoint)
    Step ID  1
    Server  <<ServerName>>
    Job Name  Extract Mobile Numbers from SharePoint
    Step Name  Run SSIS package
    Duration  00:00:19
    Sql Severity 0
    Sql Message ID 0
    Operator Emailed 
    Operator Net sent 
    Operator Paged 
    Retries Attempted 0
    Message
    Executed as user: <<Domain user>>. Started:  10:30:50  Finished: 10:31:09  Elapsed:  19.015 seconds.  The package execution failed.  The step failed.

    I'm not sure if this is progress or not.

    čtvrtek 7. června 2018 9:38
  • Well that didn't last long. I found that logging had chosen to start working and was able to find the logs in the msdb.dbo.sysssislog table. This essentially told me the same message "The exception message was: Could not load file or assembly 'Microsoft.SharePoint.Client.Runtime, Version=15.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of its dependencies. The system cannot find the file specified.".

    I've installed version 16.0.6906.1200 onto the server using the msi described above and the package is looking for version 15.0.0.0. I'm thinking that i'll need to either install 16 on my development client and ensure that that is used by the package or install 15 on the server.

    Does this sound right?

    čtvrtek 7. června 2018 11:25
  • You are right, it expects ver 15.

    Arthur

    MyBlog


    Twitter

    • Označen jako odpověď renMike úterý 10. července 2018 9:19
    čtvrtek 7. června 2018 13:30
    Moderátor
  • Having uninstalled 16, installed 15 and rerun the job I now get the error:

    Connection "SharePoint Online" failed validation.

    Looking at the versions it seems that 15 is intended for SharePoint 2013 and 16 for the newer version. Since i'm interacting with a Cloud instance I would think (it's just a guess) that it'd be the newer version and so I should be targeting 16. Is this right? If so, how do I build 16 into my package rather than 15? I have both installed on my development PC at the moment but a SSIS package doesn't have the References section a C#/VB/Web solution might.

    čtvrtek 7. června 2018 15:19
  • Hello, here you have an article to connect to SharePoint 365 using SSIS:

    https://zappysys.com/blog/sharepoint-365-rest-api-ssis-read-list-write-create/


    MVP MCT MCTS Daniel Calbimonte

    http://elpaladintecnologico.blogspot.com

    středa 13. června 2018 16:51
  • Thanks, but I was rather hoping to not have to resort to third party tools to connect to my data source.

    I'm still struggling to make a valid connection when running as an SSIS package (running in development is fine) and suspect that it is something to do with the versions of the additional Microsoft controls required to make the connection. This is partly why I'd prefer not to introduce additional tools to the mix.

    čtvrtek 14. června 2018 14:59
  • After much tinkering I managed to get the package to work from the SQL Server instance. What lead me to the solution was my effort to run the package in VS but under the account which would be used to run it on the server. My hope was that this would highlight an issue previously unidentified. There was however a problem, I couldn't 'Run As...' the VS IDE under my account as I couldn't register my instance - almost as if it was struggling to connect to the internet to authenticate my credentials. This lead me to wonder if the account had permissions to access the internet.

    To test this I logged onto the SQL Server as the intended account and opened IE. I found that I wasn't able to access any web site. This is probably my issue!

    I was able to grant the user access to the internet and found that I also had to configure the Proxy details in IE whilst logged on. from that point on my package was able to connect to the SharePoint Online instance and run successfully.

    pondělí 18. června 2018 15:13