SSIS, InfoPath and SharePoint Lists

Have you ever needed  a low cost solution for capturing data in a form on mobile device and then pull the data back into an in house SQL database. In this solution data is entered into an InfoPath form on a tablet (Windows, iOS or Android), submitted to a SharePoint Online form library and then pulled into a SQLserver database using SQL Integration Services (SSIS).

screen-shot-2017-01-21-at-10-17-01-am

This solution will also work with standard SharePoint lists and can be used with SharePoint Online, SharePoint 2013 or SharePoint 2016.

The things you need to know:

  • InfoPath forms can be hosted in a SharePoint Online forms library
  • InfoPath can ‘promote’ fields from the form into metadata fields in the SharePoint Online forms library e.g. each form will be saved and selected fields will appear as columns on the form library.
  • SharePoint Online form libraries can be queried using an oData connection
  • SQL Server Integration Services (SSIS) can use oData as a data source and push or pull data to and from the data source
  • SSIS can be scheduled

Note: SQL Server Standard Edition (or better) includes SSIS

InfoPath steps:

  1. Using InfoPath Designer, create a form and publish the form to a SharePoint Form library. The easiest option is to do this from InfoPath and let it create the Form library for you.
  2. In Form Options, select the fields that you want to ‘promote’ to SharePoint.
  3. Publish the form
  4. In SharePoint verify that the promoted fields are showing as columns in the Forms library

SQL Server and SSIS steps:

In this step we setup a database and SSIS package. The connection is initiated from the server running SSIS and so this server must be able to make an outbound connection to Office 365.

SSIS packages are created using SQL Server Data Tools

  1. Create a table in your database with fields to store the data from the form
  2. Using Visual Studio create an SSIS package
    • Create the  SQLserver data connection for SQL table created in step 1
    • Create the oData connection to the SharePoint Online list. You will need:
      • The URL for the Form library (the site URL)
      • Office 365 credentials with permission to read the list. These credentials will be saved in the SSIS package
      • See the authentication note below
    • Map the fields from the oData Connection to the SQLserver Connection fields
    • Save
  3. Once you have tested the process (see steps below), you can schedule the SSIS package to run on as frequently as you need.

