SharePoint 2013-Create User Profiles from BCS External List using PowerShell

Overview:
We have an internal identity management system that stores and manages identities and custom properties for all employees.  As part of a recent project designed a solution to populate our SharePoint user profile store using information stored about each user in our custom indentity management system.

Alternatives:
We evaluated the Active Directory Import and User Profile Synchronization as well.  The Active Directory Import supports LDAP sources for profiles and although can be configured to import from BCS external list does not actually create user profiles based on that data, rather can populate additional properties for AD user profiles that already exist.  In our case those user profiles do not exist since we are not able to use AD import or profile sync in this environment.

Solution steps::

Step 1:
Configure SharePoint 2013 Secure Store Service and Business Connectivity Services

Step 2:
Configure an external content type and external list in SharePoint Designer 2013 with full CRUD operations enabled bound to a custom database table in SQL where identities are stored.

Note: The external list provides full CRUD operations on the external list to the SQL database table.  This is a key additional feature over a standard SharePoint list which was useful in our case having the option of querying and perform updates to the external list directly from our custom admin form via JavaScript and the SharePoint REST API.

This could be interesting for other use cases as well where updating a custom SQL database table from a SharePoint list is usefull i.e. AngularJS or REACT UI user-interface integrated with SharePoint list data via REST.  Exploring more on this front soon.

Step 3:
Develop PowerShell script to query the SharePoint external list, for each item check for existing profiles in SharePoint, and if none exist create a new profle populated with the data in the external list item for each user.

Example PowerShell script:

#Rod Stagg

#Create SharePoint User Profiles based on data in External List (BCS)

[CmdletBinding()]
Param(
[Parameter(Mandatory=$True)]
[System.String]$siteUrl,
[Parameter(Mandatory=$True)]
[System.String]$listSiteUrl,
[Parameter(Mandatory=$True)]
[System.String]$domain,
[Parameter(Mandatory=$True)]
[System.String]$user
)

Remove-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue
Add-PSSnapin Microsoft.SharePoint.Powershell

$claimsformat = “i:0#.w|”;
$accountName = “”;
$fullname = “”;
$lastfirst = “”;
$firstname = “”;
$lastname = “”;

$Web = Get-SPWeb $listSiteUrl
$SourceList = $Web.Lists[“External List”]
$SourceItems = $SourceList.Items | where {$_[‘networkid’] -ne “”}
$site = Get-SPSite $siteUrl
$context = Get-SPServiceContext($site)
$profilemanager = new-object Microsoft.Office.Server.UserProfiles.UserProfileManager($context)
$SourceItems | ForEach-Object {
# Assumes account in domain\account format and networkid field is the unique identifier on external list
$accountName = $claimsformat + $domain + “\” + $_[‘networkid’]
# Assumes username stored as lastname, firstname on external list so split
$lastfirst = $_[‘lastfirst’];
$firstname = $lastfirst -split “,”
Write-Host $firstname[1];
Write-Host $firstname[0];
Write-Host $accountName;
# If user profile doesn’t exist create new one
if ($profilemanager.UserExists($accountName)) {
$userProfile = $profilemanager.GetUserProfile($accountName)
}
else {
$userProfile = $profilemanager.CreateUserProfile($accountName)
}

# Update any necessary user profile properties
$userProfile[“FirstName”].Value = $firstname[1]
$userProfile[“LastName”].Value = $firstname[0]
# Commit changes
$userProfile.Commit()

}

Additional elements of entire solution:

  • SSIS population of SQL database table that is bound to the SharePoint 2013 external list.
  • Windows schedule task to execute PowerShell script on
  • JavaScript to update the external list from our custom form.

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s