internal void ExecuteSyncDescriptions(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox sandbox, IServiceProvider provider, string tableName) { try { #if DENALI || SQL2014 var db = sandbox.Database; Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.AMOCode code; #else Database db = null; if (!sandbox.IsTabularMetadata) { db = ((Microsoft.AnalysisServices.BackEnd.DataModelingSandboxAmo)sandbox.Impl).Database; } else { db = null; } Microsoft.AnalysisServices.BackEnd.AMOCode code; #endif code = delegate { int iDescriptionsSet; Microsoft.AnalysisServices.BackEnd.SandboxTransactionProperties properties = new Microsoft.AnalysisServices.BackEnd.SandboxTransactionProperties(); properties.RecalcBehavior = Microsoft.AnalysisServices.BackEnd.TransactionRecalcBehavior.Default; using (Microsoft.AnalysisServices.BackEnd.SandboxTransaction tran = sandbox.CreateTransaction(properties)) { if (!TabularHelpers.EnsureDataSourceCredentials(sandbox)) { MessageBox.Show("Cancelling Sync Descriptions because data source credentials were not entered.", "BIDS Helper Tabular Sync Descriptions - Cancelled!"); tran.RollbackAndContinue(); return; } #if !(DENALI || SQL2014) Microsoft.AnalysisServices.BackEnd.DataModelingTable table = sandbox.Tables[tableName]; if (table.IsStructuredDataSource) { MessageBox.Show("BI Developer Extensions does not yet support modern (Power Query) data sources.", "BI Developer Extensions"); return; } iDescriptionsSet = SyncDescriptionsPlugin.SyncDescriptions(table, true); if (iDescriptionsSet > 0) { table.UpdateNowOrLater(); } #else Dimension d = db.Dimensions.GetByName(tableName); iDescriptionsSet = SyncDescriptionsPlugin.SyncDescriptions(d, true, provider, true); if (iDescriptionsSet > 0) { db.Update(UpdateOptions.ExpandFull); } #endif tran.GetType().InvokeMember("Commit", System.Reflection.BindingFlags.Instance | System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.Public, null, tran, null); //The .Commit() function used to return a list of strings, but in the latest set of code it is a void method which leads to "method not found" errors //tran.Commit(); } MessageBox.Show("Set " + iDescriptionsSet + " descriptions successfully.", "BIDS Helper - Sync Descriptions"); }; #if DENALI || SQL2014 sandbox.ExecuteAMOCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true); #else sandbox.ExecuteEngineCode(Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationType.Update, Microsoft.AnalysisServices.BackEnd.DataModelingSandbox.OperationCancellability.AlwaysExecute, code, true); #endif } catch (System.Exception ex) { MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "BIDS Helper - Error"); } }
internal static int SyncDescriptions(Microsoft.AnalysisServices.BackEnd.DataModelingTable d, bool bPromptForProperties) { int iUpdatedDescriptions = 0; Microsoft.AnalysisServices.BackEnd.EditMappingUtility util = new Microsoft.AnalysisServices.BackEnd.EditMappingUtility(d.Sandbox); var conn = ((Microsoft.AnalysisServices.BackEnd.RelationalDataStorage)((util.GetDataSourceConnection(util.GetDataSourceID(d.Id), d.Sandbox)))).DataSourceConnection; conn.Open(); System.Data.Common.DbCommand cmd = conn.CreateCommand(); string sDBTableName = d.SourceTableName; sq = conn.Cartridge.IdentStartQuote; fq = conn.Cartridge.IdentEndQuote; //cartridge = conn.Cartridge; if (conn.SourceType != Microsoft.AnalysisServices.BackEnd.DataSourceType.SqlServer && conn.SourceType != Microsoft.AnalysisServices.BackEnd.DataSourceType.SqlAzure) { MessageBox.Show("Data source [" + conn.ConnectionName + "] connects to " + conn.SourceType.ToString() + " which may not be supported."); } String sql = "select distinct Name from sys.extended_properties order by Name"; if (bPromptForProperties) { SSAS.SyncDescriptionsForm form = new SSAS.SyncDescriptionsForm(); cmd.CommandText = sql; System.Data.Common.DbDataReader reader = cmd.ExecuteReader(); List <string> listNames = new List <string>(); while (reader.Read()) { listNames.Add(Convert.ToString(reader["Name"])); form.listOtherProperties.Items.Add(Convert.ToString(reader["Name"])); } reader.Close(); form.cmbDescriptionProperty.DataSource = listNames; DialogResult result = form.ShowDialog(); if (result != DialogResult.OK) { return(iUpdatedDescriptions); } DescriptionPropertyName = form.cmbDescriptionProperty.GetItemText(form.cmbDescriptionProperty.SelectedItem); List <string> listOtherProperties = new List <string>(); for (int i = 0; i < form.listOtherProperties.CheckedItems.Count; i++) { listOtherProperties.Add(form.listOtherProperties.GetItemText(form.listOtherProperties.CheckedItems[i])); } OtherPropertyNamesToInclude = listOtherProperties.ToArray(); OverwriteExistingDescriptions = form.chkOverwriteExistingDescriptions.Checked; } if ((string.IsNullOrEmpty(d.Description) || OverwriteExistingDescriptions) && !string.IsNullOrEmpty(sDBTableName)) { sql = "SELECT PropertyName = p.name" + "\r\n" + ",PropertyValue = CAST(p.value AS sql_variant)" + "\r\n" + "FROM sys.all_objects AS tbl" + "\r\n" + "INNER JOIN sys.schemas sch ON sch.schema_id = tbl.schema_id" + "\r\n" + "INNER JOIN sys.extended_properties AS p ON p.major_id=tbl.object_id AND p.minor_id=0 AND p.class=1" + "\r\n" //+ "where sch.name = '" + oDimensionKeyTable.ExtendedProperties["DbSchemaName"].ToString().Replace("'", "''") + "'\r\n" + "where tbl.object_id = object_id('" + sDBTableName.Replace("'", "''") + "')\r\n" + "order by p.name"; string sNewDimensionDescription = ""; //DataSet dsTableProperties = new DataSet(); cmd.CommandText = sql; System.Data.Common.DbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { if (string.Compare((string)reader["PropertyName"], DescriptionPropertyName, true) == 0) { sNewDimensionDescription = (string)reader["PropertyValue"]; } } reader.Close(); reader = cmd.ExecuteReader(); while (reader.Read()) { foreach (string sProp in OtherPropertyNamesToInclude) { if (string.Compare((string)reader["PropertyName"], sProp, true) == 0 && !string.IsNullOrEmpty((string)reader["PropertyValue"])) { if (sNewDimensionDescription.Length > 0) { sNewDimensionDescription += "\r\n"; } sNewDimensionDescription += (string)reader["PropertyName"] + ": " + (string)reader["PropertyValue"]; } } } reader.Close(); if (!string.IsNullOrEmpty(sNewDimensionDescription)) { d.Description = sNewDimensionDescription; iUpdatedDescriptions++; } } foreach (Microsoft.AnalysisServices.BackEnd.DataModelingColumn a in d.Columns) { if (a.IsRowNumber || a.IsCalculated) { continue; } if ((string.IsNullOrEmpty(a.Description) || OverwriteExistingDescriptions) && (!string.IsNullOrEmpty(sDBTableName))) { sql = "SELECT PropertyName = p.name" + "\r\n" + ",PropertyValue = CAST(p.value AS sql_variant)" + "\r\n" + "FROM sys.all_objects AS tbl" + "\r\n" + "INNER JOIN sys.schemas sch ON sch.schema_id = tbl.schema_id" + "\r\n" + "INNER JOIN sys.all_columns AS clmns ON clmns.object_id=tbl.object_id" + "\r\n" + "INNER JOIN sys.extended_properties AS p ON p.major_id=clmns.object_id AND p.minor_id=clmns.column_id AND p.class=1" + "\r\n" //+ "where sch.name = '" + oDsvTable.ExtendedProperties["DbSchemaName"].ToString().Replace("'", "''") + "'\r\n" + "where tbl.object_id = object_id('" + sDBTableName.Replace("'", "''") + "')\r\n" + "and clmns.name = '" + a.DBColumnName.Replace("'", "''") + "'\r\n" + "order by p.name"; string sNewDescription = ""; cmd.CommandText = sql; System.Data.Common.DbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { if (string.Compare((string)reader["PropertyName"], DescriptionPropertyName, true) == 0) { sNewDescription = (string)reader["PropertyValue"]; } } reader.Close(); cmd.CommandText = sql; reader = cmd.ExecuteReader(); while (reader.Read()) { foreach (string sProp in OtherPropertyNamesToInclude) { if (string.Compare((string)reader["PropertyName"], sProp, true) == 0 && !string.IsNullOrEmpty((string)reader["PropertyValue"])) { if (sNewDescription.Length > 0) { sNewDescription += "\r\n"; } sNewDescription += (string)reader["PropertyName"] + ": " + (string)reader["PropertyValue"]; } } } reader.Close(); if (!string.IsNullOrEmpty(sNewDescription)) { a.Description = sNewDescription; iUpdatedDescriptions++; } } } return(iUpdatedDescriptions); }