Sharepoint deployment scenario with absolute urls involved

Question

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?

Answer 1

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.

Answer 2

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.