Excel Services Deployment: Changing Data Connection Libraries url from code

November 17, 2008

Lately, I’ve been involved in a large Business Intelligence project where Sharepoint Excel Services was used as a front end for delivering Excel based BI dashboards. Although It wasn’t the first time that I worked with Excel Services, it was the biggest project of that kind I’ve ever been involved in. The problem I faced was with the deployment of the reports and I am really surprised that almost nobody has comed accross this issue before. Excel reports nice and fancy and every end user loves it, the problem comes when you need to set up a deployment strategy based on Sharepoint Features for different environments (Dev, Test & Prod) for delivering the reports. 

The issue we found is that Excel Services reports are using .odc data connections stored in a  Trusted Data Connection library at a Sharepoint server. At design time, when the report is created, you select a connection and add it to the current workbook. The problem is that the absolute url of the .odc file is stored in the same excel file ! It’s easy to figure out all kind of deployment issues caused by this behaviour: What happens when we are moving excel files from one environment (Test) to another (Prod) ? All excel documents have to be opened and each connection refreshed to point to the new Data Connection Library.

Ideally, I wanted to have a named data connections model just like the reporting services platform has. A reporting site with a fixed Data Connection Library would be created and connections could be refered by their name and centrally managed at that reporting site level. Unfortunately, this is not the behaviour so I had to come up with a different solution.

What I did is add in a feature receiver class of my excel provisioning feature (responsible for adding all the excel reports to my reporting site) that would open each excel file with OpenXml libraries and manually change the urls of all the connections to the new url. Following is a description of that process and the code of my feature activated event where this code is triggered. As a restriction, I had to use .Net framework 3.0 instead of 3.5, this is why the code used is based on OpenXml SDK 1.0 and not 2.0 !!

public override void FeatureActivated(SPFeatureReceiverProperties properties)
{            
    SPWeb web = (SPWeb)properties.Feature.Parent;

    SPList list = web.Lists["<name_of_data_connection_library>"];

    //iterate every report of the document library
    foreach (SPListItem report in list.Items)
    {                
        Stream s = report.File.OpenBinaryStream();

        //open package
        using (SpreadsheetDocument xlPackage = SpreadsheetDocument.Open(s, true))
        {
            ConnectionsPart connectionsPart = xlPackage.WorkbookPart.ConnectionsPart;
            Stream connectionsStr = connectionsPart.GetStream();
            XmlDocument doc = new XmlDocument();
            doc.Load(connectionsStr);

            XmlNamespaceManager nsManager = new XmlNamespaceManager(doc.NameTable);
            nsManager.AddNamespace("default", doc.DocumentElement.NamespaceURI);
            XmlNodeList nodelist = doc.SelectNodes("//default:connections/default:connection", nsManager);

            //iterate connections part
            foreach (XmlNode node in nodelist)
            {
                //set new url
                node.Attributes["odcFile"].Value = "<new_connection_string>";
            }

            using (StreamWriter sw = new StreamWriter(connectionsPart.GetStream(FileMode.Create)))
            {
                doc.Save(sw.BaseStream);
            }

            //save sharepoint item
            report.File.SaveBinary(s);
        }
    }
}

That’s all ! I hope you find it usefull and please leave a comment if so !

Advertisements