static void Main(string[] args)
{
Microsoft.SqlServer.Dts.Runtime.Application App = new Microsoft.SqlServer.Dts.Runtime.Application();
string packagePath = System.Configuration.ConfigurationSettings.AppSettings["PACKAGE_PATH"].ToString();
string configPath = System.Configuration.ConfigurationSettings.AppSettings["PACKAGE_CONFIG_PATH"].ToString();
string logPath = System.Configuration.ConfigurationSettings.AppSettings["LOG_PATH"].ToString();
string err = "";
Package stdProPkg1 = App.LoadPackage(packagePath, null);
stdProPkg1.ImportConfigurationFile(configPath);
DTSExecResult stdProResult1 = stdProPkg1.Execute();
if (stdProResult1.ToString() == "Success")
{
err = "SUCCESS";
}
else if (stdProResult1.ToString() == "Failure")
{
foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in stdProPkg1.Errors)
{
string error = local_DtsError.Description.ToString();
err = err + error;
}
}
WriteLog(err, logPath);
}
public PrecedenceConstraint Add_PrecConstr(
Executable ex1,
Executable ex2,
Sequence sqn = null,
DTSPrecedenceEvalOp EvalOp = DTSPrecedenceEvalOp.Constraint,
DTSExecResult Result = DTSExecResult.Success,
string Expression = "",
bool LogicalAnd = true
)
{
PrecedenceConstraint cstr = null;
if (sqn == null)
{
cstr = p.PrecedenceConstraints.Add(ex1, ex2);
}
else
{
cstr = sqn.PrecedenceConstraints.Add(ex1, ex2);
}
cstr.EvalOp = EvalOp;
cstr.Value = Result;
cstr.Expression = Expression;
cstr.LogicalAnd = LogicalAnd;
return(cstr);
}
public bool ejecutar()
{
try
{
//Creamos una aplicación para realizar la ejecución
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
//Creamos un paquete y le asignamos el que queremos ejecutar
//Package package = app.LoadPackage(@"C:\Package_Usuario.dtsx", null);
Package package = app.LoadPackage(this.urlPackage, null);
//package.Variables
Microsoft.SqlServer.Dts.Runtime.Variables variables = package.Variables;
variables["proceso"].Value = this.pro.idproceso;
//Ejecutamos el paquete
DTSExecResult resultEtl = package.Execute(null, variables, null, null, null);
//Imprimimos el resultado de la ejecución
//Console.WriteLine("Resultado de la ejecución: {0}", result.ToString());
if (resultEtl != Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success)
{
return(false);
}
else
{
return(true);
}
}
catch (Exception ex) {
return(false);
}
}
private void Button_Click(object sender, RoutedEventArgs e)
{
eks.Visibility = Visibility.Hidden;
lblstatus.Text = "EKSTRAK SEDANG DALAM PROSES MOHON TUNGGU SEBENTAR !";
MessageBoxButton btn = MessageBoxButton.YesNo;
var result = ModernDialog.ShowMessage("EKSTRAK DATA AKAN MENGHAPUS DATA SEBELUMNYA ! \n LANJUTKAN ?", "INFORMASI", btn);
if (result.ToString() == "Yes")
{
Microsoft.SqlServer.Dts.Runtime.Application myApplication = new Microsoft.SqlServer.Dts.Runtime.Application();
// Load package from file system (use LoadFromSqlServer for SQL Server based packages)
Package myPackage = myApplication.LoadPackage(@"D:\github\DW_FINAL\BAPPEDADW\BAPPEDADW\Analisis\Package.dtsx", null);
// Execute package
DTSExecResult myResult = myPackage.Execute();
lblstatus.Text = "HASIL EKSTRAKSI : " + myResult.ToString();
// Show the execution result
}
eks.Visibility = Visibility.Visible;
}
protected void Button1_Click1(object sender, EventArgs e)
{
string logtime = "[ " + DateTime.Now.ToLocalTime().ToString() + " ]";
//execute the package for kyperatotechone
try
{
// Instantiate SSIS application object
Microsoft.SqlServer.Dts.Runtime.Application myApplication = new Microsoft.SqlServer.Dts.Runtime.Application();
string fileName = Path.Combine(Server.MapPath("~/Upload"), "ImportExcelForFinanceDashboard.dtsx");
TextBox3.Text = "";
TextBox3.Text = TextBox3.Text + logtime + "Loading KPI_Finance_Strategic_fromexcel.dtsx from server...\r\n";
string fileName2 = fileName.Replace(@"\", @"\\");
Package myPackage = myApplication.LoadPackage(@fileName2, null);
// TextBox3.Text = TextBox3.Text + logtime + "Loading package paramter - rundate...\r\n";
// myPackage.Variables["excelfolder"].Value = kpidatafolder;
// Execute package
TextBox3.Text = TextBox3.Text + logtime + "Executing package to load the Finance KPI data from spreadsheet...\r\n";
DTSExecResult myResult = myPackage.Execute();
// Show the execution result
TextBox3.Text = TextBox3.Text + logtime + "Package executed result: " + myResult.ToString() + " \r\n";
GridView1.DataBind();
GridView2.DataBind();
}
catch (Exception ee)
{
TextBox3.Text = logtime + "Package executed fail and Erros is: " + ee.Message + " \r\n";
}
}
protected void Button7_Click(object sender, EventArgs e)
{
TextBox3.Text = "";
string logtime = "[ " + DateTime.Now.ToLocalTime().ToString() + " ]";
TextBox3.Text = TextBox3.Text + logtime + " Start to export timesheet for Techone CiAnywhere...\r\n";
//invoke ssis package to export
string fileserverfolder = ConfigurationManager.AppSettings["ComCareToT1CiAnwhereTimesheetExportfolder"].ToString();
// Instantiate SSIS application object
Microsoft.SqlServer.Dts.Runtime.Application myApplication = new Microsoft.SqlServer.Dts.Runtime.Application();
string fileName3 = Path.Combine(Server.MapPath("~/Upload"), "ComCareTimesheetToT1Cianywhere.dtsx");
// TextBox3.Text = TextBox3.Text+logtime + "Loading import package from file system...\r\n";
Package myPackage = myApplication.LoadPackage(@fileName3, null);
myPackage.Variables["filefolder"].Value = fileserverfolder;
TextBox3.Text = TextBox3.Text + logtime + " Starting to export timesheet data to file: " + fileserverfolder + "...\r\n";
ShowProcessBar();
DTSExecResult myResult = myPackage.Execute();
// Show the execution result
TextBox3.Text = TextBox3.Text + logtime + " Export result: " + myResult.ToString() + " \r\n";
TextBox3.Text = TextBox3.Text + logtime + " Finish exporting timesheet for Techone CiAnywhere.\r\n";
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
DTSExecResult Result = base.Validate(connections, variableDispenser, componentEvents, log);
if (Result == DTSExecResult.Success)
{
if (string.IsNullOrEmpty(Server))
{
componentEvents.FireError(0, "Reporting Service Execution Task", "Missing configuration value - Report Server", "", 0);
return(DTSExecResult.Failure);
}
if (string.IsNullOrEmpty(SelectedReport))
{
componentEvents.FireError(0, "Reporting Service Execution Task", "Missing configuration value - Selected Report", "", 0);
return(DTSExecResult.Failure);
}
if (string.IsNullOrEmpty(FileFormat))
{
componentEvents.FireError(0, "Reporting Service Execution Task", "Missing configuration value - Output File Format", "", 0);
return(DTSExecResult.Failure);
}
if (string.IsNullOrEmpty(FileName))
{
componentEvents.FireError(0, "Reporting Service Execution Task", "Missing configuration value - Output File Name", "", 0);
return(DTSExecResult.Failure);
}
}
return(DTSExecResult.Success);
}
protected void SSISExport(string filename, int startno, int endno)
{
string logtime = "[ " + DateTime.Now.ToLocalTime().ToString() + " ]";
//invoke ssis package to export
string fileserverfolder = ConfigurationManager.AppSettings["CCToT1Manyfiles"].ToString();
// Instantiate SSIS application object
Microsoft.SqlServer.Dts.Runtime.Application myApplication = new Microsoft.SqlServer.Dts.Runtime.Application();
string fileName3 = Path.Combine(Server.MapPath("~/Upload"), "ComCareTimesheetToT1Cianywhere_severalfiles.dtsx");
// TextBox3.Text = TextBox3.Text+logtime + "Loading import package from file system...\r\n";
Package myPackage = myApplication.LoadPackage(@fileName3, null);
myPackage.Variables["filefolder"].Value = fileserverfolder;
myPackage.Variables["filename"].Value = filename;
myPackage.Variables["startno"].Value = startno;
myPackage.Variables["endno"].Value = endno;
TextBox3.Text = TextBox3.Text + logtime + " Starting to export timesheet data to file: " + filename + "...\r\n";
ShowProcessBar();
try
{
DTSExecResult myResult = myPackage.Execute();
// Show the execution result
TextBox3.Text = TextBox3.Text + logtime + " " + filename + " export result: " + myResult.ToString() + " \r\n";
}
catch (Exception ex)
{
TextBox3.Text = TextBox3.Text + logtime + "Load fail and ERROR is :" + ex.Message;
}
finally
{
myPackage.Dispose();
myPackage = null;
}
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
Events = componentEvents;
Conns = connections;
Transaction = transaction;
DTSExecResult execResult = DTSExecResult.Success;
if (!DoNothingAndContinue)
{
try
{
SleepUntilWaitTime();
SleepSomeMinutes();
WaitForSQL();
}
catch
{
execResult = DTSExecResult.Failure;
}
}
else
{
FireInformation("DoNothingAndContinueExecution is set to true - task will do nothing and continue.");
}
return(execResult);
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
Events = componentEvents;
Conns = connections;
DTSExecResult execResult = base.Validate(connections, variableDispenser, componentEvents, log);
if (execResult == DTSExecResult.Success)
{
if (!DoNothingAndContinue)
{
if (HasSQLCheckStatement && !HasSQLConnection)
{
FireError("An Sql check statement is defined, aber no connection exists. Please select a valid ADO.NET or OLEDB connection or leave the sql check statement blank.");
}
if (HasSQLConnection && HasSQLCheckStatement && !HasMaximumSQLWaitTime)
{
FireWarning("A Sql check statement and connection exists, but no maximum wait time was entered. The default of 24h is used.");
}
if (HasSQLConnection && HasSQLCheckStatement && !HasSQLRepetitionFrequencyInMinutes)
{
FireWarning("A Sql check statement and connection exists, but no Sql repetition frequency was entered. The SQL statement will be fired on the database every second. This can cause performance issues.");
}
}
}
return(execResult);
}
public override sealed void LoadFromXml(XmlNode testXml)
{
if (testXml.Name != "Test")
{
throw new ArgumentException(string.Format("The Xml does not contain the correct type ({0}).", "Test"));
}
Name = testXml.Attributes != null && testXml.Attributes["name"] != null ? testXml.Attributes["name"].Value : null;
PackageLocation = testXml.Attributes != null && testXml.Attributes["package"] != null ? testXml.Attributes["package"].Value : null;
Task = testXml.Attributes != null && testXml.Attributes["task"] != null ? testXml.Attributes["task"].Value : null;
Asserts = LoadAsserts(testXml);
TestSetup = new CommandSet(string.IsNullOrEmpty(Name) ? "Setup" : Name + " Setup", TestSuite, testXml["TestSetup"]);
TestTeardown = new CommandSet(string.IsNullOrEmpty(Name) ? "Teardown" : Name + " Teardown", TestSuite, testXml["TestTeardown"]);
XmlNode xmlNode = testXml.Attributes != null?testXml.Attributes.GetNamedItem("taskResult") : null;
if (xmlNode == null)
{
TaskResult = DTSExecResult.Success;
}
else
{
TaskResult = (DTSExecResult)Enum.Parse(typeof(DTSExecResult), xmlNode.Value, true);
}
}
static void Main(string[] args)
{
string sourcePackage = string.Empty;
string path = string.Empty;
string variableName = string.Empty;
string designValue = string.Empty;
string newValue = string.Empty;
InformationListener listener = null;
sourcePackage = @"J:\Src\SO\SSIS\Package.dtsx";
path = @"J:\runtime";
variableName = "User::FolderPath";
listener = new InformationListener();
Application app = new Application();
Package pkg = null;
Variable ssisVariable = null;
pkg = app.LoadPackage(sourcePackage, null);
ssisVariable = pkg.Variables[variableName];
designValue = ssisVariable.Value.ToString();
Console.WriteLine(string.Format("Designtime value = {0}", designValue));
ssisVariable.Value = path;
newValue = ssisVariable.Value.ToString();
Console.WriteLine(string.Format("new value = {0}", newValue));
DTSExecResult results = DTSExecResult.Canceled;
results = pkg.Execute(null, null, listener, null, null);
Console.WriteLine("Press any key to continue");
Console.ReadKey();
}
private bool LoadIntoRCAD()
{
Application ssisApp = new Application();
SSISEventListener eventListener = new SSISEventListener();
string packagePath = null;
RegistryKey alignmentLoaderKey = Registry.LocalMachine.OpenSubKey(ALIGNMENTLOADER_KEY);
if (alignmentLoaderKey != null)
{
if (alignmentLoaderKey.GetValue(SSISPACKAGE_KEY, null) != null && alignmentLoaderKey.GetValueKind(SSISPACKAGE_KEY) == RegistryValueKind.String)
{
packagePath = alignmentLoaderKey.GetValue(SSISPACKAGE_KEY).ToString();
}
}
if (packagePath == null)
{
return(false);
}
Package rCADLoadNewAlignment = null;
try
{
//Should we read the location of the package out of the environment or registry?
rCADLoadNewAlignment = ssisApp.LoadPackage(packagePath, eventListener);
rCADLoadNewAlignment.Variables["AlignmentDataFile"].Value = _alignmentFile;
rCADLoadNewAlignment.Variables["AlignmentColumnDataFile"].Value = _alignmentColumnFile;
rCADLoadNewAlignment.Variables["AlignmentDataDataFile"].Value = _alignmentDataFile;
rCADLoadNewAlignment.Variables["AlignmentSequenceDataFile"].Value = _alignmentSequenceDataFile;
rCADLoadNewAlignment.Variables["SequenceMainDataFile"].Value = _sequenceMainFile;
rCADLoadNewAlignment.Variables["SequenceAccessionDataFile"].Value = _sequenceAccessionFile;
rCADLoadNewAlignment.Variables["rCADConnectionString"].Value = _data.ConnectionString;
rCADLoadNewAlignment.Variables["NextAlnID"].Value = _data.GetNextAlignmentID();
rCADLoadNewAlignment.Variables["NextSeqID"].Value = _data.GetNextSequenceID();
rCADLoadNewAlignment.Variables["SecondaryStructureBasePairsDataFile"].Value = _secondaryStructureBasePairsFile;
rCADLoadNewAlignment.Variables["SecondaryStructureExtentsDataFile"].Value = _secondaryStructureExtentsFile;
DTSExecResult result = rCADLoadNewAlignment.Execute(null, null, eventListener, null, null);
if (result == DTSExecResult.Success)
{
return(true);
}
else
{
using (StreamWriter output = new StreamWriter(new FileStream(Path.GetTempPath() + "AlignmentLoader.ssis_errors.out", FileMode.Create)))
{
output.Write(eventListener.ErrorLog);
output.Flush();
}
return(false);
}
}
catch
{
return(false);
}
}
// English Section
protected void Btncom_Click(object sender, EventArgs e)
{
Application app = new Application();
Package package = null;
package = app.LoadPackage(@"C:\Users\abhishekj\Documents\Visual Studio 2008\Projects\CA\CA\Package.dtsx", null);
DTSExecResult results = package.Execute();
}
public override DTSExecResult Execute(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log, object transaction)
{
DTSExecResult execResult = DTSExecResult.Success;
System.Windows.Forms.MessageBox.Show(this.displayText);
return(execResult);
}
/// <summary>
/// Performs alterations on graph
/// </summary>
/// <param name="graph">Graph</param>
/// <returns>True = success, false = failure</returns>
public static bool AlterGraph(Graph graph)
{
Logger.Common($"<-- {graph.FileName} -->");
// Loop through all column meta changes
foreach (TableMetaChange tableMetaChange in _databaseMetaChange.Tables.Values)
{
foreach (ColumnMetaChange columnMetaChange in tableMetaChange.Columns.Values.Where(c => c.ColumnChanges > 0))
{
Logger.Common($"Graph {graph.FileName} with changes {columnMetaChange.ColumnChanges} on table {tableMetaChange.OldTable?.Name}");
try
{
foreach (ColumnChanges change in columnMetaChange.ListChanges())
{
graph.Alter(columnMetaChange, change);
}
}
catch (Exception e)
{
Logger.Error($"[AlterGraph]{e.Message}\n{e.StackTrace}");
return(false);
}
}
}
// Validate package
DTSExecResult dtsExecResult = graph.Package.Validate(graph.Package.Connections, graph.Package.Variables, new PackageValidateErrorEvent(), null);
// Save package
try
{
string path = NameToSavePath(graph.FileName);
// If output folder doesn't not exist, then create it
if (!Directory.Exists(Path.GetFullPath(path)))
{
Directory.CreateDirectory(Path.GetDirectoryName(path));
}
_application.SaveToXml(path, graph.Package, null);
Logger.Common($"{graph.FileName} saved to {path}");
}
catch (Exception e)
{
Logger.Error($"[SaveXML]{e.Message}");
return(false);
}
if (dtsExecResult == DTSExecResult.Failure)
{
return(false);
}
Logger.Common($"</-- {graph.FileName} -->");
return(true);
}
private void ValidatePackage(Package package)
{
Microsoft.DataTransformationServices.Design.ComponentModel.EventHandlerErrorCollector events = new Microsoft.DataTransformationServices.Design.ComponentModel.EventHandlerErrorCollector();
DTSExecResult result = package.Validate(package.Connections, package.Variables, events, null);
List <string> listOtherErrors = new List <string>();
listOtherErrors.AddRange(RecurseContainersAndGetVariableValidationErrors(package));
foreach (Window w in this.ApplicationObject.Windows)
{
IDesignerHost designer = w.Object as IDesignerHost;
if (designer == null)
{
continue;
}
EditorWindow win = designer.GetService(typeof(Microsoft.DataWarehouse.ComponentModel.IComponentNavigator)) as EditorWindow;
if (win == null)
{
continue;
}
if ((win.PropertiesLinkComponent as Package) == package)
{
AddErrorsToVSErrorList(w, listOtherErrors.ToArray());
break;
}
}
foreach (KeyValuePair <IComponent, ICollection <IComponentErrorInfo> > pair in events.ComponentIssuesMap)
{
IComponent key = pair.Key;
System.Collections.ICollection issues = (System.Collections.ICollection)pair.Value;
IDesignerHost host = (IDesignerHost)packageDesigner.GetType().InvokeMember("DesignerHost", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.GetProperty | System.Reflection.BindingFlags.FlattenHierarchy | System.Reflection.BindingFlags.Instance, null, packageDesigner, null);
IDesigner containerDesigner = host.GetDesigner(key);
if (containerDesigner != null)
{
Type t = FindBaseType(containerDesigner.GetType(), "DtsContainerDesigner");
if (t == null)
{
continue;
}
//doesn't work... not sure why... so the following is the equivalent of this one line:
//t.InvokeMember("OnContainerValidated", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.FlattenHierarchy | System.Reflection.BindingFlags.Instance, null, containerDesigner, new object[] { result, issues });
Microsoft.DataTransformationServices.Design.ComponentModel.ComponentValidationEventArgs e = new Microsoft.DataTransformationServices.Design.ComponentModel.ComponentValidationEventArgs(containerDesigner.Component);
e.Issues = issues;
e.ValidationFailed = (issues != null) && (issues.Count > 0);
//doesn't work... not sure why... so the following is the equivalent of this one line:
//containerDesigner.GetType().InvokeMember("RaiseValidationEvent", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.FlattenHierarchy | System.Reflection.BindingFlags.Instance, null, containerDesigner, new object[] { e });
t.InvokeMember("PostTaskListValidationMessages", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.DeclaredOnly | System.Reflection.BindingFlags.Instance, null, containerDesigner, new object[] { e });
object componentValidationService = t.InvokeMember("componentValidationService", System.Reflection.BindingFlags.NonPublic | System.Reflection.BindingFlags.GetField | System.Reflection.BindingFlags.DeclaredOnly | System.Reflection.BindingFlags.Instance, null, containerDesigner, null);
componentValidationService.GetType().InvokeMember("OnValidated", System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.InvokeMethod | System.Reflection.BindingFlags.FlattenHierarchy | System.Reflection.BindingFlags.Instance, null, componentValidationService, new object[] { e });
}
}
}
protected void Button1_Click1(object sender, EventArgs e)
{
string logtime = "[ " + DateTime.Now.ToLocalTime().ToString() + " ]";
//get the yyyymmdd format date from canlendar
string rundate = Calendar1.SelectedDate.ToShortDateString();
string sdate = "";
string smonth = "";
string syear = "";
string newrundate = "";
if (rundate.Length == 9)
{
sdate = "0" + rundate.Substring(0, 1);
smonth = rundate.Substring(2, 2);
syear = rundate.Substring(5, 4);
newrundate = syear + smonth + sdate;
}
else
{
sdate = rundate.Substring(0, 2);
smonth = rundate.Substring(3, 2);
syear = rundate.Substring(6, 4);
newrundate = syear + smonth + sdate;
}
if (newrundate == "00010101")
{
// ClientScript.RegisterStartupScript(this.GetType(), "SystemMessage", "alert('You didn't selete a date & Please click the Calendar's date. Thanks!');", true);
TextBox3.Text = "[ALERT]You didn't selete a date & Please click the Calendar to select a runing date. Thanks!";
}
else
{
//execute the package for kyperatotechone
try
{
// Instantiate SSIS application object
Microsoft.SqlServer.Dts.Runtime.Application myApplication = new Microsoft.SqlServer.Dts.Runtime.Application();
string fileName = Path.Combine(Server.MapPath("~/Upload"), "JOB_KyperaToTechone_Inputdate.dtsx");
TextBox3.Text = TextBox3.Text + logtime + "Loading JOB_KyperaToTechone_Inputdate.dtsx from server...\r\n";
string fileName2 = fileName.Replace(@"\", @"\\");
Package myPackage = myApplication.LoadPackage(@fileName2, null);
TextBox3.Text = TextBox3.Text + logtime + "Loading package paramter - rundate...\r\n";
myPackage.Variables["RunDate"].Value = newrundate;
// Execute package
TextBox3.Text = TextBox3.Text + logtime + "Executing package...\r\n";
DTSExecResult myResult = myPackage.Execute();
// Show the execution result
TextBox3.Text = TextBox3.Text + logtime + "Package executed result: " + myResult.ToString() + " \r\n";
}
catch (Exception ee)
{
TextBox3.Text = logtime + "Package executed fail and Erros is: " + ee.Message + " \r\n";
}
}
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
Boolean fireAgain = false;
try
{
// Validate mandatory String properties.
DTSExecResult propertyValidationResult = this.ValidateProperties(ref componentEvents);
if (propertyValidationResult != DTSExecResult.Success)
{
return(propertyValidationResult);
}
// The package developer should know that files will be removed from the source.
if (this.FtpRemove)
{
componentEvents.FireInformation(0, TASK_NAME, REMOVE_ENABLED_MESSAGE, String.Empty, 0, ref fireAgain);
}
// Verify the connection.
using (Session winScpSession = this.EstablishSession())
{
componentEvents.FireInformation(0, TASK_NAME, SESSION_OPEN_MESSAGE, String.Empty, 0, ref fireAgain);
// Verify the remote resources.
OperationMode operation = (OperationMode)Enum.Parse(typeof(OperationMode), this.FtpOperationName);
switch (operation)
{
case OperationMode.PutFiles:
Boolean remoteDirectoryExists = winScpSession.FileExists(this.FtpRemotePath);
if (!remoteDirectoryExists)
{
componentEvents.FireInformation(0, TASK_NAME, String.Format(REMOTE_DIRECTORY_MISSING_MESSAGE_PATTERN, this.FtpRemotePath), String.Empty, 0, ref fireAgain);
}
break;
case OperationMode.GetFiles:
default:
Boolean remoteFileExists = winScpSession.FileExists(this.FtpRemotePath);
if (!remoteFileExists)
{
componentEvents.FireInformation(0, TASK_NAME, String.Format(REMOTE_FILES_MISSING_MESSAGE_PATTERN, this.FtpRemotePath), String.Empty, 0, ref fireAgain);
}
break;
}
}
return(DTSExecResult.Success);
}
catch (Exception exc)
{
String exceptionMessage = exc != null ? exc.Message : UNKNOWN_EXCEPTION_MESSAGE;
componentEvents.FireError(0, TASK_NAME, String.Format(EXCEPTION_MESSAGE_PATTERN, exceptionMessage), String.Empty, 0);
return(DTSExecResult.Failure);
}
}
private DTSExecResult ValidateProperties(ref IDTSComponentEvents componentEvents)
{
DTSExecResult result = DTSExecResult.Success;
if (String.IsNullOrEmpty(this.FtpProtocolName))
{
componentEvents.FireError(0, TASK_NAME, FtpProtocolName_MISSING_MESAGE, String.Empty, 0);
result = DTSExecResult.Failure;
}
if (String.IsNullOrEmpty(this.FtpHostName))
{
componentEvents.FireError(0, TASK_NAME, FtpHostName_MISSING_MESAGE, String.Empty, 0);
result = DTSExecResult.Failure;
}
if (String.IsNullOrEmpty(this.FtpUserName))
{
componentEvents.FireError(0, TASK_NAME, FtpUserName_MISSING_MESAGE, String.Empty, 0);
result = DTSExecResult.Failure;
}
if (String.IsNullOrEmpty(this.FtpPassword))
{
componentEvents.FireError(0, TASK_NAME, FtpPassword_MISSING_MESAGE, String.Empty, 0);
result = DTSExecResult.Failure;
}
if (String.IsNullOrEmpty(this.FtpSshHostKeyFingerprint))
{
componentEvents.FireError(0, TASK_NAME, FtpSshHostKeyFingerprint_MISSING_MESAGE, String.Empty, 0);
result = DTSExecResult.Failure;
}
if (String.IsNullOrEmpty(this.FtpOperationName))
{
componentEvents.FireError(0, TASK_NAME, FtpOperationName_MISSING_MESAGE, String.Empty, 0);
result = DTSExecResult.Failure;
}
if (String.IsNullOrEmpty(this.FtpLocalPath))
{
componentEvents.FireError(0, TASK_NAME, FtpLocalPath_MISSING_MESAGE, String.Empty, 0);
result = DTSExecResult.Failure;
}
if (String.IsNullOrEmpty(this.FtpRemotePath))
{
componentEvents.FireError(0, TASK_NAME, FtpRemotePath_MISSING_MESAGE, String.Empty, 0);
result = DTSExecResult.Failure;
}
return(result);
}
public bool WERPEQInsertTransDetails(int processId, string Packagepath, string configPath)
{
bool blResult = false;
try
{
Package werpEQTranPkg1 = App.LoadPackage(Packagepath, null);
werpEQTranPkg1.Variables["varProcessIdDataFlow"].Value = processId;
werpEQTranPkg1.Variables["varProcessIdUpdateTable"].Value = processId;
werpEQTranPkg1.ImportConfigurationFile(configPath);
DTSExecResult werpEQTranResult1 = werpEQTranPkg1.Execute();
if (werpEQTranResult1.ToString() == "Success")
{
//To update the least trans date for the process.
DataSet dsmindateval = new DataSet();
UploadsCommonDao uploadscommonDao = new UploadsCommonDao();
CustomerPortfolioBo customerportfoliobo = new CustomerPortfolioBo();
bool valupdated = true;
dsmindateval = uploadscommonDao.GetMinDateofUploadTrans(processId, "EQ");
if (dsmindateval.Tables[0].Rows.Count != 0)
{
DateTime LeastTransDate = DateTime.Parse(dsmindateval.Tables[0].Rows[0]["MinDate"].ToString());
int AdvId = int.Parse(dsmindateval.Tables[0].Rows[0]["AdviserId"].ToString());
valupdated = customerportfoliobo.UpdateAdviserDailyEODLogRevaluateForTransaction(AdvId, "EQ", LeastTransDate);
}
if (valupdated)
{
blResult = true;
}
}
}
catch (BaseApplicationException Ex)
{
throw Ex;
}
catch (Exception Ex)
{
BaseApplicationException exBase = new BaseApplicationException(Ex.Message, Ex);
NameValueCollection FunctionInfo = new NameValueCollection();
FunctionInfo.Add("Method", "WerpMFUploadsBo.cs:WerpMFInsertToInputProfile()");
object[] objects = new object[2];
objects[0] = processId;
objects[1] = Packagepath;
FunctionInfo = exBase.AddObject(FunctionInfo, objects);
exBase.AdditionalInformation = FunctionInfo;
ExceptionManager.Publish(exBase);
throw exBase;
}
return(blResult);
}
protected void Button1_Click1(object sender, EventArgs e)
{
string logtime = "[ " + DateTime.Now.ToLocalTime().ToString() + " ]";
try
{
logtxt.Text = "";
// Instantiate SSIS application object
Microsoft.SqlServer.Dts.Runtime.Application myApplication = new Microsoft.SqlServer.Dts.Runtime.Application();
string fileName = Path.Combine(Server.MapPath("~/Upload"), "PPMP_ImportClientLists.dtsx");
logtxt.Text = logtxt.Text + logtime + "Loading PPMP_ImportClientLists from AH.dtsx from server...\r\n";
string fileName2 = fileName.Replace(@"\", @"\\");
Package myPackage = myApplication.LoadPackage(fileName2, null);
// Execute package
logtxt.Text = logtxt.Text + logtime + "Executing package...\r\n";
DTSExecResult myResult = myPackage.Execute();
// Show the execution result
logtxt.Text = logtxt.Text + logtime + "Package executed result: " + myResult.ToString() + " \r\n";
//check records in table
int r_no = 0;
string connStr = ConfigurationManager.ConnectionStrings["DEX"].ConnectionString;
SqlConnection connsql = new SqlConnection(connStr);
try
{
if (connsql.State.ToString() == "Closed")
{
connsql.Open();
}
//check financil transaction data (most types)
string sqlstr = "SELECT count(*) from DEX_PPMP_clientlist ";
SqlCommand Cmd = new SqlCommand(sqlstr, connsql);
r_no = (int)Cmd.ExecuteScalar();
logtxt.Text = logtxt.Text + logtime + "Total records in table PPMP_ImportClientLists is :" + r_no.ToString() + " \r\n";
}
catch (Exception eee)
{
logtxt.Text = logtxt.Text + logtime + "#Query From Database ERROR# " + eee.Message + " .Please try again!";
}
finally
{
connsql.Close();
}
}
catch (Exception ee)
{
logtxt.Text = logtime + "Package executed fail and Erros is: " + ee.Message + " \r\n";
}
}
static void Main(string[] args)
{
Application app = new Application();
Package package = app.LoadPackage(@"C:\Learn\SSIS\Learn.SSIS\Learn.SSIS\CallFromCS.dtsx", null);
Variables vars = package.Variables;
vars["Country"].Value = "US";
vars["State"].Value = "California";
DTSExecResult result = package.Execute();
Console.WriteLine("Package Execution results: {0}", result.ToString());
}
//Fourth Phase: Checks and setting of flags in the staging
public bool WERPMFProcessDataInStagingTrans(int processId, string Packagepath, string configPath)
{
bool IsProcessComplete = false;
try
{
string query1 = "Update dbo.CustomerMFXtrnlTransactionStaging set " +
"dbo.CustomerMFXtrnlTransactionStaging.C_CustomerId=E.C_CustomerId, " +
"dbo.CustomerMFXtrnlTransactionStaging.CMFA_AccountId = D.CMFA_AccountId " +
"from CustomerMFXtrnlTransactionStaging A inner join CustomerMutualFundAccount D on " +
"A.[CMFXTS_FolioNum]=D.[CMFA_FolioNum] inner join CustomerPortfolio E on " +
"D.CP_PortfolioId=E.CP_PortfolioId inner join Customer F on E.C_CustomerId=F.C_CustomerId " +
"inner join AdviserRM G on F.AR_RMId=G.AR_RMId where G.A_AdviserId= A.A_AdviserId " +
"and A.ADUL_ProcessId = " + processId;
Package WERPMFTranPkg3 = App.LoadPackage(Packagepath, null);
WERPMFTranPkg3.Variables["varProcessID"].Value = processId;
WERPMFTranPkg3.Variables["varQueryFolioCheck"].Value = query1;
WERPMFTranPkg3.Variables["varChecksProcessId"].Value = processId;
WERPMFTranPkg3.Configurations[0].ConfigurationString = configPath;
DTSExecResult WERPMFTranResult3 = WERPMFTranPkg3.Execute();
if (WERPMFTranResult3.ToString() == "Success")
{
IsProcessComplete = true;
}
}
catch (BaseApplicationException Ex)
{
throw Ex;
}
catch (Exception Ex)
{
BaseApplicationException exBase = new BaseApplicationException(Ex.Message, Ex);
NameValueCollection FunctionInfo = new NameValueCollection();
FunctionInfo.Add("Method", "KarvyUploadBo.cs:WERPMFProcessDataInStagingTrans()");
object[] objects = new object[2];
objects[0] = processId;
objects[1] = Packagepath;
FunctionInfo = exBase.AddObject(FunctionInfo, objects);
exBase.AdditionalInformation = FunctionInfo;
ExceptionManager.Publish(exBase);
throw exBase;
}
return(IsProcessComplete);
}
public override DTSExecResult Validate(Connections connections, VariableDispenser variableDispenser, IDTSComponentEvents componentEvents, IDTSLogging log)
{
DTSExecResult execResult = base.Validate(connections, variableDispenser, componentEvents, log);
if (execResult == DTSExecResult.Success)
{
// Validate task properties
if (string.IsNullOrEmpty(displayText))
{
componentEvents.FireWarning(1, this.GetType().Name, "Value required for DisplayText", string.Empty, 0);
}
}
return(execResult);
}
public Microsoft.SqlServer.Dts.Runtime.DTSExecResult load_etl()
{
Microsoft.SqlServer.Dts.Runtime.DTSExecResult x = new DTSExecResult();;
Etl.Lokasi = Environment.CurrentDirectory.ToString() + "\\Resources\\Package.dtsx";
try
{
Etl.Pack = run_etl(Etl.Pack, Etl.Lokasi, Etl.App);
x = Etl.Pack.Execute();
}
catch (Exception ex)
{
MessageBox.Show("Terjadi kesalahan pada sistem dalam membaca paket, eksekusi dibatalkan", "General Error", MessageBoxButton.OK);
}
return(x);
}
public void ValidateReturnsSuccessIfServerNameSpecifiedTest()
{
MongoConnectionManager target = new MongoConnectionManager();
target.ServerName = "server123";
target.DatabaseName = "db123";
target.UserName = "******";
target.Password = "******";
IDTSInfoEvents infoEvents = null;
DTSExecResult expected = DTSExecResult.Success;
DTSExecResult actual = default(DTSExecResult);
actual = target.Validate(infoEvents);
Assert.AreEqual(expected, actual);
}
public void ValidateReturnsFailureIfPasswordEmptyTest()
{
MongoConnectionManager target = new MongoConnectionManager();
target.ServerName = "server123";
target.DatabaseName = "db123";
target.UserName = "******";
target.Password = "";
IDTSInfoEvents infoEvents = null;
DTSExecResult expected = DTSExecResult.Failure;
DTSExecResult actual = default(DTSExecResult);
actual = target.Validate(infoEvents);
Assert.AreEqual(expected, actual);
}
private void ExecutePackageWithEvents(Package pkg, CancellationToken token)
{
try
{
var runPkg =
task.Task.Factory.StartNew(() =>
{
// Throw an exception if we get an error
_logger.Information("Executing DE Package...");
SSISEvents ev = new SSISEvents(_logger);
DTSExecResult rc = pkg.Execute(null, null, ev, null, null);
if (rc != DTSExecResult.Success)
{
_logger.Error("Error: the DE failed to complete successfully {ErrorCode}.", 50000);
//StringBuilder dtserrors = new StringBuilder();
foreach (DtsError error in pkg.Errors)
{
_logger.Error("Error: {Desc}, {ErrorCode}", error.Description, error.ErrorCode);
//dtserrors.AppendLine(error.Description);
}
throw new UnexpectedSsisException("SSIS Package execution failed");
//return false;
}
});
runPkg.Wait(token);
}
catch (Exception ex)
{
pkg.Dispose();
_logger.Error(ex, "Package was cancelled: {PackageId}, {Name}", pkg.ID, pkg.Name);
//var app = new Application();
//var list = app.GetRunningPackages(".");
//foreach (var p in list)
//{
// if (p.PackageID == Guid.Parse(pkg.ID))
// {
// p.Stop();
// pkg.Dispose();
// break;
// }
//}
throw ex;
}
}
public Microsoft.SqlServer.Dts.Runtime.DTSExecResult load_etl()
{
Microsoft.SqlServer.Dts.Runtime.DTSExecResult x = new DTSExecResult(); ;
Etl.Lokasi = Environment.CurrentDirectory.ToString() + "\\Resources\\Package.dtsx";
try
{
Etl.Pack = run_etl(Etl.Pack, Etl.Lokasi, Etl.App);
x = Etl.Pack.Execute();
}
catch (Exception ex)
{
MessageBox.Show("Terjadi kesalahan pada sistem dalam membaca paket, eksekusi dibatalkan", "General Error", MessageBoxButton.OK);
}
return x;
}
public bool ExecutePackage(out String errors)
{
DTSExecResult result = package.Execute();
StringBuilder sb = new StringBuilder();
errors = String.Empty;
if (result != DTSExecResult.Success)
{
foreach (DtsError err in package.Errors)
{
sb.AppendLine(err.Description);
}
errors = sb.ToString();
return(false);
}
return(true);
}
private void Etlbtn_Click(object sender, RoutedEventArgs e)
{
DTSExecResult x = new DTSExecResult();
Etlbtn.Dispatcher.BeginInvoke(new Action(() => Etlbtn.Visibility = System.Windows.Visibility.Hidden));
pbLoading.Dispatcher.BeginInvoke(new Action(() => pbLoading.Visibility = System.Windows.Visibility.Visible));
proseslbl.Dispatcher.BeginInvoke(new Action(() => proseslbl.Visibility = System.Windows.Visibility.Visible));
var bw = new BackgroundWorker();
App.ETLViewModel.Loading = true;
bw.DoWork += (Sender, args) =>
{
x = App.ETLViewModel.load_etl();
};
bw.RunWorkerCompleted += (Sender, args) =>
{
App.ETLViewModel.Loading = false;
if (x == DTSExecResult.Success)
{
main cont = new main();
cont.Show();
this.Close();
}
else
{
string err = string.Empty;
foreach (var p in App.ETLViewModel.Etl.Pack.Errors)
{
err += p.Description.ToString();
}
MessageBox.Show(err);
MessageBox.Show("Terjadi kesalahan pada sistem. proses ekseskusi data dibatalkan. silahkan di coba lagi", "General Error", MessageBoxButton.OK);
Etlbtn.Dispatcher.BeginInvoke(new Action(() => Etlbtn.Visibility = System.Windows.Visibility.Visible));
pbLoading.Dispatcher.BeginInvoke(new Action(() => pbLoading.Visibility = System.Windows.Visibility.Hidden));
proseslbl.Dispatcher.BeginInvoke(new Action(() => proseslbl.Visibility = System.Windows.Visibility.Hidden));
}
};
bw.RunWorkerAsync();
}
/// <summary>
/// Adds a precedence constraint to a package - the connectTo parm determines if we are connecting the targetTaskComponent
/// to or from the sourceExecutable.
/// </summary>
/// <param name="container"></param>
/// <param name="sourceExecutable"></param>
/// <param name="targetExecutable"></param>
/// <param name="desiredResult"></param>
public static void AddPrecedenceConstraint(this Package container, Executable sourceExecutable, Executable targetExecutable, DTSExecResult desiredResult)
{
var precedenceConstraint = container.PrecedenceConstraints.Add(sourceExecutable, targetExecutable);
precedenceConstraint.Value = desiredResult;
}
