private void RunPackage(Extraction extraction, ExtractionJob job, ExtractionTrigger extractionTrigger)
{
var packagePath = extraction.PackagePath + extraction.PackageName + ".dtsx";
dts.Package package = _app.LoadPackage(packagePath, null);
Log.WriteErrorLog(string.Format("Starting package execution for {0}-{1}.", _serviceName, extraction.Title));
//Update tigger to Running
extractionTrigger.Status = PackageStatus.Running;
extractionTrigger.Remark = "Package currently running...";
_dataManager.UpdateExtractionTrigger(_connectionString, extractionTrigger);
//DD-mon-YYYY
extraction.ScriptText = extraction.ScriptText.Replace("@StartDate", job.StartDate.GetOracleDate());
extraction.ScriptText = extraction.ScriptText.Replace("@EndDate", job.EndDate.GetOracleDate());
extraction.ScriptText = extraction.ScriptText.Replace("@FYYear", string.Format("FY{0}", job.EndDate.Year.ToString()));
extraction.ScriptText = extraction.ScriptText.Replace("@Year", string.Format("{0}", job.EndDate.Year.ToString()));
extraction.ScriptText = extraction.ScriptText.Replace("@Period", string.Format("M{0}", job.EndDate.Month.ToString().PadLeft(2, '0')));
package.Variables["StorProc"].Value = extraction.ScriptText;//"Script", true, "", extraction.ScriptText);
var result = package.Execute();
if (result == dts.DTSExecResult.Success)
{
int ssisDuration = 0;
//int.TryParse(Math.Round((package.ExecutionDuration / 1000.00), 2).ToString(), out ssisDuration);
ssisDuration = (package.ExecutionDuration / 1000);
Log.WriteErrorLog(string.Format("Package execution for {0}-{1} successfull.", _serviceName, extraction.Title));
//Update tigger to Done
extractionTrigger.Status = PackageStatus.Done;
//extractionTrigger.Remark = string.Format("{0}{1}lines extracted ({2})", string.Format("Package {0}-{1} successfully executed: ", _serviceName, extraction.Title), package.Variables["RecCount"].Value.ToString(), DateTimeExtensions.GetRealTime(ssisDuration));
extractionTrigger.Remark = string.Format("{0}{1}lines extracted ({2})", string.Format("Package {0}-{1} successfully executed: ", _serviceName, extraction.Title), package.Variables["RecCount"].Value.ToString(), HHMMSS(ssisDuration));
_dataManager.UpdateExtractionTrigger(_connectionString, extractionTrigger);
//_Exp = _ProcessName & ": " & _ssisCount & " lines extracted (" & HHMMSS(_ssisDuration) & ")"
}
else
{
string errorMessage = string.Empty;
foreach (var error in package.Errors)
{
errorMessage += error.Description;
}
Log.WriteErrorLog(string.Format("Package execution for {0}-{1} failed.{2}", _serviceName, extraction.Title, errorMessage));
//Update tigger to Fail
extractionTrigger.Status = PackageStatus.Fail;
extractionTrigger.Remark = string.Format("Package {0}-{1} failed.", _serviceName, extraction.Title) + "\n" + errorMessage;
_dataManager.UpdateExtractionTrigger(_connectionString, extractionTrigger);
}
}
private void RunProcedure(Extraction extraction, ExtractionJob job, ExtractionTrigger extractionTrigger)
{
var actionName = extraction.PackageName;
var parameters = new List <SqlParameter>();
parameters.Add(new SqlParameter()
{
ParameterName = "StartDate", Value = job.StartDate.GetOracleDate()
});
parameters.Add(new SqlParameter()
{
ParameterName = "EndDate", Value = job.EndDate.GetOracleDate()
});
parameters.Add(new SqlParameter()
{
ParameterName = "FYYear", Value = string.Format("FY{0}", job.EndDate.Year.ToString())
});
parameters.Add(new SqlParameter()
{
ParameterName = "Year", Value = string.Format("{0}", job.EndDate.Year.ToString())
});
parameters.Add(new SqlParameter()
{
ParameterName = "Period", Value = string.Format("M{0}", job.EndDate.Month.ToString().PadLeft(2, '0'))
});
Log.WriteErrorLog(string.Format("Starting store procedure execution for {0}-{1}.", _serviceName, extraction.Title));
//Update tigger to Running
extractionTrigger.Status = PackageStatus.Running;
extractionTrigger.Remark = "Store procedure currently running...";
_dataManager.UpdateExtractionTrigger(_connectionString, extractionTrigger);
var result = string.Empty;
SqlDataManager.RunProcedureWithMessage(_connectionString, actionName, parameters.ToArray());
if (result.Contains("Success"))
{
Log.WriteErrorLog(string.Format("Store procedure execution for {0}-{1} successfull.", _serviceName, extraction.Title));
//Update tigger to Done
extractionTrigger.Status = PackageStatus.Done;
extractionTrigger.Remark = result;
_dataManager.UpdateExtractionTrigger(_connectionString, extractionTrigger);
}
else
{
Log.WriteErrorLog(string.Format("Store procedure execution for {0}-{1} failed.{2}", _serviceName, extraction.Title, result));
//Update tigger to Fail
extractionTrigger.Status = PackageStatus.Fail;
extractionTrigger.Remark = string.Format("Package {0}-{1} failed. {2}", _serviceName, extraction.Title, result);
_dataManager.UpdateExtractionTrigger(_connectionString, extractionTrigger);
}
}
public void UpdateExtractionTrigger(string connectionString, ExtractionTrigger trigger)
{
using (var entityContext = new CoreContext(connectionString))
{
var existingEntity = (from e in entityContext.Set <ExtractionTrigger>()
where e.ExtractionTriggerId == trigger.ExtractionTriggerId
select e).FirstOrDefault();
SimpleMapper.PropertyMap(trigger, existingEntity);
entityContext.SaveChanges();
}
}
public ExtractionTrigger UpdateExtractionTrigger(ExtractionTrigger extractionTrigger)
{
return(Channel.UpdateExtractionTrigger(extractionTrigger));
}
public void UpdateTrigger(ExtractionTrigger trigger)
{
_triggerRepository.Update(trigger);
}