Automate moving data connected Visio diagrams
As many of you know, connecting data to Visio diagrams is easy and sharing them via Visio Services in SharePoint is even easier. But what happens when you decide to move a data connected Visio diagram to another site, site collection, or even a different farm?
If you take a Visio diagram that is connected to data and move it to a different location you might break the data connection depending on what type of data connections you are connected to.
Excel Services
If you are connected to data in a Excel Workbook the data connection stores the name and location of the Excel Workbook…
DataModule=Microsoft.Office.Visio.Server.EcsDataHandler,Microsoft.Office.Visio.Server; Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source= https://spdemo/demo/SupplyChainDemo.xlsx;Mode=Read;Extended Properties="HDR=YES;IMEX=1;MaxScanRows=0;Excel 12.0;";Jet OLEDB:System database="";Jet OLEDB:Registry Path="";Jet OLEDB:Engine Type=37;Jet OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database Password="";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False;Jet OLEDB:Support Complex Data=False;Jet OLEDB:Bypass UserInfo Validation=False;Jet OLEDB:Limited DB Caching=False;Jet OLEDB:Bypass ChoiceField Validation=False
Moving this Visio diagram, depending on where you move it, may allow for it to continue to read data from this location. Most scenarios that I see from customers involve moving the data and the diagram to another server.
SQL & SQL Azure
If you are connecting your diagrams to SQL or SQL azure you may have a direct connection to SQL or you might be using an ODC file ( recommended ). The data connection stores the full path to the ODC file as shown below.
Filename = https://spdemo/Data Connections/gd212.database.windows.net.odc
This needs to be updated after you move the Visio diagram ( and the ODC file ) to a new site or farm.
SharePoint List & BCS
If you are connected to data from a SharePoint list or an external list, the data connection records the UniqueID of the list as well as the name of the site that contains the list.
PROVIDER=WSS;DATABASE=https://visio15demo;LIST={FAEEF716-07E1-4F22-AB65-476C4ABFB599};
Both of these properties need to be updated after moving the Visio diagram to a new site or farm.
In most cases you probably need to open the diagram in Visio and rerun the data connection wizard in order to repair the connection to the data source. This is a common scenario for me as I develop demos for myself and my customers and I need to move them to other SharePoint Farms or SharePoint sites in Office 365.
Attached to this article is a SharePoint sandboxed solution that I put together to demonstrate how you can automate the process of fixing data connections in a Visio diagram after moving them to another SharePoint site, farm, etc. The sample uses a sandboxed solution and the FeatureActivated event to trigger the process of updating the data connections in all the VSDX files that are found in my site. If you review the code you will see that I am only dealing with data connections that are connected to SharePoint lists. I find the name of the original list, find this list on the new target site that contains the diagram, and then update the data connection appropriately.
Your logic and scenario might be different but I think you will find the attached solution code useful in helping you to solve this issue.
FYI - this sample code also demonstrates how to edit an OpenXML based document stored in a document library, in-memory, from a sandboxed solution. This technique makes this solution perfect for SharePoint in Office 365 where only sandboxed solutions are allowed.