none
Change PowerPivot data source when deploying from Dev -> Staging - > Production

    Question

  • We have PowerPivot workbooks we store in SharePoint 2013 Libraries. 

    We have a powershell script which automates the deployment of sites and pages. Is there a way using PowerShell (or any scripting language really) to look into the workbook and change the PowerPivot data source when deployment from one environment to another? (Dev -> Stage -> Prod) We need to update the workbooks to point to our SSAS cubes Dev -> Stage -> Prod respectively.

    Thank you for any expert advice you may offer,

    Sincerely,

    -Craig

    Wednesday, July 23, 2014 3:44 PM

Answers

  • Basically, it's an excel file (XLSX) file and you can use excel OM for this.

    A simple program (in C# or any language of your choice) can be written to do the following and achieve what you need.

    *****************************************

    using XLS = Microsoft.Office.Interop.Excel;

    string info = null;
    XLS.Application application = null;

    try
    {
     application = new XLS.Application();

     var book = application.Workbooks.Open(filepath);

     foreach (XLS.WorkbookConnection wc in book.Connections)
     {
      switch (wc.Type)
      {
       case XLS.XlConnectionType.xlConnectionTypeOLEDB:
        info = wc.Name + " " + wc.OLEDBConnection.Connection.ToString() + "\r\n";
        break;
      }
     }

     book.Close();
    }
    finally
    {
     if (application != null)
     {
      application.Quit();
     }
    }

    *****************************************

    Hopefully, this was helpful.



    • Proposed as answer by Alok B Friday, August 01, 2014 4:14 AM
    • Marked as answer by determinism Friday, August 01, 2014 4:45 AM
    • Edited by Alok B Friday, August 01, 2014 6:59 AM correct answer
    Friday, August 01, 2014 4:13 AM

All replies

  • Hi Craig,

    Currently, it seems that PowerPivot doesn't support to update data source connection string via PowerShell or programatically.

    I will recommend you could submit a feedback to the Microsoft Connect at this link:
    https://connect.microsoft.com/SQLServer/Feedback


    Elvis Long
    TechNet Community Support

    Monday, July 28, 2014 11:44 AM
  • Basically, it's an excel file (XLSX) file and you can use excel OM for this.

    A simple program (in C# or any language of your choice) can be written to do the following and achieve what you need.

    *****************************************

    using XLS = Microsoft.Office.Interop.Excel;

    string info = null;
    XLS.Application application = null;

    try
    {
     application = new XLS.Application();

     var book = application.Workbooks.Open(filepath);

     foreach (XLS.WorkbookConnection wc in book.Connections)
     {
      switch (wc.Type)
      {
       case XLS.XlConnectionType.xlConnectionTypeOLEDB:
        info = wc.Name + " " + wc.OLEDBConnection.Connection.ToString() + "\r\n";
        break;
      }
     }

     book.Close();
    }
    finally
    {
     if (application != null)
     {
      application.Quit();
     }
    }

    *****************************************

    Hopefully, this was helpful.



    • Proposed as answer by Alok B Friday, August 01, 2014 4:14 AM
    • Marked as answer by determinism Friday, August 01, 2014 4:45 AM
    • Edited by Alok B Friday, August 01, 2014 6:59 AM correct answer
    Friday, August 01, 2014 4:13 AM