0

I have been hitting dead ends when trying to pull SharePoint List data into SQL Server. The goal is to query the data so I am able to use it in a Tableau dashboard. The reason we have to do it this was is a 2 fold problem.

1st. The Tableau connection works but one of the columns is returning all null values instead of the double that is in that column.

2nd. I was able to pull this data in to MS Access and then to Tableau but this will not work from an automation standpoint so we need this data to be pulled into our SQL Server daily via a job.

I can manage all the automation once the connection is made but I cannot find a way to connect a list to my SQL Server.

I have tried everything online to fix this issue. OData connection, Stored procedure that does not appear to exist for SSIS connection it as a linked server. All ideas have failed.

Does anyone know a working way to pull data from SharePoint into SQL Server?

2 Answers 2

0

I've done it using a triggered Azure function and CSOM/C# - basically a slightly different version of #2 in this article.

1
  • I am decent at SQL and Python but lacking in the powershell department. I will look into it but it appears to be a complex option. I did manage to pull the data down using python and then loading that directly to the SQL Server using shareplum and pyodbc libraries but then I ran into another problem. The data is only returning 7 columns for some reason lol. I just keep hitting walls with this issue.
    – Mike - SMT
    Jul 10, 2020 at 21:23
0

I have done the exactly same requirement for my Tableau Team, as they wanted to generate some report from SharePoint List but didnot want to use SharePoint Online to Tableau Connector as it cost much for them. I have used simple Powershell script which run every day 5pm and pull data from SharePoint and Push to SQL server.

#This script to pull the SharePoint Online List data and move into SQL  
#created by Vinit Kumar  
#SQL data base information - variables - Please change  
$MSSQLServerInstanceName = "ContosoServer\InstanceName"  
$DatabaseName = "DBTest"  
$TableName = " Product "  
# SharePoint Variables - Please change  
$O365ServiceAccount = "[email protected]"  
$O365ServiceAccountPwd = "abc@2020"  
$siteURL = "https://abc.sharepoint.com/sites/test"  
$List = " Product "  
#Connect to SharePoint Online[SecureString] $SecurePass = ConvertTo - SecureString $O365ServiceAccountPwd - AsPlainText - Force[System.Management.Automation.PSCredential] $PSCredentials = New - Object System.Management.Automation.PSCredential($O365ServiceAccount, $SecurePass)  
Connect - PnPOnline - Url $siteURL - Credentials $PSCredentials  
#Get Sharepoint List items  
$Items = Get - PnPListItem - List $List  
#Loop through the items  
foreach($Item in $Items) {  
    $MovedToSQL = $Item["MovedToSQL"]  
    # check  
    if item already moved to SQL  
    if ($MovedToSQL - ne "Yes") {  
        #  
        $productName = $Item["ProductName"]  
        $productDescription = $Item["ProductDescription"]  
        $productCost = $Item["ProductCost"]  
        $productMake = $Item["ProductMake"]  
        # Insert query  
        for SQL Table  
        $insertquery = "    
        INSERT INTO[$DatabaseName]. [$TableName]  
            ([ProductName], [ProductDescription], [ProductCost], [ProductMake])  
        VALUES('$productName  , '$productDescription  , '$productCost ', '$productMake ')  
        GO "    
        #Invoke SQLcmd to insert the item into SQL table  
        Invoke - SQLcmd - ServerInstance $MSSQLServerInstanceName - query $insertquery - Database $DatabaseName  
        #Update the SharePoint List once item moved and update the Column "MovedToSQL" = "Yes"  
        Set - PnPListItem - List $List - Identity $Item.Id - Values @ {  
            "MovedToSQL" = "Yes"  
        }  
    }  
}   

this Code is specific to SharePoint Online but it should be almost the same for SharePoint Server as well. click here for an article with details of the code.

2
  • Thanks I will have my team look into it and see if it will work for us. I personally do not have direct access to the SQL Server so I will have to have them test this option.
    – Mike - SMT
    Jul 14, 2020 at 15:38
  • Yes, please test let me know outcome so i can try to help if your team stuck with any specific error. Jul 14, 2020 at 15:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.