public static long Execute(this PackageInfo packageInfo,
bool use32RuntimeOn64, EnvironmentReference reference,
Collection<PackageInfo.ExecutionValueParameterSet> setValueParameters,
Collection<PackageInfo.PropertyOverrideParameterSet> propertyOverrideParameters,
int commandTimeout)
{
long executionId = 0;
string connectionString = packageInfo.Parent.Parent.Parent.Parent.Connection.ServerConnection.ConnectionString;
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
executionId = CreateExecution(packageInfo, use32RuntimeOn64,
reference, commandTimeout, connection);
SetExecutionParameterValues(packageInfo, setValueParameters,
commandTimeout, connection, executionId);
SetPropertyOverrideParameters(packageInfo, propertyOverrideParameters,
commandTimeout, connection, executionId);
StartExecution(commandTimeout, executionId, connection, packageInfo);
}
return executionId;
}
public static long Execute(this PackageInfo @this, bool use32RuntimeOn64, EnvironmentReference reference, Collection <PackageInfo.ExecutionValueParameterSet> setValueParameters, int commandTimeout)
{
object obj = SqlHelper.ExecuteSQLCommand(((IntegrationServices)@this.GetDomain()).Connection, CommandType.Text, @this.ScriptCreateExecution(use32RuntimeOn64, reference), null, ExecuteType.ExecuteScalar, commandTimeout);
if (setValueParameters != null && setValueParameters.Count > 0)
{
var param = new SqlParameter("execution_id", SqlDbType.Int);
param.Value = obj;
SqlParameter[] array = new SqlParameter[]
{
param
};
SqlHelper.ExecuteSQLCommand(((IntegrationServices)@this.GetDomain()).Connection, CommandType.Text, @this.ScriptSetExecutionValues(setValueParameters), array, ExecuteType.ExecuteNonQuery, commandTimeout);
}
var param3 = new SqlParameter("execution_id", SqlDbType.Int);
param3.Value = obj;
SqlParameter[] array3 = new SqlParameter[]
{
param3
};
SqlHelper.ExecuteSQLCommand(((IntegrationServices)@this.GetDomain()).Connection, CommandType.Text, @this.ScriptStartExecution(), array3, ExecuteType.ExecuteNonQuery, commandTimeout);
return((long)obj);
}
public static long Execute(this PackageInfo packageInfo,
bool use32RuntimeOn64, EnvironmentReference reference,
Collection <PackageInfo.ExecutionValueParameterSet> setValueParameters,
Collection <PackageInfo.PropertyOverrideParameterSet> propertyOverrideParameters,
int commandTimeout, string connectionString)
{
long executionId = 0;
//TODO investigate further why it's not working anymore:
// STACK: Method not found: 'Microsoft.SqlServer.Management.Sdk.Sfc.SqlStoreConnection Microsoft.SqlServer.Management.IntegrationServices.IntegrationServices.get_Connection()'
//connectionString = packageInfo.Parent.Parent.Parent.Parent.Connection.ServerConnection.ConnectionString;
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
executionId = CreateExecution(packageInfo, use32RuntimeOn64,
reference, commandTimeout, connection);
SetExecutionParameterValues(packageInfo, setValueParameters,
commandTimeout, connection, executionId);
SetPropertyOverrideParameters(packageInfo, propertyOverrideParameters,
commandTimeout, connection, executionId);
StartExecution(commandTimeout, executionId, connection, packageInfo);
}
return(executionId);
}
public static long Execute(this PackageInfo packageInfo,
bool use32RuntimeOn64, EnvironmentReference reference,
Collection<PackageInfo.ExecutionValueParameterSet> setValueParameters,
int commandTimeout)
{
return packageInfo.Execute(use32RuntimeOn64, reference, setValueParameters, null, commandTimeout);
}
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));
}
public static long Execute(this PackageInfo packageInfo,
bool use32RuntimeOn64, EnvironmentReference reference,
Collection <PackageInfo.ExecutionValueParameterSet> setValueParameters,
Collection <PackageInfo.PropertyOverrideParameterSet> propertyOverrideParameters,
int commandTimeout)
{
long executionId = 0;
string connectionString = packageInfo.Parent.Parent.Parent.Parent.Connection.ServerConnection.ConnectionString;
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
executionId = CreateExecution(packageInfo, use32RuntimeOn64,
reference, commandTimeout, connection);
SetExecutionParameterValues(packageInfo, setValueParameters,
commandTimeout, connection, executionId);
SetPropertyOverrideParameters(packageInfo, propertyOverrideParameters,
commandTimeout, connection, executionId);
StartExecution(commandTimeout, executionId, connection, packageInfo);
}
return(executionId);
}
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 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 SingleSensorReadingsLog FromSingleSensorReadings(Stream readings)
{
readings.Position = 0;
var log = new List <ILogMeasure>();
using (var reader = new StreamReader(readings))
{
var environmentContext = EnvironmentReference.Deserialize(reader.ReadLine());
var heading = _logRowProvider.Load(reader.ReadLine());
string line = null;
while ((line = reader.ReadLine()) != null)
{
log.Add(_logRowProvider.Load(line));
}
return(new SingleSensorReadingsLog {
DeviceInfo = heading, Values = log, Environment = environmentContext
});
}
}
private static long CreateExecution(PackageInfo packageInfo,
bool use32RuntimeOn64, EnvironmentReference reference,
int commandTimeout, SqlConnection connection)
{
long executionId = 0;
string storedProcName = string.Format("{0}.{1}",
packageInfo.Parent.Parent.Parent.Name, CREATE_EXECUTION);
using (var command = new SqlCommand(storedProcName, connection))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = commandTimeout;
command.Parameters.Add(FOLDER_NAME, SqlDbType.NVarChar, 128);
command.Parameters.Add(PROJECT_NAME, SqlDbType.NVarChar, 128);
command.Parameters.Add(PACKAGE_NAME, SqlDbType.NVarChar, 260);
command.Parameters.Add(REFERENCE_ID, SqlDbType.BigInt);
command.Parameters.Add(USE32BITRUNTIME, SqlDbType.Bit);
command.Parameters.Add(EXECUTION_ID, SqlDbType.Int);
command.Parameters[EXECUTION_ID].Direction = ParameterDirection.Output;
command.Parameters[FOLDER_NAME].Value = packageInfo.Parent.Parent.Name;
command.Parameters[PROJECT_NAME].Value = packageInfo.Parent.Name;
command.Parameters[PACKAGE_NAME].Value = packageInfo.Name;
if (reference != null)
{
command.Parameters[REFERENCE_ID].Value = reference.ReferenceId;
}
command.Parameters[USE32BITRUNTIME].Value = use32RuntimeOn64;
command.ExecuteNonQuery();
executionId = long.Parse(command.Parameters[EXECUTION_ID].Value.ToString());
}
return executionId;
}
private static long CreateExecution(PackageInfo packageInfo,
bool use32RuntimeOn64, EnvironmentReference reference,
int commandTimeout, SqlConnection connection)
{
long executionId = 0;
string storedProcName = string.Format("{0}.{1}",
packageInfo.Parent.Parent.Parent.Name, CREATE_EXECUTION);
using (var command = new SqlCommand(storedProcName, connection))
{
command.CommandType = CommandType.StoredProcedure;
command.CommandTimeout = commandTimeout;
command.Parameters.Add(FOLDER_NAME, SqlDbType.NVarChar, 128);
command.Parameters.Add(PROJECT_NAME, SqlDbType.NVarChar, 128);
command.Parameters.Add(PACKAGE_NAME, SqlDbType.NVarChar, 260);
command.Parameters.Add(REFERENCE_ID, SqlDbType.BigInt);
command.Parameters.Add(USE32BITRUNTIME, SqlDbType.Bit);
command.Parameters.Add(EXECUTION_ID, SqlDbType.Int);
command.Parameters[EXECUTION_ID].Direction = ParameterDirection.Output;
command.Parameters[FOLDER_NAME].Value = packageInfo.Parent.Parent.Name;
command.Parameters[PROJECT_NAME].Value = packageInfo.Parent.Name;
command.Parameters[PACKAGE_NAME].Value = packageInfo.Name;
if (reference != null)
{
command.Parameters[REFERENCE_ID].Value = reference.ReferenceId;
}
command.Parameters[USE32BITRUNTIME].Value = use32RuntimeOn64;
command.ExecuteNonQuery();
executionId = long.Parse(command.Parameters[EXECUTION_ID].Value.ToString());
}
return(executionId);
}
public float GetSensorEnvironmentContext(ILogHeading header, EnvironmentReference environment)
{
return(RegisteredComponents[header.DeviceType.Value].EnvironmentContext(environment));
}
private static string ScriptCreateExecution(this PackageInfo @this, bool use32RuntimeOn64, EnvironmentReference reference)
{
if (use32RuntimeOn64)
{
string platform = Helpers.GetPlatform(@this.GetDomain() as IntegrationServices);
if (platform != null && !platform.Contains("64"))
{
throw new InvalidArgumentException($"Use32RuntimeOn64_Not64: {use32RuntimeOn64}");
}
}
SfcTsqlProcFormatter sfcTsqlProcFormatter = new SfcTsqlProcFormatter();
sfcTsqlProcFormatter.Procedure = string.Format(CultureInfo.InvariantCulture, "[{0}].[catalog].[create_execution]", new object[]
{
Helpers.GetEscapedName(@this.Parent.Parent.Parent.Name)
});
sfcTsqlProcFormatter.Arguments.Add(new SfcTsqlProcFormatter.SprocArg("package_name", "Name", true, false));
List <SfcTsqlProcFormatter.RuntimeArg> list = new List <SfcTsqlProcFormatter.RuntimeArg>();
long num = 0L;
SfcTsqlProcFormatter.RuntimeArg item = new SfcTsqlProcFormatter.RuntimeArg(typeof(long), num);
list.Add(item);
sfcTsqlProcFormatter.Arguments.Add(new SfcTsqlProcFormatter.SprocArg("execution_id", true, true));
item = new SfcTsqlProcFormatter.RuntimeArg(typeof(string), @this.Parent.Parent.Name);
list.Add(item);
sfcTsqlProcFormatter.Arguments.Add(new SfcTsqlProcFormatter.SprocArg("folder_name", true));
item = new SfcTsqlProcFormatter.RuntimeArg(typeof(string), @this.Parent.Name);
list.Add(item);
sfcTsqlProcFormatter.Arguments.Add(new SfcTsqlProcFormatter.SprocArg("project_name", true));
item = new SfcTsqlProcFormatter.RuntimeArg(typeof(bool), use32RuntimeOn64);
list.Add(item);
sfcTsqlProcFormatter.Arguments.Add(new SfcTsqlProcFormatter.SprocArg("use32bitruntime", true));
SqlInt64 sqlInt = (reference != null) ? reference.ReferenceId : SqlInt64.Null;
item = new SfcTsqlProcFormatter.RuntimeArg(typeof(SqlInt64), sqlInt);
list.Add(item);
sfcTsqlProcFormatter.Arguments.Add(new SfcTsqlProcFormatter.SprocArg("reference_id", true));
Version serverVersion = ((IntegrationServices)@this.GetDomain()).Connection.ServerVersion;
if (serverVersion.Major >= 14)
{
if (serverVersion >= new Version(14, 0, 700))
{
item = new SfcTsqlProcFormatter.RuntimeArg(typeof(bool), false);
list.Add(item);
sfcTsqlProcFormatter.Arguments.Add(new SfcTsqlProcFormatter.SprocArg("runinscaleout", true));
}
else
{
item = new SfcTsqlProcFormatter.RuntimeArg(typeof(bool), false);
list.Add(item);
sfcTsqlProcFormatter.Arguments.Add(new SfcTsqlProcFormatter.SprocArg("runincluster", true));
}
}
return(sfcTsqlProcFormatter.GenerateScript(@this, list));
}
public override void ProcessCurrentDelayed(List <Task <DeviceDetails> > taskQueue, EnvironmentReference environmentContext, LogFile file)
{
var task = Context.CallSubOrchestratorAsync <DeviceDetails>(nameof(ProcessBatchOrchestrator), new LogFile(environmentContext, file.Queue));
taskQueue.Add(task);
}
public virtual void ProcessCurrentDelayed(List <Task <DeviceDetails> > taskQueue, EnvironmentReference environmentContext, LogFile file)
{
taskQueue.Add(ProcessBatch(new LogFile(environmentContext, file.Queue)));
}
