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);
}
}
