The WorkerThread Blog

we know stuff so you don't have to

Archive for the ‘development’ Category

Review: Simego Data Synchronisation Studio

Posted by workerthread on May 31, 2011

I recently had the opportunity to take a look at Data Synchronisation Studio 3.0, an application developed and marketed by Simego Limited in the UK.   I was interested to see how it might be used to transfer data from or to SharePoint lists as I’ve noticed a fair amount of hits and comments on previous blog posts I’ve written which touch on this subject, like this one on using Integration Services to get SharePoint list data into a database.

Let’s consider first where Data Sync Studio (I’ll shorten the name from now on) is positioned, and how it might help with data synchronisation requirements.   If you are working with Microsoft SQL Server you will know that one of its very useful components is Integration Services,  an excellent tool for moving high volumes of data between applications.  If you are populating a data warehouse, loading database tables with data from an external source, cleaning, merging or de-duplicating data and you are a SQL Server customer, you need to get familiar with Integration Services.  But of course not everyone needing to move data around has access to Integration Services and even if they do, it may be the case that the data providers they need to work with aren’t catered for, either officially or unofficially.   I’ll look at two of those data providers here, namely SharePoint lists, and Active Directory Users, both available in Data Sync Studio.   You’ll see though at the bottom of this page there are many more, including the usual Microsoft database formats, Microsoft Exchange and CRM as well as Oracle, MySQL and OData.

Installing the Application

Data Sync Studio is a standalone .Net application which comes in 32-bit and 64-bit versions.   I’ve been trying it out on a Windows Server 2008 R2 virtual Machine, but you could just as easily install on a modern Windows desktop PC running Windows 7, Vista or even XP.   It is licensed per developer rather than per server.  Once you create a synchronisation project, if it is something which needs to be run on a scheduled basis then you will need to have a licenced copy of the application installed on the machine (desktop or server) which runs the scheduled job.

At the time of writing (May 2011) a single user licence of Data Synchronisation Studio currently costs 450 GBP / 720 USD.  More information on pricing and licensing options are shown on the product home page.

Importing or Exporting SharePoint Data

Data Sync Studio allows you to use a SharePoint list as either a data source or destination and you can synchronise data in both directions.  This means that you can fairly easily connect your list to a SQL Server table and keep the two in sync.   Of course you should first be sure this is the best approach to solving your business problem.  There are other methods to consider here, not least of all Business Connectivity Services, but BCS is definitely not the answer for everyone as it’s quite difficult to get right and again will need other tools to set up correctly – at the very least access to SharePoint Designer 2010.   Also I’ve often come across situations where data needs to be captured in SharePoint then later moved to an external database such as SQL Server for additional analysis, reporting or even archiving.

I took the example I used in my article on using Integration Services with SharePoint data.  I created the same list in SharePoint 2010 and a destination table in SQL Server 2008 R2.   In Data Sync Studio the SharePoint list became Data Source A and the SQL Server table Data Destination B:


First, I moved all of the current SharePoint list items to SQL Server.   This is easily accomplished using the “Compare A>B” button, which gives me a summary of the differences between source and destination.  I can then use the “Synchronise” button to push these changes to SQL Server.   Updates, additions and even deletions from the SharePoint list can also be handled and sync’d to the SQL Server destination:


And if you want, the sync can be bi-directional – you can specify which synchronisation options are used by setting properties in the source and destination:


Data Sync Studio also includes a “Dynamic Columns” feature which lets you create custom code to express columns.  These can range from fairly simple examples such as concatenating multiple columns (for example first and last name) or performing lookup or validation tasks.  One example provided is a “SharePoint Lookup Helper”.  You would use this if you were importing data into a SharePoint list and during import you needed to match a source value to the ID of an item in a separate lookup list.  Note that this facility is available via a C# programming interface so definitely requires .Net expertise.  You can read more about this here.

I also tried using the SharePoint data provider with Office 365 but couldn’t get this to work as it wouldn’t connect.  Simego tell me they are actively looking at this and I think it would be useful.   I can imagine for example a scenario where external users submit data to an Office 365 application and the data is sync’d with a corporate application or reporting system.

Working with Active Directory Data

Data Sync Studio includes LDAP Active Directory Users as a data provider which can be used to import this data into a database  or SharePoint list:


I tried this out, loading specific columns of the AD user list into a SharePoint list.   I could certainly see some uses for this.   Simego say they already have clients using this to populate a staff or team directory in SharePoint.   Also, although the Active Directory User data provider doesn’t allow inserts or deletes (a good thing) it does allow you to update configured columns.   This could be useful – it’s often the case that when an organisation implements SharePoint they want to get best value from searchable information in Active Directory, but fields related to job title, department, contact phone numbers etc aren’t populated.   This could provide a nominated user (maybe a member of the HR department) with a way of updating the fields in a list exported to SharePoint and then syncing the updated fields back to Active Directory.

