5

I have many reports in PPS which has around 60 data sources to analysis services. I want to change their connection string (i.e. I want to change the server which they are pointing to). I have tried below solution (Console application) to get the list of data sources available in SharePoint for PPS but can't see/modify connection strings.

static void Main(string[] args)
    {
        DataTable dt = new DataTable();
        dt = GetList("SharePoint site URL", "Data Connections");

    }

    public static DataTable GetList(string site, string listname)
    {
        ClientContext ctx = new ClientContext(site);

        List lst = ctx.Web.Lists.GetByTitle(listname);

        CamlQuery cq = CamlQuery.CreateAllItemsQuery();

        ListItemCollection lic = lst.GetItems(cq);

        ctx.Load(lic);

        ctx.ExecuteQuery();
        DataTable dt = new DataTable();

        foreach (var field in lic[0].FieldValues.Keys)
        {
            dt.Columns.Add(field);
        }

        foreach (var item in lic)
        {
            DataRow dr = dt.NewRow();

            foreach (var obj in item.FieldValues)
            {
                if (obj.Value != null)
                {
                    string type = obj.Value.GetType().FullName;

                    if (type == "Microsoft.SharePoint.Client.FieldLookupValue")
                    {
                        dr[obj.Key] = ((FieldLookupValue)obj.Value).LookupValue;
                    }
                    else if (type == "Microsoft.SharePoint.Client.FieldUserValue")
                    {
                        dr[obj.Key] = ((FieldUserValue)obj.Value).LookupValue;
                    }
                    else
                    {
                        dr[obj.Key] = obj.Value;
                    }
                }
                else
                {
                    dr[obj.Key] = null;
                }
            }

            dt.Rows.Add(dr);
        }

        return dt;
    }

With above code I am getting all data connections available in "Data Connections" list (below screenshot is the data table structure)

enter image description here

Unfortunately, there is no connection string in that information. I am not able to find any solution online.

Zerotoinfinity
  • 6,290
  • 32
  • 130
  • 206
  • Would this help: http://stackoverflow.com/questions/706566/database-connection-string-info – gregory Jan 21 '17 at 00:14
  • @gregory Thanks for replying but SharePoint PPS connection doesn't get stored in web.config. I believe they get strored in content database in encrypted format. :( – Zerotoinfinity Jan 23 '17 at 10:34

0 Answers0