Ejemplo n.º 1
0
        internal static void ExecuteStoredProcedure(IntegrationServices store, string schema, string sprocName, SqlParameter[] parameters, string databaseName)
        {
            try
            {
                SqlConnection sqlConnection = SqlHelper.GetSqlConnection(store.Connection);
                SqlCommand    sqlCommand    = sqlConnection.CreateCommand();
                sqlCommand.CommandType    = CommandType.StoredProcedure;
                sqlCommand.CommandTimeout = 600000;
                sqlCommand.CommandText    = string.Format(CultureInfo.InvariantCulture, "[{0}].[{1}].[{2}]", new object[]
                {
                    Helpers.GetEscapedName(databaseName),
                    string.IsNullOrEmpty(schema) ? "dbo" : schema,
                    sprocName
                });
                if (parameters != null && parameters.Length > 0)
                {
                    sqlCommand.Parameters.AddRange(parameters);
                }

                sqlCommand.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw new IntegrationServicesException($"StoredProcedureException: {sprocName}, {ex.Message}");
            }
        }
Ejemplo n.º 2
0
        private void CreateEnvironmentButton_Click(object sender, EventArgs e)
        {
            try
            {
                Server server = new Server(_server);

                IntegrationServices isServer = new IntegrationServices(server);

                Catalog catalog = isServer.Catalogs["SSISDB"];

                CatalogFolder folder = catalog.Folders["ProSSIS"];

                EnvironmentInfo env = new EnvironmentInfo(folder, "Environment1", "Description of Environment1");
                env.Create();

                env.Variables.Add("var1", TypeCode.Int32, 1, false, "Var1 Description");
                env.Variables.Add("sensitiveVar2", TypeCode.String, "secure value", true, "");
                env.Alter();

                //ProjectInfo p = folder.Projects["ProSSIS"];

                //p.References.Add("Environment1", folder.Name);
                //p.Alter();

                MessageBox.Show("Environment Created");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
        static void Main(string[] args)
        {
            string targetServerName = "localhost";
            string folderName       = "Permanencia";
            string projectName      = "Permanencia";
            string packageName      = "Package1.dtsx";

            // Conexion
            string sqlConnectionString = "Data Source=" + targetServerName +
                                         ";Initial Catalog=master;Integrated Security=SSPI;";
            SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);

            // Integration Services obj
            IntegrationServices integrationServices = new IntegrationServices(sqlConnection);

            // Integration Services catalog
            Catalog catalog = integrationServices.Catalogs["SSISDB"];

            // Carpeta
            CatalogFolder folder = catalog.Folders[folderName];

            // Proyecto
            ProjectInfo project = folder.Projects[projectName];

            // Package
            PackageInfo package = project.Packages[packageName];

            // Run run package
            package.Execute(false, null);
        }
Ejemplo n.º 4
0
    private string GetPackageStatus()
    {
        Server server = new Server(_server);

        IntegrationServices service = new IntegrationServices(server);

        Catalog catalog = service.Catalogs[_catalog];

        foreach (ExecutionOperation exec in catalog.Executions)
        {
            if (exec.FolderName == _folder && exec.PackageName == _packageinfo.Name)
            {
                catalog.Executions.Refresh();
                if (!exec.Completed)
                {
                    return("Executing");
                }
            }
        }

        return("Sleeping");

        //RunningPackages rps = dtsapp.GetRunningPackages(_server);
        //foreach (RunningPackage rp in rps)
        //{
        //    if (rp.PackageID == new Guid(_packageinfo.PackageGuid))
        //    {
        //        return "Executing";
        //    }
        //}
        //return "Sleeping";
    }
Ejemplo n.º 5
0
        private void TransferPackageButton_Click(object sender, EventArgs e)
        {
            try
            {
                Server server = new Server(_server);

                IntegrationServices isServer = new IntegrationServices(server);

                Catalog catalog = isServer.Catalogs["SSISDB"];

                CatalogFolder folder = new CatalogFolder(catalog, "Test", "Test description");// catalog.Folders["ProSSIS"];
                folder.Create();

                Project localProject = CreateNewProject("newproject.ispac", "New Project");

                folder.DeployProject("New Project", System.IO.File.ReadAllBytes("newprojects.ispac"));
                folder.Alter();

                ProjectInfo newProject = folder.Projects["New Project"];
                newProject.Move("ProSSIS");
                newProject.Alter();
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Ejemplo n.º 6
0
        }     //END Create web method.

        //+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
        #endregion

        #region Private Methods.
        // =====================================================================================
        // -------------------------------------------------------------------------------------

        // =====================================================================================
        /// <summary>
        /// This method updates the TokenUserProfile object in the database.
        /// </summary>
        /// <param name="UserProfile">EusTokenUserProfile object</param>
        /// <returns>EvEventCodes enumerated value.</returns>
        // -------------------------------------------------------------------------------------
        private EvEventCodes UpdateTokenUserProfile(EusTokenUserProfile UserProfile)
        {
            this.LogMethod("UpdateTokenUserProfile");
            //
            // initialise the methods variables and objects.
            //
            EvUserProfileBase serviceUserProfile = new EvUserProfileBase( );

            serviceUserProfile.UserId     = "TokenService";
            serviceUserProfile.CommonName = "Token Service";

            //
            // Set the device client service session state.
            //
            IntegrationServices integrationServices = new IntegrationServices(
                Global.ServiceVersion,
                Global.GlobalObjectList,
                Global.ApplicationPath,
                serviceUserProfile,
                Global.UniForm_BinaryFilePath,
                Global.UniForm_BinaryServiceUrl);

            integrationServices.LoggingLevel   = 5;
            integrationServices.EventLogSource = Global.EventLogSource;

            //
            // Update the token user profile.
            //
            EvEventCodes result = integrationServices.UpdateTokenUserProfile(UserProfile);

            this.LogClass(integrationServices.Log);

            this.LogMethodEnd("UpdateTokenUserProfile");
            return(result);
        }
Ejemplo n.º 7
0
        private void ShowRunningPackagesButton_Click(object sender, EventArgs e)
        {
            Server server = new Server(_server);

            try
            {
                IntegrationServices isServer = new IntegrationServices(server);

                Catalog catalog = isServer.Catalogs["SSISDB"];

                StringBuilder messages = new StringBuilder();

                foreach (ExecutionOperation exec in catalog.Executions)
                {
                    catalog.Executions.Refresh();

                    if (!exec.Completed)
                    {
                        messages.AppendLine("Package " + exec.PackageName + " is running. The project is " + exec.ProjectName + ". It started running at " + exec.StartTime.ToString());
                    }
                }

                LogFileTextbox.Text = messages.ToString();

                //MessageBox.Show("Package executed");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Ejemplo n.º 8
0
        static void Main(string[] args)
        {
            // Variables
            string targetServerName = @"192.168.2.111\SQL17ENTERPRISE,1435";
            string folderName       = "SSISPackageItems";
            string projectName      = "SSISPackageRunInSQLJob";
            string packageName      = "Package.dtsx";

            // Create a connection to the server
            string        sqlConnectionString = "Data Source=" + targetServerName + ";Initial Catalog=master;Integrated Security=SSPI;";
            SqlConnection sqlConnection       = new SqlConnection(sqlConnectionString);

            // Create the Integration Services object
            IntegrationServices integrationServices = new IntegrationServices(sqlConnection);

            // Get the Integration Services catalog
            Catalog catalog = integrationServices.Catalogs["SSISDB"];

            // Get the folder
            CatalogFolder folder = catalog.Folders[folderName];

            // Get the project
            ProjectInfo project = folder.Projects[projectName];

            // Get the package
            PackageInfo package = project.Packages[packageName];

            // Run the package
            package.Execute(false, null);
        }
Ejemplo n.º 9
0
        public void CreateProjectAndDeployTest2()
        {
            string serverName = ConfigurationManager.ConnectionStrings["CHEF"].ConnectionString.Split(';')[1];
            int    index      = serverName.IndexOf('=');

            serverName = serverName.Substring(index + 1);
            Server server = new Server(serverName);
            IntegrationServices integrationServices = new IntegrationServices(server);
            Catalog             catalog             = integrationServices.Catalogs["SSISDB"];
            CatalogFolder       catalogFolder       = catalog.Folders["NotTheCHEFFolder"];

            if (catalogFolder == null)
            {
                catalogFolder = new CatalogFolder(catalog, "NotTheCHEFFolder", "Not the CHEF folder.");
                catalogFolder.Create();
            }
            string      strProjectLocation = @"C:\Program Files\Microsoft\CHEF\Temp";
            Package     package            = null;
            Application app = new Application();

            package = app.LoadPackage(@"C:\Users\t-satsen\Documents\Visual Studio 2010\Projects\Package_DatabaseToDatabase\BasicFeaturesPackage\bin\Debug\TableToTableDataTransferPkg.dtsx", null);
            Program_Accessor.processID   = "100";
            Program_Accessor.processName = "TestTask";
            if (catalogFolder.Projects[Program_Accessor.processID + "_" + Program_Accessor.processName] != null)
            {
                catalogFolder.Projects[Program_Accessor.processID + "_" + Program_Accessor.processName].Drop();
            }
            Program_Accessor.CreateProjectAndDeploy(catalogFolder, strProjectLocation, package);
            Assert.IsNull(catalogFolder.Projects[Program_Accessor.processID + "_" + Program_Accessor.processName]);
        }
Ejemplo n.º 10
0
        public override IExecutionResult Run()
        {
            var connection          = new SqlConnection(string.Format(@"Data Source={0};Initial Catalog=master;Integrated Security=SSPI;", Etl.Server));
            var integrationServices = new IntegrationServices(connection);

            var catalog = integrationServices.Catalogs[Etl.Catalog];
            var folder  = catalog.Folders[Etl.Folder];
            var project = folder.Projects[Etl.Project];
            var package = project.Packages[Etl.Name];

            var setValueParameters = new Collection <PackageInfo.ExecutionValueParameterSet>();

            setValueParameters.Add(new PackageInfo.ExecutionValueParameterSet
            {
                ObjectType     = 50,
                ParameterName  = "SYNCHRONIZED",
                ParameterValue = 1
            });

            long executionIdentifier = package.Execute(Etl.Is32Bits, null, setValueParameters);

            var execution = catalog.Executions[executionIdentifier];

            var result = EtlRunResult.Build(
                execution.Status
                , execution.Messages.Where(m => m.MessageType == 120 || m.MessageType == 110).Select(m => m.Message)
                , execution.StartTime
                , execution.EndTime);

            return(result);
        }
        static void Main(string[] args)
        {
            string targetFolderName = "dailyClosedJobs";
            string projectName      = "dailyClosedJobs.dtsx";
            string projectFilePath  = @"" + ConfigurationManager.AppSettings["FolderPath"];

            try
            {
                // Create a connection to the server// Create the Integration Services object
                string        conStr        = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
                SqlConnection sqlConnection = new SqlConnection(conStr);
                using (var con = sqlConnection)
                {
                    IntegrationServices integrationServices = new IntegrationServices(con);

                    // Get the Integration Services catalog
                    Catalog catalog = integrationServices.Catalogs["SSISDB"];

                    // Create the target folder
                    CatalogFolder folder = new CatalogFolder(catalog,
                                                             targetFolderName, "Folder description");

                    Console.WriteLine("Deploying " + projectName + " project.");

                    byte[] projectFile = File.ReadAllBytes(projectFilePath);
                    folder.DeployProject(projectName, projectFile);

                    Console.WriteLine("Done.");
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
            }
        }
Ejemplo n.º 12
0
        private PackageInfo GetPackage(IntegrationServices integrationServices)
        {
            if (!integrationServices.Catalogs.Contains(Etl.Catalog))
            {
                var names = String.Join(", ", integrationServices.Catalogs.Select(c => c.Name));
                throw new ArgumentOutOfRangeException("Catalog", String.Format("The catalog named '{0}' hasn't been found on the server '{1}'. List of existing catalogs: {2}.", Etl.Catalog, Etl.Server, names));
            }

            var catalog = integrationServices.Catalogs[Etl.Catalog];

            if (!catalog.Folders.Contains(Etl.Folder))
            {
                var names = String.Join(", ", catalog.Folders.Select(f => f.Name));
                throw new ArgumentOutOfRangeException("Folder", String.Format("The folder named '{0}' hasn't been found on the catalog '{1}'. List of existing folders: {2}.", Etl.Folder, Etl.Catalog, names));
            }
            var folder = catalog.Folders[Etl.Folder];

            if (!folder.Projects.Contains(Etl.Project))
            {
                var names = String.Join(", ", folder.Projects.Select(p => p.Name));
                throw new ArgumentOutOfRangeException("Project", String.Format("The project named '{0}' hasn't been found on the catalog '{1}'. List of existing projects: {2}.", Etl.Project, Etl.Folder, names));
            }
            var project = folder.Projects[Etl.Project];

            if (!project.Packages.Contains(Etl.Name))
            {
                var names = String.Join(", ", project.Packages.Select(p => p.Name));
                throw new ArgumentOutOfRangeException("Name", String.Format("The package named '{0}' hasn't been found on the project '{1}'. List of existing packages: {2}.", Etl.Name, Etl.Project, names));
            }
            var package = project.Packages[Etl.Name];

            return(package);
        }
Ejemplo n.º 13
0
        private string GetMessages(IntegrationServices integrationServices, long executionId, bool failOnWarning)
        {
            StringBuilder messages = new StringBuilder();

            foreach (var message in integrationServices.Catalogs[Catalog].Executions[executionId].Messages)
            {
                if (message.MessageType == 120)
                {
                    messages.AppendLine($"{message.MessageTime} - Error: {message.Message}");
                    this.HasFailed = true;
                }

                if (failOnWarning && message.MessageType == 110 && !message.Message.Contains("global shared memory"))
                {
                    messages.AppendLine($"{message.MessageTime} - Warning: {message.Message}");
                    this.HasFailed = true;
                }

                if (message.MessageType == 130)
                {
                    messages.AppendLine($"{message.MessageTime} - Task Failed: {message.Message}");
                }
            }

            return(messages.ToString());
        }
Ejemplo n.º 14
0
    protected PackageGroupCollection  BuildPackageGroupCollection(string pathToProject)
    {
        string[] path    = pathToProject.Split('/');
        string   catalog = path[0];
        string   folder  = path[1];
        string   project = path[2];

        Server server = new Server(ssisServer);

        IntegrationServices service = new IntegrationServices(server);

        Catalog catalogObject = service.Catalogs[catalog];

        CatalogFolder folderObject = catalogObject.Folders[folder];

        ProjectInfo            projectObject = folderObject.Projects[project];
        PackageGroupCollection collection    = new PackageGroupCollection();

        foreach (Microsoft.SqlServer.Management.IntegrationServices.PackageInfo p in projectObject.Packages)
        {
            PackageGroup g = new PackageGroup(p, server.Name, catalog, folder, project);
            collection.Add(g);
        }

        return(collection);
    }
Ejemplo n.º 15
0
        public override IExecutionResult Run()
        {
            var connection = new SqlConnection(string.Format(@"Data Source={0};Initial Catalog=master;Integrated Security=SSPI;", Etl.Server));
            var integrationServices = new IntegrationServices(connection);

            var catalog = integrationServices.Catalogs[Etl.Catalog];
            var folder  = catalog.Folders[Etl.Folder];
            var project = folder.Projects[Etl.Project];
            var package = project.Packages[Etl.Name];

            var setValueParameters = new Collection<PackageInfo.ExecutionValueParameterSet>();
            setValueParameters.Add(new PackageInfo.ExecutionValueParameterSet
            {
                ObjectType = 50,
                ParameterName = "SYNCHRONIZED",
                ParameterValue = 1
            });

            long executionIdentifier = package.Execute(Etl.Is32Bits, null, setValueParameters);

            var execution = catalog.Executions[executionIdentifier];

            var result = EtlRunResult.Build(
                execution.Status
                , execution.Messages.Where(m => m.MessageType == 120 || m.MessageType == 110).Select(m => m.Message)
                , execution.StartTime
                , execution.EndTime);

            return result;
        }
Ejemplo n.º 16
0
        //MyCode: Check if catalog exists and if folder exists; create if not.
        private static CatalogFolder CheckCatalogFolder(string folderName)
        {
            string serverName = ConfigurationManager.ConnectionStrings["CHEF"].ConnectionString.Split(';')[1];
            int    index      = serverName.IndexOf('=');

            serverName = serverName.Substring(index + 1);
            Server server = new Server(serverName);
            IntegrationServices integrationServices = new IntegrationServices(server);
            Catalog             catalog             = integrationServices.Catalogs["SSISDB"];
            CatalogFolder       catalogFolder       = null;

            if (catalog == null)
            {
                //Throw exception if catalog doesn't exist
                throw new Exception("Catalog not found. Please create the SSISDB catalog and try again.");
            }
            else
            {
                catalogFolder = catalog.Folders[folderName];
                if (catalogFolder == null)
                {
                    //Create catalog folder if it doesn't exist
                    catalogFolder = new CatalogFolder(catalog, folderName, "This is the folder which contains all projects generated through CHEF");
                    catalogFolder.Create();
                }
            }
            return(catalogFolder);
        }
Ejemplo n.º 17
0
        internal static bool IsSysAdmin(IntegrationServices store)
        {
            string cmdText = "SELECT ISNULL(IS_SRVROLEMEMBER ('sysadmin'), 0)";
            object value   = SqlHelper.ExecuteSQLCommand(store.Connection, CommandType.Text, cmdText, null, ExecuteType.ExecuteScalar);
            int    num     = Convert.ToInt32(value, CultureInfo.InvariantCulture);

            return(num == 1);
        }
Ejemplo n.º 18
0
        // GET api/values/5
        public string Get(int id)
        {
            string folderName      = "ConArchETL";
            string environmentName = "";

            string cloudDatabaseHost = ".";
            //string cloudDatabaseUser = "******";
            //string cloudDatabasePassword = "******";

            string settings = string.Copy(ConfigurationManager.ConnectionStrings["ManagementContext"].ConnectionString);


            string ADO_Evasys_ConnectionString = ".";

            // Create a connection to the server
            List <string> connectionConfigs = settings.Split(';').Where(c => !string.IsNullOrEmpty(c)).ToList();
            Dictionary <string, string> connectionValues = connectionConfigs.Select(item => item.Split('=')).ToDictionary(s => s[0], s => s[1]);

            connectionValues.TryGetValue("Data Source", out cloudDatabaseHost);

            string sqlConnectionString = "Data Source=" + cloudDatabaseHost +
                                         ";Initial Catalog=master;Integrated Security=SSPI;";
            SqlConnection sqlConnection = new SqlConnection(sqlConnectionString);


            // Create the Integration Services object
            IntegrationServices integrationServices = new IntegrationServices(sqlConnection);

            // Get the Integration Services catalog
            Catalog catalog = integrationServices.Catalogs["SSISDB"];

            // Get the folder
            CatalogFolder folder = catalog.Folders[folderName];

            // Get Environment
            environmentName = String.Format("Environment_{0}", folder.Environments.ToList().Count *2);
            EnvironmentInfo environmentInfo = folder.Environments[environmentName];

            if (environmentInfo != null)
            {
                environmentInfo.Drop();
            }

            environmentInfo = new EnvironmentInfo(folder, environmentName, environmentName);
            environmentInfo.Create();

            if (null == environmentInfo.Variables["CM.ADO_Evasys.ConnectionString"])
            {
                environmentInfo.Variables.Add("CM.ADO_Evasys.ConnectionString", TypeCode.String, ADO_Evasys_ConnectionString, false, "ConnectionString");
            }
            else
            {
                environmentInfo.Variables["CM.ADO_Evasys.ConnectionString"].Value = ADO_Evasys_ConnectionString;
            }

            environmentInfo.Alter();
            return(cloudDatabaseHost);
        }
Ejemplo n.º 19
0
        public IntegrationPackageResult Execute(string packageName, SsisParameter[] parameters)
        {
            SqlConnection       ssisConnection = new SqlConnection(SqlConnectionString);
            IntegrationServices ssisServer     = new IntegrationServices(ssisConnection);

            // The reference to the package which you want to execute
            PackageInfo ssisPackage = ssisServer.Catalogs[CashDiscipline.Common.Constants.SsisCatalog].Folders[ssisFolderName].Projects[catalogName].Packages[packageName];

            // Add execution parameter to override the default asynchronized execution. If you leave this out the package is executed asynchronized
            Collection <PackageInfo.ExecutionValueParameterSet> executionParameter = new Collection <PackageInfo.ExecutionValueParameterSet>();

            executionParameter.Add(new PackageInfo.ExecutionValueParameterSet {
                ObjectType = 50, ParameterName = "SYNCHRONIZED", ParameterValue = 1
            });

            // Modify package parameter
            foreach (var svcParam in parameters)
            {
                ParameterInfo pInfo = null;
                var           pKey  = new ParameterInfo.Key(svcParam.ParameterName);
                if (!ssisPackage.Parameters.TryGetValue(pKey, out pInfo))
                {
                    throw new InvalidOperationException(string.Format("Parameter name {0} does not exist in package.", pKey.Name));
                }
                pInfo.Set(ParameterInfo.ParameterValueType.Literal, svcParam.ParameterValue);
            }

            ssisPackage.Alter();

            // Get the identifier of the execution to get the log
            long executionIdentifier = ssisPackage.Execute(false, null, executionParameter);

            // Loop through the log and add the messages to the listbox
            SsisMessages = new List <SsisMessage>();
            var execution = ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier];

            foreach (OperationMessage message in ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier].Messages)
            {
                SsisMessages.Add(new SsisMessage()
                {
                    MessageSourceType = message.MessageSourceType,
                    Message           = message.Message,
                    MessageType       = message.MessageType
                });
            }

            // Update result
            PackageResult = new IntegrationPackageResult();
            PackageResult.SsisMessages       = this.SsisMessages;
            PackageResult.ExecutionIdentifer = executionIdentifier;
            PackageResult.PackageName        = packageName;
            PackageResult.OperationStatus    = (SsisOperationStatus)execution.Status;

            return(PackageResult);
        }
Ejemplo n.º 20
0
        public void CheckCatalogFolderTest1()
        {
            string folderName = "CHEFFolder"; // TODO: Initialize to an appropriate value
            Server server     = new Server("localhost");
            IntegrationServices integrationServices = new IntegrationServices(server);
            Catalog             catalog             = integrationServices.Catalogs["SSISDB"];
            CatalogFolder       expected            = catalog.Folders[folderName]; // TODO: Initialize to an appropriate value
            CatalogFolder       actual;

            actual = Program_Accessor.CheckCatalogFolder(folderName);
            Assert.AreEqual(expected.FolderId, actual.FolderId);
        }
Ejemplo n.º 21
0
        internal static object GetPrincipalIdByName(IntegrationServices store, string databaseName, string principalName)
        {
            SqlParameter[] parameters = new SqlParameter[]
            {
                new SqlParameter("@name", principalName)
            };
            string cmdText = string.Format(CultureInfo.InvariantCulture, "SELECT [principal_id] from [{0}].[internal].[get_database_principals]() where name = @name", new object[]
            {
                databaseName
            });

            return(SqlHelper.ExecuteSQLCommand(store.Connection, CommandType.Text, cmdText, parameters, ExecuteType.ExecuteScalar));
        }
Ejemplo n.º 22
0
        private void DropFolderButton_Click(object sender, EventArgs e)
        {
            try
            {
                Server server = new Server(_server);

                IntegrationServices isServer = new IntegrationServices(server);

                Catalog catalog = isServer.Catalogs["SSISDB"];

                CatalogFolder folder = new CatalogFolder(catalog, "Test", "Test description");// catalog.Folders["ProSSIS"];
                folder.Create();

                EnvironmentInfo newEnv = new EnvironmentInfo(folder, "Environment1", "Description of Environment1");
                newEnv.Create();

                newEnv.Variables.Add("var1", TypeCode.Int32, 1, false, "Var1 Description");
                newEnv.Variables.Add("sensitiveVar2", TypeCode.String, "secure value", true, "");
                newEnv.Alter();

                // this will fail because there is an environment under the folder
                try
                {
                    folder.Drop();
                }
                catch
                {
                }


                foreach (EnvironmentInfo env in folder.Environments.ToArray())
                {
                    env.Drop();
                }

                foreach (ProjectInfo p in folder.Projects.ToArray())
                {
                    p.Drop();
                }

                // this will succeed now that everything has been removed.
                folder.Drop();

                MessageBox.Show("Folder removed");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Ejemplo n.º 23
0
        private void GetPackage(IntegrationServices integrationServices, out Catalog catalog, out PackageInfo package)
        {
            if (integrationServices.Catalogs.Contains(Etl.Catalog))
            {
                catalog = integrationServices.Catalogs[Etl.Catalog];
            }
            else
            {
                var names = String.Join(", ", integrationServices.Catalogs.Select(c => c.Name));
                throw new ArgumentOutOfRangeException("Catalog", String.Format("The catalog named '{0}' hasn't been found on the server '{1}'. List of existing catalogs: {2}.", Etl.Catalog, Etl.Server, names));
            }


            CatalogFolder folder;

            if (catalog.Folders.Contains(Etl.Folder))
            {
                folder = catalog.Folders[Etl.Folder];
            }
            else
            {
                var names = String.Join(", ", catalog.Folders.Select(f => f.Name));
                throw new ArgumentOutOfRangeException("Folder", String.Format("The folder named '{0}' hasn't been found on the catalog '{1}'. List of existing folders: {2}.", Etl.Folder, Etl.Catalog, names));
            }

            ProjectInfo project;

            if (folder.Projects.Contains(Etl.Project))
            {
                project = folder.Projects[Etl.Project];
            }
            else
            {
                var names = String.Join(", ", folder.Projects.Select(p => p.Name));
                throw new ArgumentOutOfRangeException("Project", String.Format("The project named '{0}' hasn't been found on the catalog '{1}'. List of existing projects: {2}.", Etl.Project, Etl.Folder, names));
            }

            if (project.Packages.Contains(Etl.Name))
            {
                package = project.Packages[Etl.Name];
            }
            else
            {
                var names = String.Join(", ", project.Packages.Select(p => p.Name));
                throw new ArgumentOutOfRangeException("Name", String.Format("The package named '{0}' hasn't been found on the project '{1}'. List of existing packages: {2}.", Etl.Name, Etl.Project, names));
            }
        }
Ejemplo n.º 24
0
        private void ExecutePackageButton_Click(object sender, EventArgs e)
        {
            try
            {
                Server server = new Server(_server);


                IntegrationServices isServer = new IntegrationServices(server);

                Catalog catalog = isServer.Catalogs["SSISDB"];

                CatalogFolder folder = catalog.Folders["ProSSIS"];

                ProjectInfo p = folder.Projects["My ProSSIS Project"];

                Microsoft.SqlServer.Management.IntegrationServices.PackageInfo pkg = p.Packages["package.dtsx"];
                EnvironmentReference reference = p.References["Environment1", folder.Name];
                reference.Refresh();

                long operationId = pkg.Execute(false, reference);

                catalog.Operations.Refresh();
                StringBuilder messages = new StringBuilder();

                foreach (Operation op in catalog.Operations)
                {
                    if (op.Id == operationId)
                    {
                        op.Refresh();

                        foreach (OperationMessage msg in op.Messages)
                        {
                            messages.AppendLine(msg.Message);
                        }
                    }
                }

                LogFileTextbox.Text = "Package executed: " + messages.ToString();

                //MessageBox.Show("Package executed");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
Ejemplo n.º 25
0
        public override IExecutionResult Run()
        {
            var connection          = new SqlConnection(string.Format(@"Data Source={0};Initial Catalog=master;Integrated Security=SSPI;", Etl.Server));
            var integrationServices = new IntegrationServices(connection);

            Catalog     catalog;
            PackageInfo package;

            GetPackage(integrationServices, out catalog, out package);


            var setValueParameters = new Collection <PackageInfo.ExecutionValueParameterSet>();

            setValueParameters.Add(new PackageInfo.ExecutionValueParameterSet
            {
                ObjectType     = 50,
                ParameterName  = "SYNCHRONIZED",
                ParameterValue = 1
            });
            var parameters = Parameterize(Etl.Parameters, package.Parameters, package.Name);

            parameters.ToList().ForEach(p => setValueParameters.Add(p));

            long executionIdentifier = -1;

            if (Etl.Timeout == 0)
            {
                executionIdentifier = package.Execute(Etl.Is32Bits, null, setValueParameters);
            }
            else
            {
                executionIdentifier = package.Execute(Etl.Is32Bits, null, setValueParameters, Etl.Timeout);
            }

            var execution = catalog.Executions[executionIdentifier];

            var etlRunResultFactory = new EtlRunResultFactory();

            var result = etlRunResultFactory.Instantiate(
                execution.Status
                , execution.Messages.Where(m => m.MessageType == 120 || m.MessageType == 110).Select(m => m.Message)
                , execution.StartTime
                , execution.EndTime);

            return(result);
        }
Ejemplo n.º 26
0
        private void ShowOperationMessagesButton_Click(object sender, EventArgs e)
        {
            try
            {
                Server server = new Server(_server);

                IntegrationServices isServer = new IntegrationServices(server);

                Catalog catalog = isServer.Catalogs["SSISDB"];

                CatalogFolder folder = catalog.Folders["ProSSIS"];

                ProjectInfo p = folder.Projects["My ProSSIS Project"];

                catalog.Operations.Refresh();
                StringBuilder messages = new StringBuilder();

                foreach (ExecutionOperation exec in catalog.Executions)
                {
                    if (exec.Completed)
                    {
                        messages.AppendLine(exec.PackageName + " completed " + exec.EndTime.ToString());

                        var ops = from a in catalog.Operations where a.Id == exec.Id select a;

                        foreach (Operation op in ops)
                        {
                            op.Refresh();

                            foreach (OperationMessage msg in op.Messages)
                            {
                                messages.AppendLine("\t" + msg.Message);
                            }
                        }
                    }
                }

                LogFileTextbox.Text = messages.ToString();

                //MessageBox.Show("Package executed");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
        }
        public override DTSExecResult Execute(Connections connections,
                                              VariableDispenser variableDispenser,
                                              IDTSComponentEvents componentEvents,
                                              IDTSLogging log,
                                              object transaction)
        {
            //return base.Execute(connections, variableDispenser, componentEvents, log, transaction);
            Server catalogServer = new Server(ServerName);
            IntegrationServices integrationServices = new IntegrationServices(catalogServer);
            Catalog             catalog             = integrationServices.Catalogs[PackageCatalog];
            CatalogFolder       catalogFolder       = catalog.Folders[PackageFolder];
            ProjectInfo         catalogProject      = catalogFolder.Projects[PackageProject];

            Microsoft.SqlServer.Management.IntegrationServices.PackageInfo catalogPackage = catalogProject.Packages[PackageName + ".dtsx"];

            catalogPackage.Execute(false, null);

            return(DTSExecResult.Success);
        }
Ejemplo n.º 28
0
        public void ExecutePackage(string packageName, bool failOnWarning = true)
        {
            try
            {
                IntegrationServices integrationServices = new IntegrationServices(this.SsisServer);
                PackageInfo         package             = GetPackage(integrationServices, packageName);

                EnvironmentReference env = null;
                if (this.UseEnvironment)
                {
                    env = GetEnvironment(package);
                }
                long executionId = package.Execute(false, env);
                var  execution   = integrationServices.Catalogs[Catalog].Executions[executionId];
                while (!execution.Completed)
                {
                    System.Threading.Thread.Sleep(1000);
                    execution.Refresh();
                }
                string errorsAndWarnings = GetMessages(integrationServices, executionId, failOnWarning);
                var    status            = integrationServices.Catalogs[Catalog].Executions[executionId].Status;

                if (this.HasFailed || status != Operation.ServerOperationStatus.Success)
                {
                    throw new Exception($"The package '{packageName}' has failed with the following warnings and errors: {errorsAndWarnings}");
                }
            }

            catch (Exception e)
            {
                StringBuilder errorMessage = new StringBuilder();
                errorMessage.AppendLine($"An error was thrown while executing a package:");
                errorMessage.AppendLine($"Error: {e.Message}");
                errorMessage.AppendLine($"The following parameters were used:");
                errorMessage.AppendLine($"Folder: {this.PackageFolder}");
                errorMessage.AppendLine($"Project: {this.ProjectName}");
                errorMessage.AppendLine($"Package: {packageName}");
                errorMessage.AppendLine($"Environment Folder: {this.EnvironmentFolder}");
                errorMessage.AppendLine($"Environment: {this.EnvironmentName}");
                errorMessage.AppendLine($"Fail on Warning: {failOnWarning}");
                throw new Exception(errorMessage.ToString());
            }
        }
Ejemplo n.º 29
0
    public void ExecPackage()
    {
        Server server = new Server(_server);

        IntegrationServices service = new IntegrationServices(server);

        Catalog catalogObject = service.Catalogs[_catalog];

        CatalogFolder folderObject = catalogObject.Folders[_folder];

        ProjectInfo projectObject = folderObject.Projects[_project];

        PackageInfo p = projectObject.Packages[_packageinfo.Name];

        p.Execute(false, null);

        //Package p = dtsapp.LoadFromSqlServer(string.Concat(_packageinfo.Folder +
        //            "\\" + _packageinfo.Name), _server, null, null, null);
        //p.Execute();
    }
Ejemplo n.º 30
0
        private void Create_Click(object sender, EventArgs e)
        {
            Server server = new Server(_server);

            try
            {
                IntegrationServices isServer = new IntegrationServices(server);

                Catalog catalog = isServer.Catalogs["SSISDB"];

                CatalogFolder folder = new CatalogFolder(catalog, "ProSSIS", "New Folder");
                folder.Create();

                MessageBox.Show("Folder Created");
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.ToString());
            }
        }
Ejemplo n.º 31
0
        internal static string GetPlatform(IntegrationServices store)
        {
            if (store == null)
            {
                return(null);
            }

            string cmdText = "EXEC xp_msver platform";
            object obj     = SqlHelper.ExecuteSQLCommand(store.Connection, CommandType.Text, cmdText, null, ExecuteType.ExecuteReader);

            using (SqlDataReader sqlDataReader = (SqlDataReader)obj)
            {
                if (sqlDataReader.Read())
                {
                    return(sqlDataReader.GetString(3));
                }
            }

            return(null);
        }
Ejemplo n.º 32
0
        public override IExecutionResult Run()
        {
            var connection = new SqlConnection(string.Format(@"Data Source={0};Initial Catalog=master;Integrated Security=SSPI;", Etl.Server));
            var integrationServices = new IntegrationServices(connection);

            Catalog catalog;
            PackageInfo package;
            GetPackage(integrationServices, out catalog, out package);

            var setValueParameters = new Collection<PackageInfo.ExecutionValueParameterSet>();
            setValueParameters.Add(new PackageInfo.ExecutionValueParameterSet
            {
                ObjectType = 50,
                ParameterName = "SYNCHRONIZED",
                ParameterValue = 1
            });
            var parameters = Parameterize(Etl.Parameters, package.Parameters, package.Name);
            parameters.ToList().ForEach(p => setValueParameters.Add(p));

            long executionIdentifier = -1;
            if (Etl.Timeout==0)
                executionIdentifier = package.Execute(Etl.Is32Bits, null, setValueParameters);
            else
                executionIdentifier = package.Execute(Etl.Is32Bits, null, setValueParameters, Etl.Timeout);

            var execution = catalog.Executions[executionIdentifier];

            var etlRunResultFactory = new EtlRunResultFactory();

            var result = etlRunResultFactory.Instantiate(
                execution.Status
                , execution.Messages.Where(m => m.MessageType == 120 || m.MessageType == 110).Select(m => m.Message)
                , execution.StartTime
                , execution.EndTime);

            return result;
        }
Ejemplo n.º 33
0
        private void GetPackage(IntegrationServices integrationServices, out Catalog catalog, out PackageInfo package)
        {
            if (integrationServices.Catalogs.Contains(Etl.Catalog))
                catalog = integrationServices.Catalogs[Etl.Catalog];
            else
            {
                var names = String.Join(", ",integrationServices.Catalogs.Select(c => c.Name));
                throw new ArgumentOutOfRangeException("Catalog", String.Format("The catalog named '{0}' hasn't been found on the server '{1}'. List of existing catalogs: {2}.", Etl.Catalog, Etl.Server, names));
            }

            CatalogFolder folder;
            if (catalog.Folders.Contains(Etl.Folder))
                folder = catalog.Folders[Etl.Folder];
            else
            {
                var names = String.Join(", ", catalog.Folders.Select(f => f.Name));
                throw new ArgumentOutOfRangeException("Folder", String.Format("The folder named '{0}' hasn't been found on the catalog '{1}'. List of existing folders: {2}.", Etl.Folder, Etl.Catalog, names));
            }

            ProjectInfo project;
            if (folder.Projects.Contains(Etl.Project))
                project = folder.Projects[Etl.Project];
            else
            {
                var names = String.Join(", ", folder.Projects.Select(p => p.Name));
                throw new ArgumentOutOfRangeException("Project", String.Format("The project named '{0}' hasn't been found on the catalog '{1}'. List of existing projects: {2}.", Etl.Project, Etl.Folder, names));
            }

            if (project.Packages.Contains(Etl.Name))
                package = project.Packages[Etl.Name];
            else
            {
                var names = String.Join(", ", project.Packages.Select(p => p.Name));
                throw new ArgumentOutOfRangeException("Name", String.Format("The package named '{0}' hasn't been found on the project '{1}'. List of existing packages: {2}.", Etl.Name, Etl.Project, names));
            }
        }
Ejemplo n.º 34
0
        public async Task<IHttpActionResult> RunAsync(ODataActionParameters parameters)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            var name = (string)parameters["name"];
            var variables = (IEnumerable<JobParameter>)parameters["parameters"];
            var descriptor = SsisServiceHelper.SsisJobDescriptors[name];

            {
                var connection = default(SqlConnection);
                try
                {
                    connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["MasterConnection"].ConnectionString);
                    var services = new IntegrationServices(connection);
                    var environment = services.Catalogs["SSISDB"].Folders["Diamond"].Environments[descriptor.EnvironmentName];
                    foreach (var variable in variables)
                    {
                        environment.Variables[variable.Name].Value = SsisServiceHelper.Convert(variable.Value, variable.Type);
                    }

                    environment.Alter();
                }
                finally
                {
                    if (connection != null) connection.Dispose();
                }
            }

            {
                var connection = default(SqlConnection);
                var command = default(SqlCommand);
                try
                {
                    connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["MSDBConnection"].ConnectionString);
                    command = connection.CreateCommand();
                    command.CommandText = "sp_start_job";
                    command.CommandType = CommandType.StoredProcedure;

                    {
                        var parameter = command.CreateParameter();
                        parameter.ParameterName = "@return_value";
                        parameter.SqlDbType = SqlDbType.Int;
                        parameter.Direction = ParameterDirection.ReturnValue;
                        command.Parameters.Add(parameter);
                    }

                    {
                        var parameter = command.CreateParameter();
                        parameter.ParameterName = "@job_name";
                        parameter.Direction = ParameterDirection.Input;
                        parameter.SqlDbType = SqlDbType.VarChar;
                        parameter.Value = descriptor.JobName;
                        command.Parameters.Add(parameter);
                    }

                    await connection.OpenAsync();
                    await command.ExecuteNonQueryAsync();

                    var result = (int)command.Parameters["@return_value"].Value;
                    if (result != 0)
                    {
                        throw new InvalidOperationException(string.Format("SQL Server Agent job, {0}, failed to start.", descriptor.JobName));
                    }

                    return Ok();
                }
                finally
                {
                    if (command != null) command.Dispose();
                    if (connection != null) connection.Dispose();
                }
            }
        }