by Rod Stagg, SharePoint Solutions Architect/Developer
http://www.rstagg.com 

Overview

Recently our IT department embarked on a collaborative effort with a key business stakeholder to develop a standardized quoting and bidding solution.   The solution is an interim solution (2 years) replacing a current set of processes (both manual and automated).  We needed to develop the solution quickly and efficiently to address a current need.    

The solution:

Take advantage of SharePoint’s collaboration and document sharing features and built-in workflow capabilities for lists and document libraries to provide a standardized workflow process and centralized repository for tracking and reporting purposes. 

Example SharePoint Designer Workflow

The challenge:

Migrating historical data into existing SharePoint lists and libraries.  A key requirement required migration of historical data into the new solution’s lists, document libraries, and ensure they function properly with existing SharePoint Designer workflows.

No Easy Button: 

We quickly determined that importing the data into SharePoint as entirely new lists from either Excel or Access by itself was not a viable option given the approximately 100 fields involved that varied in type from one datasource to another. 

The historical data was stored in Excel spreadsheets and Access databases.  In one case 10,000+ records were stored in a single Access 2003 database.   

Solution approach for migrating the data:

Use SharePoint Designer workflows to map the fields and import the data into existing lists. 

Steps we took to manage the migration:

• Import the existing historical data stored in Excel Spreadsheets directly into SharePoint as new temporary lists to be deleted when migration was complete.   
• Export the data stored in the Access databases directly to temporary lists in SharePoint using the export to Windows SharePoint Services feature.   
• Develop SharePoint Designer workflows for each temporary list and set the workflow to start manually and also whenever a list item is updated.  
• Add a custom column to each list to track whether an item had been migrated. 
• Add a workflow condition to check whether the item has already been migrated before starting the workflow i.e. if the custom migrated field equals “notmigrated” initiate the workflow.   
• Add actions to create a new list item in the destination list(s) for each desired field/value from the source temporary list.   
• Add a final step to the workflow to update the current item’s migrated field to “migrated” following the creation of the new list item preventing the workflow from looping endlessly.   
• Run an append query from Access to update the custom migrated field in each list item of the temporary list.  This update initiates the workflows.   
• If necessary, develop a simple Windows application to append a specific field in every row of the temporary SharePoint list.  In our case, Access was timing out for our large number of records. 
• We handled special cases for data mapping in code in specific cases where our historical data contained values not present in our new choice fields.
• Monitor your source temporary lists and destination lists to ensure the workflow runs successfully. 

Key Take-Aways:

Server Settings:
Depending on your server settings it may be necessary to update your server’s workflow settings to accommodate a large number of concurrent workflows.  I changed the timeout to 25 minutes. 

See http://msdn.microsoft.com/en-us/library/dd441390.aspx

Create new list items rather than copying.  Creating new list items in your workflows and providing the associated mappings turned out to be more reliable than copying list items. 

Re-use workflows when possible.
We saved time by reusing the same SharePoint Designer workflow on another separate list by simply replacing the listid GUID in your workflows .xoml file

CreateItemActivity ListId="{}{[yourlistsid]}" x:Name="ID30" Overwrite="False" __Context="{ActivityBind ROOT,Path=__context

Use content-types:
When working with a large number of fields consider grouping into content types if appropriate.  This is especially useful if you need the ability to easily filter based on the original datasource or want to provide a specialized form based on the originating datasource. 

Manage list size:

For large number of data items consider using separate lists in your solution to limit the number of total list items in any one list to 5,000 or less. 

Other Approaches we considered:

Develop the code in C# and use the SharePoint object model to both import/export the data to SharePoint and also provide the mapping of fields.  Given the number of fields approached 100+ we determined handling everything in custom code was not the most efficient approach. 

Use Access to import all of the Excel spreadsheets into the Access database and then create append queries in Access to append the data into the existing SharePoint lists.  Seems like the obvious approach but after testing with a subset of the 100+ required fields we determined ensuring that each field/data type in Access was compatible with the corresponding fields/data type in the SharePoint lists was too time-consuming. 

Also, possibly related to the large number of 10,000 records involved, the Access append query we used for testing frequently timed-out or locked-up before completing.