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)
Unfortunately, there is no connection string in that information. I am not able to find any solution online.