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}"); } }
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); }
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"; }
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); } }
} //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); }
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); } }
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); }
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]); }
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); } }
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); }
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()); }
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); }
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; }
//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); }
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); }
// 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); }
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); }
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); }
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)); }
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); } }
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)); } }
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); } }
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); }
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); }
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()); } }
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(); }
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()); } }
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); }
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; }
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)); } }
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(); } } }