Using BCS from PowerShell with SharePoint 2013

Running data migrations with SharePoint 2013 often involves using BCS and PowerShell if you don't have and third party tools at hand. This can be a bit tricky to set up, but once you are familiar the the BCS configuration, the PowerShell scripts are mostly straight forward.

From my experience there are a few differences when running against BCS lists vs. regular lists. I have now been able to find a good guide for this, so from my own trial and error I wanted to share a few tips.

Get the context straight: Accessing the list gives a weird error

Running this:

$Web = Get-SPWeb "http://mybcsweb/""
$Web.GetList("/Lists/MyBcsDataList")

Gives this error:

format-default : The shim execution failed unexpectedly - Proxy creation failed. Default context not found..    + CategoryInfo          : NotSpecified: (:) [format-default], BdcException   + FullyQualifiedErrorId : Microsoft.BusinessData.Infrastructure.BdcException,Microsoft.PowerShell.Commands.FormatDefaultComman

bcs-powershell-sp2013-1

The fix:

$Site = Get-SPSite "http://mybcsweb"
$ServiceContext = Get-SPServiceContext -Site $Site # Required when working with BCS
$ServiceScope = New-Object Microsoft.SharePoint.SPServiceContextScope $ServiceContext # Required when working with BCS
$Web = $Site.RootWeb
$Web.GetList("/Lists/MyBcsDataList")

A bit more wiring is required to get the context straight for BCS to work.

Make sure you are running with administrator privileges

I use PowerShell ISE when creating and running these scripts. From time to time I accidently forget to start this with Administrator privileges. This can fool you into weird errors like this one. Short minded one can try to start adding the missing assembly references, but the real reason is the missing privileges.

Running this:

$Web = Get-SPWeb "http://mybcsweb/"
$Web.GetList("/Lists/MyBcsDataList")

Gives this error:

format-default : The shim execution failed unexpectedly - Could not load file or assembly 'System.Data.OracleClient, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0x80070542).    + CategoryInfo          : NotSpecified: (:) [format-default], BdcException  + FullyQualifiedErrorId : Microsoft.BusinessData.Infrastructure.BdcException,Microsoft.PowerShell.Commands.FormatDefaultCommand

bcs-powershell-sp2013-2

The fix:

Search for "PowerShell ISE", right-click it and choose "Run as Administrator".

bcs-powershell-sp2013-3

Include ViewFields in you CAML queries

Always remember to include the fields you are trying to query on in the "<Where>" statement within "<ViewFields>". This isn't required on regular lists, but to get any results in return, I experienced I was required to include them querying BCS lists.

Example of CAML query in PowerShell:

$Query= New-Object Microsoft.SharePoint.SPQuery 
 
$Query.ViewXml =
 "<View>
 <Method Name='Read List'/>
 <Query>
 <Where>
 <And>
 <Eq>
 <FieldRef Name='ProjectID' />
 <Value Type='Text'>100</Value>
 </Eq>
 <Eq>
 <FieldRef Name='Role' />
 <Value Type='Text'>Project Manager</Value>
 </Eq>
 </And>
 </Where>
 </Query>
 <ViewFields>
 <FieldRef Name='ProjectID'/>
 <FieldRef Name='Role'/>
 </ViewFields>
 </View>"
 
 $Items = $List.GetItems($Query)

Summary

BCS has been around for a while and isn't something very existing. But as mention in the introduction, using it for data migrations is very powerful, specially when you don't have any 3. parts tools around. Doing this from PowerShell is pretty straight forward, but these three issues I experienced can be a headache if you get stuck with them. Hopefully this post can help others get quickly passed them!