Tell me more ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.
up vote 4 down vote favorite
1

I currently have dozens of Excel workbooks being displayed in Excel services on our development Sharepoint server. The workbooks are setup to use an .ODC connection file which is hosted in a trusted connection library in the dev sharepoint server.

Everything works fine on our development Sharepoint server http://dev.*** as the workbooks are configured to use the http://dev.***/connection.odc url for the connection file. However, once the site is deployed onto our uat http://uat.*** server then the workbooks fail as they are still looking for their connections from the dev server.

Can someone please provide me with an overview of how to manage this scenario whereby there is absolute url based content being deployed across different environments?

One option I had considered was a post deployment powershell script to run through all lists, check each property and do a find / replace on "dev." to "uat.". Once I created the PowerShell script is there anyway to automate its execution on the uat server triggered by a succesful content deployment?

share|improve this question

2 Answers

up vote 0 down vote accepted

if you're keeping all odc files in one library:

  • List item go to sp library, open explorer
  • copy down all files to a local folder
  • (for testing: open an odc file in notepad. see pattern)
  • either create a c#/vb script or powershell script that replaces data source url. there is a vbscript on the web (google: replace all in folder)
  • run script
  • drag contents of folder back to sharepoint explorer.
  • stop start excel services.

manual, but not tedious. reasonable for a migration or deployment step.

share|improve this answer
@1v1cl1: I actually went with my own original suggestion which was also to powershell script the download, update and checkin of the odc files across all sites / webs / lists in order to update the odc absolute server paths. One thing though, no restart of excel services is required and therefore no disruption to users. – Brian Scott Sep 25 '12 at 8:09
Don't you need to update the ODC files and also the reference inside each Excel (.xlsx) file? Each Excel file could have Connection definitions that point to the dev server. This is a bit trickier... – Mike Honey Nov 28 '12 at 6:01
up vote 0 down vote

Another approach would be to have a set of ODC files per environment and deploy these using powershell. The powershell script has some logic (or hard-coded) that deploys the proper files to each environment. As part of your deployment you always run the powershell script.

I would discourage to put that kind of environment specific configuration in a feature or set of features.

BTW. you might want to have a look at the Secure Store to manage your connections instead of using a full connection string in the ODC file: http://technet.microsoft.com/en-us/library/hh525341.aspx. But still you will have a Secure Store application ID that is different for every environment.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.