Remote Access to SharePoint Lists with PowerShell

Question

I have a PowerShell script that pulls data from a database. I need to push this data to a SharePoint list on a different server.

Would you have tutorial or examples that show how to do this (authentication and update)?

Update: I need this to work on both SharePoint 2007 and 2010. I do not have admin access on the SharePoint server.

Answer 1

Not sure I would use PowerShell for this.

Why not use Business Connectivity Service (SP2010) or Business Data Catalog (SP2007) for this?

If you need to move data redundantly into a list in SharePoint with certain intervals another option would be SQL Integration Services.

Answer 2

Given the information I would still chose to do a console app that leverages SharePoint's web services versus using PowerShell with remoting. If you need it to work with both, then I would stick with the soap based services. http://msdn.microsoft.com/en-us/library/ms479390(v=office.12).aspx

Answer 3

This is my second answer since question was updated.

I was following link provided by @MikeOryszak and I did some testing. This is the PowerShell script that uses SP web service with custom credentials to insert data:

$items #ListItem collection

$siteUrl = "http://mySite"
$fieldsToCopy = @("Title", "SomeOtherField") #fields to copy

#Build batch
$batch = "<Batch>"
for ($index = 0; $index -lt $items.Count; $index++) {
    $methodId = $index + 1
    $batch +=  "<Method ID='$methodId' Cmd='New'><Field Name='ID'>New</Field>"
    foreach ($field in $fieldsToCopy) {
            $fldValue = $items[$index][$field]
            $batch += "<Field Name='$field'>$fldValue</Field>"
    }
    $batch += "</Method>"
}
$batch += "</Batch>"

#Make webservice call
$wsProxy = New-WebServiceProxy -Uri $siteUrl/_vti_bin/lists.asmx -Credential [DOMAIN\USER]
#You will be prompted for password
$xmlB = [xml]$batch 
($wsProxy.UpdateListItems($targetListId, $xmlB )).ResultStatus

Note: I have tested this on single server only with 'primitive' field types and it is working. There are many ways this script can be upgraded but I have done this just as 'proof of concept'

Answer 4

You can use the SharePoint Webservices from your Client. It´s only necessary that PowerShell 2.0 is running on your Client. You don´t need any Admin priviliges on the SharePoint.

Use the following code:

$Credential = New-Object System.Management.Automation.PSCredential('test\user',(Read-Host "Enter Password" -AsSecureString)) 
$SPList = New-WebServiceProxy -uri http://yoursharepoint.de/_vti_bin/lists.asmx –Credential $Credential

http://technet.microsoft.com/de-de/library/dd315258.aspx