Testing:

  1. Create a new InfoPath form and submit the form to the library
  2. In SharePoint, check to see that the promoted fields are populated
  3. In Visual Studio run the SSIS package (you may need to ‘Run as Administrator’ when you start Visual Studio for this to work
  4. Check the table in SQLserver to see if any new data has arrived

Authentication issue:

When creating the oData connection to Office 365, you must manually change the setting for ‘Microsoft Online Services Authentication’ to true. This is in the oData Connection Manager settings, under Connection in the Security section. More details here.

Resources:

How to promote fields from InfoPath to SharePoint

How to create an SSIS package with SQL Server Data Tools

MSDN: SSIS oData tutorials

Microsoft MVP award

On the 1st of January 2017 I was awarded the Microsoft MVP award for Office Servers and Services (Yes, I am a SharePoint nut). I feel honoured to be a recipient of this award because it recognises something which I personally think is very important, contributing to the IT community.

screen-shot-2017-01-17-at-8-16-46-pm

I encourage everyone to find a community of interest and participate regardless of whether it is technology based or something completely different. Being part of a community is rewarding in many ways. You’ll meet new people, make great friends, learn new things and get enormous satisfaction from helping others who share you interest.

My involvement in the IT community was sparked by a need to learn about things that weren’t written in books or covered by professional training courses. I quickly discovered that putting myself forward to talk on a topic was a great motivator for learning (I was worried about embarrassing myself). I also discovered that embarrassment doesn’t really happen, people are supportive, audiences are kind and many people will take a moment to say a quiet thank you.

I never set out to get an award, that is something that came from giving time, sharing experience and talking about what goes wrong in the real world. Thank you very much to the people who support me, participated in the events I have organised, turned up to my presentations, watched my YouTube video and read my blog. I feel very thankful and motivated to keep it up!

Thank you! Now go and sign yourself up to your local user group!

 

 

5 Steps for migrating documents to SharePoint

Migrating Process Oriented Documents

One of the challenges with SharePoint projects is content migration. It can be a daunting task with both technical and human challenges. This post discusses a five step methodology for migrating content from a file server into SharePoint.

This strategy revolves around identifying the documents that will be migrated based on the business value they bring rather than a “big bang” approach.

Experience tells us that once people start working with SharePoint, their idea of how it will work best for them evolves. For this reason, we advocate starting with a pilot content set rather than trying to tackle the entire file server in a weekend.

Step 1: Decide what to migrate

Choosing the documents to migrate first is a key part of this strategy. This needs to be achievable and of value. If the set of documents is large or complex to migrate then issues will be magnified. If the documents are of low value then no one will care.

Business process centric documents are a good place to start. These documents are produced as part of a business process and will (hopefully) be stored in one place on the file server. It is also generally easy to identify who uses the documents. It is also easy to place a value on these documents.

Conversely, choosing to migrate all the documents for a group of users, is going to be difficult. These documents could be high value for the owners, but most other people probably won’t benefit from the migration.

If the document is produced from an external system e.g. an ERP system ask, “Do these documents need to be stored in SharePoint?” If the ERP is the source of truth, then storing a second copy in SharePoint may not be necessary.

Step 2: Define your rules

Not all documents have the same requirements from compliance, legal or business process perspective. Agree on and document your standards:

  1. How long do you need to keep these documents?
  2. What meta-data do you want to record about these documents?
  3. What security requirements do the documents have?
  4. Is versioning necessary and if so how many versions?
  5. Do these documents need approval before publishing?
  6. Who owns these documents?

Your file server will be full of documents but do they all need to be migrated? Think about your business requirements and whether you migrate:

  1. All documents
  2. Documents created in the last X months
  3. Leave existing documents on the file server, but create new ones in SharePoint

Make sure these rules are documented and agreed by your key stakeholders.

As part of this step you may be faced with some decision around organising content in Document Libraries. See our Metadata vs Folders post for more details.

Step 3: Test the theory

Test the system with a small but representative sub-set of the documents. Make any adjustments and test again until the “owners” are happy with the configuration.

Your test should include the following:

  1. Security on the Document Library
  2. Check out/in status – compulsory meta-data can result in documents being checked out when they are uploaded
  3. Other settings including approval, versioning and any workflow.

Step 4: Migration

Now you have defined what it is you will migrate, the migration rules and tested the process, it is time to do it for real.

Before you begin…let your SharePoint Admin know what you are about to do. Bulk copying files can impact other users in SharePoint and consumes space on the SharePoint database servers.

Rather than uploading files one at a time, try using Drag and Drop or Explorer view to transfer files (maximum of 100 documents at a time). Keep in mind the limitations of SharePoint document libraries, by default the limit is 5000 documents in a library or a folder within a library. Folders can be used to increase the number of items in a library however you should consider other factors such as security, navigation and search before using folders.

If you are migrating large volumes of files we recommend using specialist SharePoint migration tools such as SharegateAveDoc Migrator or Metalogix Content Matrix.

Note that upload performance can be slow, especially if the SharePoint server is being accessed across a relatively low speed connection.

Step 5: Review and Repeat

Now that you have completed the migration of your first business processes documents, review the process, make any adjustments and repeat for the next set of documents.

Document migration is labour intensive. Create a roadmap for migration. Break migration tasks into a series of time-boxed sub-tasks will help keep the migration team on task and moving towards the end goal in an organised way.

 Migrating everything else

This is the first blog in our series on document migration. In our next post we will talk about migrating collections of loosely related documents. Following on from this we will cover topics including migrating content between test and production, onsite to the cloud and integrating with other systems.

References:

Uploading Documents in to SharePoint

SharePoint un-representable Datetime issue with Publishing sites

I recently had a serious issue with a SharePoint 2016 farm. Any site with Publishing enabled would fail to display giving a correlation error.

UPDATE: This issue is definitely related to Token Timeout settings. It was pretty common to adjust these settings in older SharePoint versions, but SharePoint 2016 doesn’t behave the same. See comments for more details.

Application error when access /Pages/Home.aspx, Error=The added or subtracted value results in an un-representable DateTime.  Parameter name: value   atSystem.DateTime.AddTicks(Int64 value)     at Microsoft.SharePoint.Publishing.CacheManager.HasTimedOut()     at Microsoft.SharePoint.Publishing.CacheManager.GetManager(SPSite site, Boolean useContextSite, Boolean allowContextSiteOptimization, Boolean refreshIfNoContext)     at Microsoft.SharePoint.Publishing.TemplateRedirectionPage.ComputeRedirectionVirtualPath(TemplateRedirectionPage basePage)     at Microsoft.SharePoint.Publishing.Internal.CmsVirtualPathProvider.CombineVirtualPaths(String basePath, String relativePath)     at System.Web.Hosting.VirtualPathProvider.CombineVirtualPaths(VirtualPath basePath, VirtualPath relativePath)

I tested creating a new web application and site collection using the Team Site template. This worked successfully. I tried again using a Publishing Site template and the error above  appeared.

The issue appeared to be timezone related and so we checked the timezone and locale settings on all servers in the farm and the site collections to make sure they matched.

The ULS log also pointed us towards the Security Token Service and then the TokenTimeOut setting. Bingo! The SharePoint 2016 farm was using Mini-roles and the server host the Security Token Service had failed to pickup the configuration update with the timezone settings and so didn’t match the rest of the farm.

To resolve the issue we did the following:

Initially we had installed the farm with United States timezone, when a change was made to use New Zealand time, the configuration didn’t fully update on all servers and the Security Token Service was responding with US Date format making things very unhappy.

Publishing pages use the Security Token Service to validate pages. If the validation fails the page doesn’t load. Team sites without Publishing enabled are OK as they don’t do this validation.

SharePoint 2016 Workflow Manager Registration error

I tried to register Workflow Manager with my SharePoint site collection on a new SharePoint 2016 farm. I followed this article and everything was fine until the last step in the process,  registering the Workflow Manager in SharePoint using this command:

Register-SPWorkflowService –SPSite http://sharepoint –WorkflowHostUri https://workflow:12291

Unfortunately I struck the following error:

Register-SPWorkflowService : Failed to query the OAuth S2S metadata endpoint at URI ‘http://sharepoint/_layouts/15/metadata/json/1;. Error details: ‘An error occurred while sending the request.’. HTTP headers received from the server….

Quite a few people reported this as being an issue with DNS registrations e.g. the SharePoint site URL not being accessible. I checked DNS name resolution and everything looked fine, so it must something else. If you are having name resolution issues try using a hosts file entry on the SharePoint server to resolve the issue.

The solution was to enable OAuth and Metadata over HTTP. To enable these settings use the following PowerShell on the SharePoint server:

$cfg = Get-SPSecurityTokenServiceConfig
$cfg.AllowOAuthOverHttp = $true
$cfg.AllowMetaDataOverHttp = $true
$cfg.Update()

Now rerun the Register-SPWorkflowService PowerShell command to complete the registration.

I hope this saves you a bit of time!

SharePoint Managed Account Login Failures KB3177108

SharePoint allows you to config “managed accounts” used to run service applications to automatically change passwords. I normally turn this on as it is a good way to help ensure those accounts are secure.

Today I worked on an issue where the accounts were failing to login. In IIS I could see various App Pools using the managed accounts were stopped. I restarted them but they stopped again immediately.

To make matters worse, I was unable to use Central Admin to reset the passwords.

Here’s what I did to solve the issue:

  • In IIS I noted the name of the Service Account assigned to the stopped App Pool
  • In Active Directory, I reset the password and set the account to never expire
  • On the SharePoint server, I used PowerShell to reset the Service Account password to a known password using this command:

Set-SPManagedAccount -identity Domain\User -ExistingPassword (Convertto-Securestring “P@ssword” -AsPlainText –Force)

  • Back to IIS again, restarted the App Pool
  • Repeat for all Managed Accounts with automatic password refresh

So what caused this?

A Windows update installed onto the Domain Controllers was the issue. KB3177108 has a known issues which prevents the Kerberos negotiate process from falling back to NTLM. The KB article goes into detail on this and some work arounds.

Phew! We’re back online again. Happy days!