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); }
public static long Execute(this PackageInfo packageInfo, bool use32RuntimeOn64, EnvironmentReference reference, Collection <PackageInfo.ExecutionValueParameterSet> setValueParameters, int commandTimeout, string connectionString) { return(packageInfo.Execute(use32RuntimeOn64, reference, setValueParameters, null, commandTimeout, connectionString)); }
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 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 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 btnStart_Click(object sender, EventArgs e) { // Connecting to the SQL Server instance where the catalog is located using (SqlConnection ssisConnection = new SqlConnection("Data Source=.;Initial Catalog=master;Integrated Security=SSPI;")) { try { // SSIS server object with connection IntegrationServices ssisServer = new IntegrationServices(ssisConnection); // The reference to the package which you want to execute lblStatus.Text = "Loading package from catalog."; Form.ActiveForm.Refresh(); PackageInfo ssisPackage = ssisServer.Catalogs["SSISDB"].Folders["Extending SSIS"].Projects["Chapter 21"].Packages["myPackage.dtsx"]; // Setting parameters Collection <PackageInfo.ExecutionValueParameterSet> executionParameter = new Collection <PackageInfo.ExecutionValueParameterSet>(); // Add execution parameter for an asynchronized (value=0, default) or synchronized (value=1) execution executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "SYNCHRONIZED", ParameterValue = 0 }); // Add execution parameter (value) to override the default logging level (0=None, 1=Basic, 2=Performance, 3=Verbose) executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 50, ParameterName = "LOGGING_LEVEL", ParameterValue = 3 }); // Add a project parameter (value) to fill a project parameter //executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 20, ParameterName = "MyProjectParameter", ParameterValue = "some value" }); // Add a package parameter (value) to fill a package parameter //executionParameter.Add(new PackageInfo.ExecutionValueParameterSet { ObjectType = 30, ParameterName = "MyPackageParameter", ParameterValue = "some value" }); // Execute package and return the ServerExecutionId long executionIdentifier = ssisPackage.Execute(false, null, executionParameter); // Get execution details with the ServerExecutionId from the previous step lblStatus.Text = "Executing package"; Form.ActiveForm.Refresh(); ExecutionOperation executionOperation = ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier]; // Workaround for 30 second timeout: // Loop while the execution is not completed while (!(executionOperation.Completed)) { // Refresh execution info executionOperation.Refresh(); // Wait 5 seconds before refreshing (we don't want to stress the server) System.Threading.Thread.Sleep(5000); } // Showing the ServerExecutionId lblStatus.Text = "Execution " + executionOperation.Id.ToString() + " finished: " + executionOperation.Status.ToString(); // Clear listbox before adding log rows to it lbLog.Items.Clear(); // Loop through the log and add the messages to the listbox foreach (OperationMessage message in ssisServer.Catalogs["SSISDB"].Executions[executionIdentifier].Messages) { lbLog.Items.Add(message.MessageType.ToString() + ": " + message.Message); } } catch (Exception ex) { // Log code for exceptions lblStatus.Text = "Error: " + ex.Message; } } }