private void ExecInternal(ProjectItem projItem, DataModelingSandbox sandbox) { #if DENALI || SQL2014 var db = sandbox.Database; #else var db = ((DataModelingSandboxAmo)sandbox.Impl).Database; #endif // extract deployment information DeploymentSettings deploySet = new DeploymentSettings(projItem); ApplicationObject.StatusBar.Progress(true, "Deploying Tabular Database", 3, 5); // Connect to Analysis Services Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server(); svr.Connect(deploySet.TargetServer); ApplicationObject.StatusBar.Progress(true, "Deploying Tabular Database", 4, 5); // execute the xmla try { Microsoft.AnalysisServices.Scripter scr = new Microsoft.AnalysisServices.Scripter(); Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase); if (targetDB == null) { throw new System.Exception( string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer)); } StringBuilder sb = new StringBuilder(); XmlWriterSettings xws = new XmlWriterSettings(); xws.OmitXmlDeclaration = true; xws.ConformanceLevel = ConformanceLevel.Fragment; XmlWriter xwrtr = XmlWriter.Create(sb, xws); // TODO - do we need different code for JSON based models?? scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { db }, xwrtr, true); // update the MDX Script XmlaResultCollection xmlaRC = svr.Execute(sb.ToString()); if (xmlaRC.Count == 1 && xmlaRC[0].Messages.Count == 0) { // all OK - 1 result - no messages } else { StringBuilder sbErr = new StringBuilder(); for (int iRC = 0; iRC < xmlaRC.Count; iRC++) { for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++) { sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description); } } MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy Tabular Database"); } } catch (Exception ex) { MessageBox.Show(ex.ToString(), "BIDSHelper - Deploy Tabular Database - Exception"); package.Log.Exception("Deploy Tabular Database Failed", ex); } }
public override async Task <ActionResponse> ExecuteActionAsync(ActionRequest request) { var azureToken = request.DataStore.GetJson("AzureTokenAS"); string serverUrl = request.DataStore.GetValue("ASServerUrl"); string xmla = request.DataStore.GetValue("xmlaFilePath"); string asDatabase = request.DataStore.GetValue("ASDatabase"); string sqlConnectionString = request.DataStore.GetValue("SqlConnectionString"); var connectionStringObj = SqlUtility.GetSqlCredentialsFromConnectionString(sqlConnectionString); string connectionString = ValidateConnectionToAS.GetASConnectionString(request, azureToken, serverUrl); string xmlaContents = File.ReadAllText(request.Info.App.AppFilePath + "/" + xmla); Server server = null; try { server = new Server(); server.Connect(connectionString); // Delete existing Database db = server.Databases.FindByName(asDatabase); db?.Drop(); // Deploy database definition var obj = JsonUtility.GetJsonObjectFromJsonString(xmlaContents); obj["create"]["database"]["name"] = asDatabase; XmlaResultCollection response = server.Execute(obj.ToString()); if (response.ContainsErrors) { return(new ActionResponse(ActionStatus.Failure, response[0].Value)); } // Reload metadata and update connection string server.Refresh(true); db = server.Databases.FindByName(asDatabase); ((ProviderDataSource)db.Model.DataSources[0]).ConnectionString = $"Provider=SQLNCLI11;Data Source=tcp:{connectionStringObj.Server};Persist Security Info=True;User ID={connectionStringObj.Username};Password={connectionStringObj.Password};Initial Catalog={connectionStringObj.Database}"; db.Update(UpdateOptions.ExpandFull); // Process if there's a tag requesting it if (db.Model.DataSources[0].Annotations.ContainsName("MustProcess")) { db.Model.RequestRefresh(AnalysisServices.Tabular.RefreshType.Full); db.Model.SaveChanges(); } server.Disconnect(true); return(new ActionResponse(ActionStatus.Success)); } catch (Exception e) { return(new ActionResponse(ActionStatus.Failure, string.Empty, e, null)); } finally { server?.Dispose(); } }
/// <summary> /// Cube full process /// </summary> /// <param name="cube_server"></param> /// <param name="cubeDBName"></param> /// <param name="xmla"></param> public void CUBE_PROCESS_FULL(DB_SQLHELPER_BASE sqlHelper, Server cube_server, String cubeDBName) { try { String cubeProcessXMLAPath = CONFIGURATION_HELPER.BASIC_CONFIGURATION_FOLDER + @"\SSASConfiguration\CubeProcess.xml"; String cubeProcessXMLA = System.IO.File.ReadAllText(cubeProcessXMLAPath); cubeProcessXMLA = cubeProcessXMLA.Replace("$(cubeDBName)", cubeDBName); XmlaResultCollection _result = cube_server.Execute(cubeProcessXMLA); foreach (XmlaResult _res in _result) { foreach (XmlaMessage message in _res.Messages) { sqlHelper.ADD_MESSAGE_LOG( message.ToString(), MESSAGE_TYPE.CUBE_PROCESS, MESSAGE_RESULT_TYPE.Normal); } } sqlHelper.ADD_MESSAGE_LOG( String.Format("Processed cube {0}", cubeDBName), MESSAGE_TYPE.CUBE_PROCESS, MESSAGE_RESULT_TYPE.Normal); } catch (Exception ex) { sqlHelper.ADD_MESSAGE_LOG(ex.Message.ToString(), MESSAGE_TYPE.CUBE_PROCESS, MESSAGE_RESULT_TYPE.Error); throw (ex); } }
public override async Task <ActionResponse> ExecuteActionAsync(ActionRequest request) { string xmla = request.DataStore.GetValue("xmlaFilePath"); string asDatabase = request.DataStore.GetValue("ASDatabase"); string sqlConnectionString = request.DataStore.GetValue("SqlConnectionString"); var connectionStringObj = SqlUtility.GetSqlCredentialsFromConnectionString(sqlConnectionString); string connectionString = request.DataStore.GetValue("ASConnectionString"); string xmlaContents = File.ReadAllText(request.Info.App.AppFilePath + "/" + xmla); using (Server server = new Server()) { try { server.Connect(connectionString); // Delete existing Database db = server.Databases.FindByName(asDatabase); db?.Drop(); // Deploy database definition XmlaResultCollection response = server.Execute(xmlaContents); if (response.ContainsErrors) { return(new ActionResponse(ActionStatus.Failure, response[0].Value)); } // Reload metadata and update connection string server.Refresh(true); db = server.Databases.FindByName(asDatabase); ((ProviderDataSource)db.Model.DataSources[0]).ConnectionString = $"Provider=SQLNCLI11;Data Source=tcp:{connectionStringObj.Server};Persist Security Info=True;User ID={connectionStringObj.Username};Password={connectionStringObj.Password};Initial Catalog={connectionStringObj.Database}"; db.Update(UpdateOptions.ExpandFull); } catch (Exception e) { return(new ActionResponse(ActionStatus.Failure, string.Empty, e, null, "AS Database was not deployed")); } server.Disconnect(true); } return(new ActionResponse(ActionStatus.Success)); }
/// <summary> /// Create ssas base cube by calling SSAS API /// </summary> /// <param name="cubeServer">Cube server</param> /// <param name="cubeDBName">Cube data base name</param> /// <param name="cubeXmla">Base cube xmla</param> public void CREATE_SSAS_BASE_CUBE( DB_SQLHELPER_BASE sqlHelper, Server cubeServer, String cubeDBName, String cubeName, String dwConnectionString ) { String SSASConfigurationPath = CONFIGURATION_HELPER.BASIC_CONFIGURATION_FOLDER + @"\SSASConfiguration\BaseCubeXMLA.xml"; String SSASConfiguration = ""; System.Security.Principal.NTAccount _Everyone_Account = new System.Security.Principal.NTAccount("Everyone"); System.Security.Principal.SecurityIdentifier _SecurityIdentifier = (System.Security.Principal.SecurityIdentifier)_Everyone_Account.Translate(typeof(System.Security.Principal.SecurityIdentifier)); String sidString = _SecurityIdentifier.ToString(); SSASConfiguration = System.IO.File.ReadAllText(SSASConfigurationPath); SSASConfiguration = SSASConfiguration .Replace("$(dwConnectionString)", dwConnectionString) .Replace("$(cubeDBName)", cubeDBName) .Replace("$(cubeName)", cubeName) .Replace("$(DBTableSchemaName)", CONFIGURATION_HELPER.GET_METADATA_PROPERTY("db_table_schema_name")) .Replace("$(sid)", sidString); sqlHelper.ADD_MESSAGE_LOG(String.Format("[Create base cube] Starting create cube database {0}", cubeDBName), MESSAGE_TYPE.CREATE_CUBE, MESSAGE_RESULT_TYPE.Normal); XmlaResultCollection resultCol = cubeServer.Execute(SSASConfiguration); foreach (XmlaResult result in resultCol) { foreach (XmlaMessage message in result.Messages) { if (message.ToString().Contains("error") || message.ToString().Contains("failed")) { sqlHelper.ADD_MESSAGE_LOG("[Create base cube]" + message.ToString(), MESSAGE_TYPE.CREATE_CUBE, MESSAGE_RESULT_TYPE.Error); return; } else { sqlHelper.ADD_MESSAGE_LOG("[Create base cube]" + message.ToString(), MESSAGE_TYPE.CREATE_CUBE, MESSAGE_RESULT_TYPE.Succeed); } } } sqlHelper.ADD_MESSAGE_LOG("[Create base cube]" + String.Format("Succeed to create cube database {0}", cubeDBName), MESSAGE_TYPE.CREATE_CUBE, MESSAGE_RESULT_TYPE.Succeed); }
//===================================================================== // // Summary: Sample code for running XMLA scripts with AMO. // //--------------------------------------------------------------------- // // Copyright (C) Microsoft Corporation. All rights reserved. // // THIS CODE AND INFORMATION ARE PROVIDED "AS IS" WITHOUT WARRANTY OF // ANY KIND, EITHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO // THE IMPLIED WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A // PARTICULAR PURPOSE. // //===================================================================== /// <summary> /// Connects to Analysis Services and sends user-specified commands with AMO. /// </summary> /* * static int Main(string[] args) * { * //-------------------------------------------------------------------------------- * // In this sample code, the XML/A protocol is mentioned; to read * // about it: http://www.xmla.org/. * //-------------------------------------------------------------------------------- * * try * { * Server server = new Server(); * * server.Connect("Data Source=localhost"); * * try * { * // Run an empty Batch command (there will be no results displayed). * ServerExecute(server, "<Batch xmlns='http://schemas.microsoft.com/analysisservices/2003/engine'/>"); * * // Run an invalid command, to get errors. * ServerExecute(server, "<RandomXmlElementHereNotRecognizedByAnalysisServices/>"); * * // Run another invalid command. * ServerExecute(server, "<InvalidXmlFragment"); * * // Run a custom SOAP Envelope request. This allows user the full control over * // what is sent to the Analysis Services server and also full control over parsing * // the result. * StartAndEndXmlaRequest(server); * * // Run a custom SOAP Envelope request read from a stream. * // Useful to run a full SOAP Envelope from a file. * SendXmlaRequestFromStream(server); * * return 0; * } * finally * { * server.Disconnect(); * } * } * catch (Exception e) * { * Console.Error.WriteLine(e.ToString()); * return 1; * } * } */ /// <summary> /// Sends the specified command to Analysis Services server and displays the results in Console. /// </summary> private static void ServerExecute(Server server, string command) { //-------------------------------------------------------------------------------- // The Server.Execute method returns a collection of XmlaResult objects (and not // just a single result) because the command being executed can be a Batch // containing multiple commands, each with its own result. // Each XmlaResult objects has 2 parts: // - the Value (as a string) // - the Messages: errors and/or warnings // A command can be successful and have one or more warnings. But if at least one // error is returned in the Messages section (combined eventually with warnings), // the command failed. //-------------------------------------------------------------------------------- Console.WriteLine(); Console.WriteLine(); Console.WriteLine("========== EXECUTE =========="); Console.WriteLine(command); XmlaResultCollection results = server.Execute(command); foreach (XmlaResult result in results) { Console.WriteLine("-----------------------------"); Console.WriteLine("VALUE: {0}", result.Value); foreach (XmlaMessage message in result.Messages) { if (message is XmlaError) { Console.WriteLine("ERROR: {0}", message.Description); } else { System.Diagnostics.Debug.Assert(message is XmlaWarning); Console.WriteLine("WARNING: {0}", message.Description); } } } }
//executes the commands that we have scripted using AMO previously public static void ExecuteCaptureLog(Server server, bool parallel, bool useTransaction) { //execute XMLA XmlaResultCollection oPrepResults = server.ExecuteCaptureLog(useTransaction, parallel); server.CaptureLog.Clear(); string sErrors = string.Empty; string sWarnings = string.Empty; //Check for errors. If there are any, mark the processing as failed and capture errors. foreach (XmlaResult oPrepResult in oPrepResults) { foreach (XmlaMessage oPrepMessage in oPrepResult.Messages) { if (oPrepMessage is XmlaError) { XmlaError oError = (XmlaError)oPrepMessage; sErrors += "ERROR " + oError.ErrorCode + " - " + oPrepMessage.Description + Environment.NewLine; } else if (oPrepMessage is XmlaWarning) { XmlaWarning oWarning = (XmlaWarning)oPrepMessage; sWarnings += "WARNING " + oWarning.WarningCode + " - " + oPrepMessage.Description + Environment.NewLine; } else { sWarnings += "WARNING - " + oPrepMessage.Description + Environment.NewLine; } } } if (!string.IsNullOrEmpty(sErrors)) { throw new Exception(sErrors + sWarnings); } }
private void ExecuteScript() { if (this.InputFile == null) { Log.LogError("InputFile is required"); return; } using (StreamReader fileStream = File.OpenText(this.InputFile.GetMetadata("FullPath"))) { XmlaResultCollection resultCollection = this.server.Execute(fileStream.ReadToEnd()); foreach (XmlaResult xmlaResult in resultCollection) { IEnumerable <XmlaError> errors = xmlaResult.Messages.OfType <XmlaError>(); IEnumerable <XmlaWarning> warnings = xmlaResult.Messages.OfType <XmlaWarning>(); foreach (XmlaWarning xmlaWarning in warnings) { this.LogTaskWarning(string.Format(CultureInfo.CurrentCulture, "XMLA warning code: {0}\nDescription: {1}\nSource: {2}\nHelpFile: {3}", xmlaWarning.WarningCode, xmlaWarning.Description, xmlaWarning.Source, xmlaWarning.HelpFile)); } if (errors.FirstOrDefault() != default(XmlaError)) { foreach (XmlaError xmlaError in errors) { Log.LogError(string.Format(CultureInfo.CurrentCulture, "XMLA error code: {0}\nDescription: {1}\nSource: {2}\nHelpFile: {3}", xmlaError.ErrorCode, xmlaError.Description, xmlaError.Source, xmlaError.HelpFile)); } return; } this.LogTaskMessage(string.Format(CultureInfo.CurrentCulture, "XMLA request executed with success ({0} warnings)", warnings.Count())); } } }
public static void DeployAggDesigns(ProjectItem projItem, DTE2 ApplicationObject) { Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object; bool bFoundAggDesign = false; foreach (MeasureGroup mg in oCube.MeasureGroups) { if (mg.AggregationDesigns.Count > 0) { bFoundAggDesign = true; break; } } if (!bFoundAggDesign) { MessageBox.Show("There are no aggregation designs defined in this cube yet."); return; } if (MessageBox.Show("This command deploys just the aggregation designs in this cube. It does not change which aggregation design is assigned to each partition.\r\n\r\nYou should run a ProcessIndex command from Management Studio on this cube after aggregation designs have been deployed.\r\n\r\nDo you wish to continue?", "BIDS Helper - Deploy Aggregation Designs", MessageBoxButtons.YesNo) != DialogResult.Yes) { return; } try { ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy); ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 1, 5); string sPartitionsFileName = projItem.get_FileNames(1); sPartitionsFileName = sPartitionsFileName.Substring(0, sPartitionsFileName.Length - 5) + ".partitions"; // Check if the file is read-only (and probably checked in to a source control system) // before attempting to save. (issue: 10327 ) FileAttributes fa = System.IO.File.GetAttributes(sPartitionsFileName); if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly) { //TODO - prompt before saving? //Save the cube projItem.Save(""); } ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 2, 5); // extract deployment information DeploymentSettings deploySet = new DeploymentSettings(projItem); // use xlst to create xmla alter command XslCompiledTransform xslt = new XslCompiledTransform(); XmlReader xsltRdr; XmlReader xrdr; // read xslt from embedded resource xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployAggDesigns)); using ((xsltRdr)) { // read content from .partitions file xrdr = XmlReader.Create(sPartitionsFileName); using (xrdr) { ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 3, 5); // Connect to Analysis Services Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server(); svr.Connect(deploySet.TargetServer); ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 4, 5); // execute the xmla try { // Build up the Alter MdxScript command using XSLT against the .partitions file XslCompiledTransform xslta = new XslCompiledTransform(); StringBuilder sb = new StringBuilder(); XmlWriterSettings xws = new XmlWriterSettings(); xws.OmitXmlDeclaration = true; xws.ConformanceLevel = ConformanceLevel.Fragment; XmlWriter xwrtr = XmlWriter.Create(sb, xws); xslta.Load(xsltRdr); XsltArgumentList xslarg = new XsltArgumentList(); Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase); if (targetDB == null) { throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer)); } xslarg.AddParam("TargetDatabase", "", targetDB.ID); xslarg.AddParam("TargetCubeID", "", oCube.ID); xslta.Transform(xrdr, xslarg, xwrtr); Cube oServerCube = targetDB.Cubes.Find(oCube.ID); if (oServerCube == null) { throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer)); } // update the agg designs XmlaResultCollection xmlaRC = svr.Execute(sb.ToString()); StringBuilder sbErr = new StringBuilder(); for (int iRC = 0; iRC < xmlaRC.Count; iRC++) { for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++) { if (!string.IsNullOrEmpty(xmlaRC[iRC].Messages[iMsg].Description)) { sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description); } } } if (sbErr.Length > 0) { MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy Aggregation Designs"); } projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false); } catch (System.Exception ex) { if (MessageBox.Show("The following error occured while trying to deploy the aggregation designs\r\n" + ex.Message + "\r\n\r\nDo you want to see a stack trace?" , "BIDSHelper - Deploy Aggregation Designs" , MessageBoxButtons.YesNo , MessageBoxIcon.Error , MessageBoxDefaultButton.Button2) == DialogResult.Yes) { MessageBox.Show(ex.StackTrace); } } finally { ApplicationObject.StatusBar.Progress(true, "Deploying Aggregation Designs", 5, 5); svr.Disconnect(); } } } } finally { ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy); ApplicationObject.StatusBar.Progress(false, "Deploying Aggregation Designs", 5, 5); } }
public static string GetMessageString(this XmlaResultCollection xmlaResults) { return(string.Join(Environment.NewLine, xmlaResults.OfType <XmlaResult>().SelectMany(r => r.Messages.OfType <XmlaMessage>()).Select(msg => msg.Description).ToArray())); }
public static void DeployScript(ProjectItem projItem, DTE2 ApplicationObject) { Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object; try { //validate the script because deploying an invalid script makes cube unusable Microsoft.AnalysisServices.Design.Scripts script = new Microsoft.AnalysisServices.Design.Scripts(oCube); } catch (Microsoft.AnalysisServices.Design.ScriptParsingFailed ex) { string throwaway = ex.Message; MessageBox.Show("MDX Script in " + oCube.Name + " is not valid.", "Problem Deploying MDX Script"); return; } if (oCube.MdxScripts.Count == 0) { MessageBox.Show("There is no MDX script defined in this cube yet."); return; } try { ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy); ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 1, 5); // Check if the file is read-only (and probably checked in to a source control system) // before attempting to save. (issue: 10327 ) FileAttributes fa = System.IO.File.GetAttributes(projItem.get_FileNames(1)); if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly) { //TODO - can I check and maybe prompt before saving? //Save the cube projItem.Save(""); } ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 2, 5); // extract deployment information DeploymentSettings deploySet = new DeploymentSettings(projItem); // use xlst to create xmla alter command XslCompiledTransform xslt = new XslCompiledTransform(); XmlReader xsltRdr; XmlReader xrdr; // read xslt from embedded resource xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployMdxScript)); using ((xsltRdr)) { // read content from .cube file xrdr = XmlReader.Create(projItem.get_FileNames(1)); using (xrdr) { ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 3, 5); // Connect to Analysis Services Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server(); svr.Connect(deploySet.TargetServer); ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 4, 5); // execute the xmla try { Microsoft.AnalysisServices.Scripter scr = new Microsoft.AnalysisServices.Scripter(); // Build up the Alter MdxScript command using XSLT against the .cube file XslCompiledTransform xslta = new XslCompiledTransform(); StringBuilder sb = new StringBuilder(); XmlWriterSettings xws = new XmlWriterSettings(); xws.OmitXmlDeclaration = true; xws.ConformanceLevel = ConformanceLevel.Fragment; XmlWriter xwrtr = XmlWriter.Create(sb, xws); xslta.Load(xsltRdr); XsltArgumentList xslarg = new XsltArgumentList(); Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase); if (targetDB == null) { throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer)); } string targetDatabaseID = targetDB.ID; xslarg.AddParam("TargetDatabase", "", targetDatabaseID); xslta.Transform(xrdr, xslarg, xwrtr); // Extract the current script from the server and keep a temporary backup copy of it StringBuilder sbBackup = new StringBuilder(); XmlWriterSettings xwSet = new XmlWriterSettings(); xwSet.ConformanceLevel = ConformanceLevel.Fragment; xwSet.OmitXmlDeclaration = true; xwSet.Indent = true; XmlWriter xwScript = XmlWriter.Create(sbBackup, xwSet); Cube oServerCube = targetDB.Cubes.Find(oCube.ID); if (oServerCube == null) { throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer)); } else if (oServerCube.State == AnalysisState.Unprocessed) { throw new System.Exception(string.Format("The {0} cube is not processed the {1} server.", oCube.Name, deploySet.TargetServer)); } if (oServerCube.MdxScripts.Count == 0) { scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { oServerCube }, xwScript, true); } else { MdxScript mdxScr = oServerCube.MdxScripts[0]; scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] { mdxScr }, xwScript, true); } xwScript.Close(); // update the MDX Script XmlaResultCollection xmlaRC = svr.Execute(sb.ToString()); if (xmlaRC.Count == 1 && xmlaRC[0].Messages.Count == 0) { // all OK - 1 result - no messages } else { StringBuilder sbErr = new StringBuilder(); for (int iRC = 0; iRC < xmlaRC.Count; iRC++) { for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++) { sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description); } } MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy MDX Script"); } // Test the MDX Script AdomdConnection cn = new AdomdConnection("Data Source=" + deploySet.TargetServer + ";Initial Catalog=" + deploySet.TargetDatabase); cn.Open(); AdomdCommand cmd = cn.CreateCommand(); string qry = "SELECT {} ON 0 FROM [" + oCube.Name + "];"; cmd.CommandText = qry; try { // test that we can query the cube without errors cmd.Execute(); // Building the project means that the .asdatabase file gets re-built so that // we do not break the Deployment Wizard. // -- // This line is included in this try block so that it is only executed if we can // successfully query the cube without errors. projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false); } catch (System.Exception ex) { // undo the deployment if we caught an exception during the deployment svr.Execute(sbBackup.ToString()); MessageBox.Show(ex.Message); } finally { cmd.Dispose(); cn.Close(); cn.Dispose(); } } catch (System.Exception ex) { if (MessageBox.Show("The following error occured while trying to deploy the MDX Script\r\n" + ex.Message + "\r\n\r\nDo you want to see a stack trace?" , "BIDSHelper - Deploy MDX Script" , MessageBoxButtons.YesNo , MessageBoxIcon.Error , MessageBoxDefaultButton.Button2) == DialogResult.Yes) { MessageBox.Show(ex.StackTrace); } } finally { ApplicationObject.StatusBar.Progress(true, "Deploying MdxScript", 5, 5); // report any results back (status bar?) svr.Disconnect(); svr.Dispose(); } } } } finally { ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy); ApplicationObject.StatusBar.Progress(false, "Deploying MdxScript", 5, 5); } }
public static void DeployPerspectives(ProjectItem projItem, DTE2 ApplicationObject) { Microsoft.AnalysisServices.Cube oCube = (Microsoft.AnalysisServices.Cube)projItem.Object; if (oCube.Perspectives.Count == 0) { MessageBox.Show("There are no perspectives defined in this cube yet."); return; } try { ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationDeploy); ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 1, 5); FileAttributes fa = System.IO.File.GetAttributes(projItem.get_FileNames(1)); if ((fa & FileAttributes.ReadOnly) != FileAttributes.ReadOnly) { //Save the cube projItem.Save(""); } ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 2, 5); // extract deployment information DeploymentSettings deploySet = new DeploymentSettings(projItem); // use xlst to create xmla alter command XslCompiledTransform xslt = new XslCompiledTransform(); XmlReader xsltRdr; XmlReader xrdr; // read xslt from embedded resource xsltRdr = XmlReader.Create(new StringReader(BIDSHelper.Resources.Common.DeployPerspectives)); using ((xsltRdr)) { // read content from .cube file xrdr = XmlReader.Create(projItem.get_FileNames(1)); using (xrdr) { ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 3, 5); // Connect to Analysis Services Microsoft.AnalysisServices.Server svr = new Microsoft.AnalysisServices.Server(); svr.Connect(deploySet.TargetServer); ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 4, 5); // execute the xmla try { // Build up the Alter perspectives command using XSLT against the .cube file XslCompiledTransform xslta = new XslCompiledTransform(); StringBuilder sb = new StringBuilder(); XmlWriterSettings xws = new XmlWriterSettings(); xws.OmitXmlDeclaration = true; xws.ConformanceLevel = ConformanceLevel.Fragment; XmlWriter xwrtr = XmlWriter.Create(sb, xws); xslta.Load(xsltRdr); XsltArgumentList xslarg = new XsltArgumentList(); Database targetDB = svr.Databases.FindByName(deploySet.TargetDatabase); if (targetDB == null) { throw new System.Exception(string.Format("A database called {0} could not be found on the {1} server", deploySet.TargetDatabase, deploySet.TargetServer)); } string targetDatabaseID = targetDB.ID; xslarg.AddParam("TargetDatabase", "", targetDatabaseID); xslta.Transform(xrdr, xslarg, xwrtr); Cube oServerCube = targetDB.Cubes.Find(oCube.ID); if (oServerCube == null) { throw new System.Exception(string.Format("The {0} cube is not yet deployed to the {1} server.", oCube.Name, deploySet.TargetServer)); } //drop any perspectives which don't exist svr.CaptureXml = true; for (int i = 0; i < oServerCube.Perspectives.Count; i++) { Perspective p = oServerCube.Perspectives[i]; if (!oCube.Perspectives.Contains(p.ID)) { p.Drop(); i--; } } svr.CaptureXml = false; try { if (svr.CaptureLog.Count > 0) { svr.ExecuteCaptureLog(true, false); } } catch (System.Exception ex) { throw new System.Exception("Error dropping perspective that were deleted in the source code. " + ex.Message); } // update the perspectives XmlaResultCollection xmlaRC = svr.Execute(sb.ToString()); StringBuilder sbErr = new StringBuilder(); for (int iRC = 0; iRC < xmlaRC.Count; iRC++) { for (int iMsg = 0; iMsg < xmlaRC[iRC].Messages.Count; iMsg++) { sbErr.AppendLine(xmlaRC[iRC].Messages[iMsg].Description); } } if (sbErr.Length > 0) { MessageBox.Show(sbErr.ToString(), "BIDSHelper - Deploy Perspectives"); } try { // Building the project means that the .asdatabase file gets re-built so that // we do not break the Deployment Wizard. projItem.DTE.Solution.SolutionBuild.BuildProject(projItem.DTE.Solution.SolutionBuild.ActiveConfiguration.Name, projItem.ContainingProject.UniqueName, false); } catch (System.Exception ex) { MessageBox.Show(ex.Message); } } catch (System.Exception ex) { if (MessageBox.Show("The following error occured while trying to deploy the perspectives\r\n" + ex.Message + "\r\n\r\nDo you want to see a stack trace?" , "BIDS Helper - Deploy Perspectives" , MessageBoxButtons.YesNo , MessageBoxIcon.Error , MessageBoxDefaultButton.Button2) == DialogResult.Yes) { MessageBox.Show(ex.StackTrace); } } finally { ApplicationObject.StatusBar.Progress(true, "Deploying perspectives", 5, 5); // report any results back (status bar?) svr.Disconnect(); } } } } finally { ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy); ApplicationObject.StatusBar.Progress(false, "Deploying perspectives", 5, 5); } }