It’s probably worth noting that bi-directional synchronisation is also possible with the SharePoint 2010 User Profile service, but again, this will present you with lots of configuration challenges and is definitely not for the faint-hearted.   Data Sync Studio may be a viable alternative here.

Creating a Project Schedule

Data Sync Studio lets you create a schedule for your synchronisation project using a dialog within the Studio itself:


Once you have selected dates and times to schedule a run and entered login credentials, a Windows scheduled task is created automatically for you.   A separate “Data Synchronisation Run Tool” is also provided within which you can schedule multiple projects to run in sequence and it’s also possible to execute a synchronisation project from the command line.

Summing Up

Data Synchronisation Studio is well worth a look if you have a need to move data between line of business applications and your requirement can’t be addressed by SQL Server Integration Services, either because you aren’t able or allowed use it or because the appropriate data providers aren’t available.   Certainly it won’t take the place of Integration Services when you are moving very high volumes of data to or from SQL Server and Simego don’t sell it as a replacement.   The fact that several of the data providers included currently aren’t in Integration Services could mean it pays its way quite quickly (as well as the two mentioned above I could also see it being valuable for Exchange data and for Microsoft CRM for example).   It’s also possible to create your own data providers and make these available as add-ins.   Simego provide some example add-in providers showing how this might be done, via a C# project.

You should be aware though that this isn’t an end-user tool.  It’s really aimed at developers with some coding experience, someone happier with developer tools such as Visual Studio, Integration Services or Business Intelligence Developer Studio.   Also, although the application comes with a help file and lots of online screenshots, it did take me a little while to understand the user interface and perhaps one or two additional “walk-through” sessions in the help file would be useful.   Having said that, you might also find what you are looking for in these blog posts.

Overall then my evaluation experience was a positive one.  It’s also good to see a home-grown (UK) development company coming up with a tool like this (and even spelling synchronisation without a “z”!).

Posted in development, Microsoft SharePoint, Office, SQL Server | Comments Off on Review: Simego Data Synchronisation Studio

Thoughts on Access Services in SharePoint 2010

Posted by workerthread on January 16, 2010

Back in November last year I gave a presentation to the UK Access User Group  on Access 2010 and in particular how it works with Access Services in SharePoint 2010.  I’ve been meaning to put something together on my thoughts on Access Services, both good bits and not so good.  Last week Robert Bogue wrote an interesting piece about Access Services and how he sees key usage areas.  Robert sees one main area of interest being as a reporting tool for SharePoint content, even content not originally created in Access.  I’m not sure I agree with him here, for reasons I’ll explain below.

First let me say that the Access team have put some clever features in Access 2010.  As with Access 2007, the emphasis is on a well-behaved Office application where customisation and development is ideally done without coding in VBA.  To this end, the Access macro editor has been completely re-designed, and now includes data macros which allow you to create events at the table level (similar to triggers in SQL Server).  There are also some interesting new navigation features which make it easy to create a modern looking application for users to find their way around.  Same with forms and report design – each of these has a “layout” design mode which is mandatory if you want to design for Access Services.  Using layout mode means designing forms and reports in pretty much the same way as you would an HTML table, or an InfoPath form.  There is a new option to create a “web database” which disables any Access feature which won’t be compatible with Access Services.  There’s also a compatibility checker which reviews all of the objects in your database and highlights anything which may cause problems when publishing to Access Services in SharePoint 2010.  At the bottom of this post I’ll include some links to videos and getting started tutorials which you might want to try out yourself.

Robert mentions the fact that Access 2010 web database reports are published up to Access Services using RDL (report definition language).  And in fact you don’t need to install full-blown SQL Server Reporting Services to get this to work – there is a reporting services add-in for your SharePoint 2010 server which you can install from a download here.  This can be run in local mode when using Access Services as explained here.  Once you have published your Access app, reports work in pretty much the way they would in the full version of Reporting Services, including pagination and report export options for PDF, Excel etc as you can see here:


Web database applications are also available as SharePoint site templates.  If you have installed the beta of SharePoint 2010 you will see several of these in the available templates list, here’s an example:


When you provision your new site it will look very much like it would if you had published it from Access – and you can also further customise the site in Access 2010.  There are similar templates provided in the current Access 2010 beta, so you can also try it out by using one of the Access templates to create your web database and taking a look under the covers at some of the design and macro coding techniques used (no VBA of course!).

And yet, and yet….

There are still several reasons why Access services might not be that attractive to a lot of SharePoint users.  First and foremost, as Robert says, Access Services is a SharePoint 2010 Enterprise feature, which will bump up your licence costs considerably.  And the appeal of Access Services as a SharePoint reporting tool may be diminished by the fact that SQL Server 2008 R2 includes SharePoint lists as a data source out of the box.  This means that you could install SQL Server 2008 R2 and integrate with any version of SharePoint including SharePoint Foundation 2010 (Windows SharePoint Services 4.0).  From what I have seen so far SQL Server 2008 R2 will also happily connect to SharePoint Server 2007 lists and will allow you to deploy reports to multiple SharePoint zones, so even if you aren’t considering a SharePoint 2010 upgrade you may want to consider the R2 release of SQL Server 2008 for reporting.

