I need daily gives a load of data in a List in Sharepoint Online through a table of SQL Server 2017.
I chose to do this through SQL Server Integration Services 2017 and it works correctly.
For this, I created a Data flow Task
named Package:
Within the Data Flow Task
, I have a connection to the database (DB) to fetch the data.
Then, I have a Component Script
named Script to make the connection with Sharepoint Online.
And then insert data into the Sharepoint List:
To make the connection with sharepoint Online and insert data in the Sharepoint List I created some parameters:
And within the Component Script I have the following code:
public override void Input0_ProcessInputRow(Input0Buffer Row)
{
// Starting with ClientContext, the constructor requires a URL to the
// server running SharePoint
string siteUrl = Variables.siteUrl.ToString();
ClientContext clientContext = new ClientContext(siteUrl);
// Retrieve Login
string login = Variables.login.ToString();
// Retrieve Password
string password = Variables.password.ToString();
// Credentials
clientContext.Credentials = SignIn.Login(login, password);
// Retrieve List Items
List myList = clientContext.Web.Lists.GetByTitle(Variables.list.ToString());
// Create a new ListItemCreationInformation object
ListItemCreationInformation itemCreateInfo = new ListItemCreationInformation();
// Create a new List Item
ListItem myListItem = myList.AddItem(itemCreateInfo);
myListItem["Title"] = Row.Name;
// Note that the web.Update() doesn't trigger a request to the server
// Requests are only sent to the server from the client library when
// the ExecuteQuery() method is called
myListItem.Update();
// Execute the query to the server
clientContext.ExecuteQuery();
}
SignIn class:
class SignIn
{
public static SharePointOnlineCredentials Login(string login, string password)
{
SecureString securePassword = new SecureString();
foreach (char c in password)
{
securePassword.AppendChar(c);
}
return new SharePointOnlineCredentials(login, securePassword);
}
}
When running Job through SQL Server Agent with this package, I get the following error:
Executed as user: INSTANCE\SQLAgentService. Microsoft (R) SQL Server Execute Package Utility Version 12.0.4100.1 for 64-bit Copyright (C) Microsoft Corporation. All rights reserved. Started: 14:56:49 Error: 2020-09-01 14:58:52.51 Code: 0x00000001 Source: Package Description: Exception has been thrown by the target of an invocation. End Error DTExec: The package execution returned DTSER_SUCCESS (0). Started: 14:56:49 Finished: 14:58:52 Elapsed: 122.735 seconds. The package executed successfully. The step succeeded.
Doubts:
Does SQLAgentService need permission to access Sharepoint Online?
Do I need to install the Microsoft.SharePoint.Client.Runtime.dll and Microsoft.SharePoint.Client.dll dll's on the SQLAgentService server?
For me, authentication takes place through the nameless@test.onmicrosoft.com account (which has permission to collaborate on Sharepoint Online).
How can I populate a Sharepoint Online List via SQL Server Agent?
User contributions licensed under CC BY-SA 3.0