Example #1
0
        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 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);
                            }
                        }
                        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();
                        }
                    }
                }
            }
            finally
            {
                ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationDeploy);
                ApplicationObject.StatusBar.Progress(false, "Deploying MdxScript", 5, 5);
            }
        }
        private void ExecInternal(ProjectItem projItem, DataModelingSandbox sandbox)
        {
            //sandbox.
            // 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);
                scr.ScriptAlter(new Microsoft.AnalysisServices.MajorObject[] {sandbox.Database}, 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");
            }
        }
        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);
            }
        }