Then there’s the degree to which a published Access web database is locked down.  Most of the standard SharePoint 2010 site settings options are missing in the published site – if you want to continue customisation you need to do this in Access and re-sync changes.  Here is the standard menu of options:


Also, the look and feel of the Access site will be inherited from the Access database, not from your (possibly) extensively re-branded master page.  When I did the user group presentation, this was something that several attendees who also had SharePoint involvement were concerned about.  I DID notice that it the _layouts/settings.aspx and _layouts/viewlsts.aspx pages were still accessible though, so it was still possible to see all of the site settings options that had been hidden – not sure you’re meant to do this though as it could make further Access customisations difficult and it may be that this is locked down further in the final release.

As someone who has been doing a lot of work with SharePoint over the past three years, I would also have liked to see some tighter integration with standard SharePoint stuff.  For example, if you have a web database which assigns items to users (extremely common in SharePoint) I would want to use alerts, the out of the box personalisation features that we take for granted (custom views that use a [me] filter for example).  I couldn’t find any easy way to do this with an Access Service database.  Equally important for anyone thinking of trying this out, a published Access web database can’t be opened in SharePoint Designer 2010.  So if you want to use any of these features and you are moving data from Access your best bet could be to migrate the Access tables to SharePoint lists in a normal SharePoint site, then do your additional customisations rather than using an Access Services web database.  I can understand the reasons why Access Services doesn’t allow this (it needs to retain Access client fidelity) but it kind of eliminates a lot of the reasons why you want to use the SharePoint platform in the first place.  I suppose what I’m really looking for is a way of publishing the web database to SharePoint then disconnecting from the original Access app so I can continue my customisations on the SharePoint side.

Personally I think it’s unlikely that many existing, complex Access apps will be migrated to Access Services as there will be too many incompatible features.  It’s possible that some smaller departmental applications might get migrated, but this still require support from the IT Department which I’m not sure exists in lots of places.  And if I was designing a new application, first I would look hard at the new code-free development options in 2010.  We have what looks like a better version of SharePoint Designer, SPD workflows which can be re-used in multiple lists, list forms that can be customised in InfoPath, even (finally) options to enforce relationship behaviour in lists.  And many of these features will be available across all versions, so you won’t necessarily need SharePoint 2010 Enterprise.

Whereas Robert thinks SharePoint reporting is one of the key areas where where Access Services fits in, for me, I think the real opportunity is in hosted SharePoint 2010 solutions.  With Access 2010 you could develop an in-house application, or possibly a commercial one and make this available as both a rich client or easily extend reach using a hosted version of SharePoint 2010 enterprise.  This could have strong appeal to the many developers who have vertical market Access applications out there already and means they could offer a hosted browser based version of an application, even to small, distributed groups of users who can’t afford or don’t want their own in-house server. 

So that’s my take on Access Services.  I’m interested to hear what you think, particularly if you get to try it out yourself so as usual comments are welcome.  If you would like to try out the hosting option for yourself, you can get a free trial right now from Access Hosting.  Then all you need to get started is a download of Office 2010 beta.

Here are some links you might find useful of you plan to try out Access 2010 and Access Services:

Access Product Team Blog: Access Services Overview and Install Information

Access Product Team Blog: Publish to SharePoint (part 1)

The Access Show: Access 2010 Demo of Access Services and Web Databases

The Access Show: Publish an Access 2007 Database to SharePoint with Access 2010

Albert Kallal’s Access 2010 web database demo video

Posted in development, Microsoft SharePoint, Office, SQL Server, web development | 26 Comments »

Family Show – Showing Off WPF with a Genealogy Application

Posted by workerthread on May 4, 2009

US Company Vertigo have a long track record in the development of showcase solutions for a range of clients, and have been involved in several proof of concept projects for Microsoft going back over the years.  Right now they have a very nice Windows Presentation Foundation project available for download called Family Show.  The latest version of this has been put together using Visual Studio 2008 and .Net 3.5, and certainly shows off some nice user interface design features.  If you are interested in UI design, or indeed if you have an interest in genealogy, this is worth a download.

Here’s a quick screenshot of how it looks (click picture for a better view):


(by the way, it’s not one of my relatives 🙂 )

So if you want to take a look for yourself, the download is available here, and source code is available as a CodePlex project here.  Scott Stanfield from Vertigo has also recorded a video demo of Family Show which you can watch here.

Posted in development, Technology, Training | Comments Off on Family Show – Showing Off WPF with a Genealogy Application