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