Excel Services Deployment: Changing Data Connection Libraries url from code

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

9 Responses to Excel Services Deployment: Changing Data Connection Libraries url from code

  1. Michael Meyer says:

    I am having the same problem – so thanks for posting this.

    Are you running this directly on the SharePoint server or from a client workstation?

  2. oricode says:

    This code runs at the Sharepoint server, when the feature is deployed

  3. Michael Meyer says:

    So how do you deploy/install the code you have written?

    Thanks

  4. oricode says:

    It’s on the post 🙂 I provisioned the files to the server using a Sharepoint feature, then I used a Feature Receiver class that gets called whenever the feature is Activated to process the files and do the replacements in the connection strings

  5. Michael Meyer says:

    For “” are you hard coding in a value or how does this work to change it between Test and Prod?

  6. Michael Meyer says:

    Last post didn’t take but what I was asking is whether you are hard coding the new connection string or how did you accomplish getting the new connection string applied.

  7. oricode says:

    Well in my case the connection strings I was replacing were pointing to web services deployed at the _vti_bin folder of the Sharepoint site so the only thing I did on the feature receiver is to get the url of the site were the feature was being activated and then replace the host url of the excel file being parced with the value from the current site were the feature was being deployed.

  8. Michael Meyer says:

    Last question I hope (-: For the web.lists is this the data connection library files or is it suppose to be the excel work books that have the sheets and charts you want to publish?

  9. Victor says:

    Wow, it is very helpful post, at least for me 🙂
    Thank you very much !!!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: