​The Layer2 Cloud Connector for Microsoft Office 365 and SharePoint connects almost any on-premise data source, e.g. ODBC, OLEDB, Microsoft .NET based providers, Files (Excel, XML, CSV), File Shares, SQL databases like SQL Server, Oracle, MySQL, IBM DB2, IBM AS/400, IBM Informix, Notes, SharePoint, Exchange, Active Directory, Navision, SAP and many more directly to native SharePoint lists and libraries in the Microsoft Cloud - or any other Microsoft SharePoint 2010 / 2013 installation - in just minutes without any programming. This FAQ is about connecting the output of PowerShell scripts with SharePoint lists to enable notifications and workflows, e.g. on specific data changes.

PowerShell-SharePoint-Connection.png 

Connect PowerShell scripts to native SharePoint lists for reporting, notifications and workflows - Why?

Windows PowerShell is Microsoft's task automation and configuration management framework, consisting of a command-line shell and associated scripting language built on .NET Framework. It is often used for administration and reporting, not only for the Windows operating system but also for applications platforms like Microsoft Exchange, SQL Server or SharePoint. You can use the Layer2 Cloud Connector (for SharePoint Online) or the Layer2 Business Data List Connector (for SharePoint on-premise) to connect the output of such scripts as reports to synchronize with a native SharePoint list. This opens up endless possibilities:

  • Reporting using native SharePoint lists: All list features available for the PowerShell reporting data, e.g. views, filter, grouping, calculated fields, search etc.
  • Management Dashboards: Can be connected to the SharePoint lists.
  • Mobile Access: PowerShell results are cached in native SharePoint lists for mobile access, everywhere with any device.
  • Notifications on data change: The script is executed scheduled on a regular base (e.g. every hour). You will be notified in case of data changed by list subscriptions
  • Workflows on data change: You can check for specific values, e.g. thresholds and start your own business logic a workflow, if required. Think about library quotas or any other reporting issue. Tasks can be created if required.
  • Versioning: You have a history of changes for your script output / report.
  • File Share Integration: You can list file shares with PowerShell and have the output available in lists to link back to files (e.g. using calculated fields).

Any other ideas? For any questions please use the FAQ Web Form or contact [email protected] directly.

Connect PowerShell scripts to native SharePoint lists for notifications and workflows - How?

​Installation requirements

If not installed, please install the Layer2 Cloud Connector. To connect Layer2 tools to a data source you need a data provider (driver). For Windows PowerShell you can install and use the provider offered here by RSSBus.

Please select the data provider in the Cloud Connector data entity settings as shown below.

 

SharePoint-List-to-PowerShell-Connection-1.png 
 Fig.: Data Source configuration to execute a PowerShell script and connect to results via ADO.NET.
 
Setup a PowerShell sample skript
 
As a sample we have used a script service.ps1 that simply lists the services running on the current computer. Please note the "param" part with data types, field names and default values that is required for using SQL with the result set.
 
param(
 [String]$DisplayName='*',
 [String]$Name='',
 [String]$Status='',
 [String]$ServiceName='',
 [String]$ServiceType='',
 [Boolean]$CanPauseAndContinue=0,
 [Boolean]$CanShutdown=0,
 [Boolean]$CanStop=0
)
 
Get-Service  |Sort-Object DisplayName | Select-Object DisplayName, Name, Status, ServiceName, ServiceType, CanPauseAndContinue, CanShutdown, CanStop
 
The PowerShell script has the following output when executed:
 
SharePoint-List-to-PowerShell-Connection-3.png 
 
Fig.: Sample PowerShell Script output that simply lists the local services. 
 
Connection String
 
As a next step you have ro provide a connection string. The most important attribute is the script location The directory that contains your PowerShell scripts), e.g.:
 
script location=C:\myScripts
 
You will find many more options, e.g. for logging, authentication, caching etc. here.
 
SQL data query
 
You can query the PowerShell result set as usual with SQL. Please use your script name as table or view name, e.g.:
 
Select * from MyScript
 
You will find more options to use SQL, e.g. to make use of aliases, where conditions or to limit the result set here.
 
Set a primary key
 
To carefully update the SharePoint list (or any other target data entity) you need one or more unique columns that acts as a primary key. 
 
Verify your settings using the data preview
 
SharePoint-List-to-PowerShell-Connection-2.png 
Fig.: Data preview of the script result shown in the Layer2 Cloud Connector.
 

Connect PowerShell scripts to native SharePoint lists for notifications and workflows - Setup Data Destination

​Before you setup the data destination, you have to create. We are using a SharePoint Online list in this sample. Generally you can use any data destination, e.g. SQL tables. You have to create manually with the appropriate structure / fields. After your data destination is created, you can connect:

  

SharePoint-List-to-PowerShell-Connection-4.png

Fig.: Setup a SharePoint Online list as data destination for the PowerShell output.

 
You don't have to provide a primary key here, SharePoint Item-ID is used by default. Please verify the connection and data preview. As a next step you have to setup the field / column mapping between data source and data destination.
 
SharePoint-List-to-PowerShell-Connection-5.png
 
Fig.: Setup field / column mapping between data source and destination.
 
Please note that we don't use the auto-mapping feature in this sample. That gives us the option to map the ServiceName field (from PowerShell) to the Title column in SharePoint (as often required for lists).
 

Now you can run the first synchronization.

SharePoint-List-to-PowerShell-Connection-6.png

Fig. First synchronization of the PowerShell script results to the SharePoint list.

 

Please note that with next updates existing SharePoint list items are updated via the primary key (Name). Only changes in data source cause changes in data destinations. In this way notifications and workflows can be used to take actions. In case everythings fits, you can enable scheduling and set an appropiate interval.

Connect PowerShell scripts to native SharePoint lists for notifications and workflows - Results

As a result we have a native SharePoint list connected to the output of the PowerShell script available with all the great SharePoint list options like views, calculated fields, search, notifications, workflows etc.

 

SharePoint-List-to-PowerShell-Connection-7.png 

 

 

 

Layer2 Cloud Connector - Next Steps

​You can download the Layer2 Cloud Connector and the RSSBus ADO.NET Data Provider for PowerShell as trial after registration. Please contact [email protected] if you need help with your specific requirements.
Share this site on Xing
Share this site on Google+
Share this site on LinkedIn

It was extremely simple to configure data sources, retrieve data from local databases and sync to our Office 365 SharePoint site. I would recommend this product to anyone with local data that needs to be sent to the SharePoint cloud.

Contact
Frank Daske

Frank Daske

Business Development Manager
+49 (0) 40 28 41 12 - 30
[email protected]

Please note that the 011 exit code must be dialed first for all international calls made from the USA, Canada and Australia.

Related News

Layer2 now connects 100+ commonly used IT systems with Twitter via its Layer2 Cloud Connector

The Layer2 Cloud Connector is now able to connect Twitter to integrate data to a wide range of IT backend systems including Office 365, SharePoint, SQL, ERP/CRM.

New Microsoft Teams Connectors: Layer2 adds 100+ typically used backend systems

​The Layer2 Cloud Connector is now able to connect to Microsoft Teams to quickly integrate more than 100 IT backend systems including local SQL, ERP/CRM.
Related Downloads
PDF Image

Cloud Connector Instruction

Download this step-by-step instruction to connect and sync your corporate or cloud data sources bi-directional to any native SharePoint 2010/2013/2016 list or library, including Microsoft Office 365.
PDF Image

Feasibility Study: Connecting local SQL Server to SharePoint Online

The study compares different approaches for SharePoint Online data integration regarding performance, features, security and investment.
PDF Image

Flyer: Layer2 Partner Program

Partner with us as a software distributer, reseller or SharePoint, Office 365 or Azure service provider. We are offering a free Partner Program with up to 30% Discount, free NFR licenses etc. Please download for more.
PDF Image

Layer2 Case Study: Cloud Connector helps ChemWerth to integrate the Microsoft Office 365 Cloud

"The Cloud Connector’s interface is easy to understand and use. Anyone familiar with utilities in the Microsoft stack will be productive immediately. Our recommendation: The Layer2 Cloud Connector is a no brainer.”
NoDoc Image

Layer2 Cloud Connector PAD File

This XML-based PAD file offers a Layer2 Cloud Connector product description for resellers to download and publish.
PDF Image

Office 365: How to sync with MS Dynamics CRM

You can sync any CRM data with native lists in SharePoint on-prem or online to make it available offline with Outlook.
PDF Image

Office 365: How to sync with SAP?

You can connect SharePoint and Office 365 with SAP via NetWeaver and OData.
PDF Image

Office 365: How to sync your local file share?

When you are moving to the cloud, there are a lot of reasons to keep your local file system. But you don't have to miss the benefits of the cloud.
PDF Image

Product Flyer: Cloud Connector for Microsoft SharePoint & Office 365

Flyer with features, benefits & pricing info for download as PDF.
PDF Image

Product Flyer: SharePoint Online Local File Server Sync

You can centrally migrate, synchronize, or backup your local file server department shares and home drives very flexible with SharePoint Online to close known gaps and overcome limitations of the Microsoft OneDrive for Business client.
PDF Image

Solutions Flyer: Data Integration with SharePoint, Office 365 and 100+

FileMaker can be integrated with 100+ other sources codeless using the Layer2 Cloud Connector.
Related Links

Layer2 Cloud Connector Community Group @ LinkedIn

​Are you already @ LinkedIn? Please join the Cloud Connector Community Group to share your experience.

Microsoft Partner Story Book for Office 365 about Cloud Connector

​Know more about a Layer2 Cloud Connector customer reference at the Microsoft Office web site.

Purchase Layer2 Cloud Connector via ComponentSource

​ComponentSource has offices in the USA, UK and Japan supporting over 125,000+ Customers, in over 180 Countries. They are also an approved Federal Government supplier through the GSA Schedule.

 

cs-award-top-100-publisher-2014-15-large.gif

Sign Up for Layer2 Product Newsletter

We keep you up-to-date about new Layer2 product releases, added features and fixed issues.