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);
}
}
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);
}
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 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;
}
}
private void Initialize()
{
CensusApplication = new Application();
bool packageInitialized = InitializePackage();
Console.WriteLine(packageInitialized
? "Package Initialized successfully"
: "The package has failed to initialize!");
if (!packageInitialized)
{
throw new Exception("Unable to initialize project! Project necessary to continue!");
}
InitializeTasks();
//bool projectInitialized = InitializeProject();
//Console.WriteLine(projectInitialized
// ? "Project Initialized successfully"
// : "The project has failed to initialize!");
//if (!projectInitialized)
// throw new Exception("Unable to initialize project!");
Save();
}
public void Execute_Package(string pkgLocation, string filepath)
{
Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
Microsoft.SqlServer.Dts.Runtime.DTSExecResult pkgResults;
Variables vars;
app = new Microsoft.SqlServer.Dts.Runtime.Application();
try
{
pkg = app.LoadPackage(pkgLocation, null);
vars = pkg.Variables;
vars["filepath"].Value = filepath;
pkgResults = pkg.Execute(null, vars, null, null, null);
if (pkgResults == DTSExecResult.Success)
{
System.Windows.Forms.MessageBox.Show("Package ran successfully");
}
else
{
System.Windows.Forms.MessageBox.Show("Package failed");
}
}catch (Exception ex) {
System.Windows.Forms.MessageBox.Show(ex.Message.ToString());
}
}
/// <summary>
/// If the package designer is already open, then use the package object on it which may have in-memory modifications.
/// If the package designer is not open, then just load the package from disk.
/// </summary>
/// <param name="pi"></param>
/// <returns></returns>
private PackageProperties GetPackageProperties(ProjectItem pi)
{
Package package;
bool bIsOpen = pi.get_IsOpen(BIDSViewKinds.Designer);
if (bIsOpen)
{
Window w = pi.Open(BIDSViewKinds.Designer); //opens the designer
w.Activate();
IDesignerHost designer = w.Object as IDesignerHost;
if (designer == null)
{
return(null);
}
EditorWindow win = (EditorWindow)designer.GetService(typeof(Microsoft.DataWarehouse.ComponentModel.IComponentNavigator));
package = win.PropertiesLinkComponent as Package;
}
else
{
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
package = app.LoadPackage(pi.get_FileNames(0), null);
}
if (package == null)
{
return(null);
}
PackageProperties props = new PackageProperties(package);
props.DatabaseName = this.DatabaseName;
props.PackagePathPrefix = this.PackagePathPrefix;
return(props);
}
private void button2_Click(object sender, EventArgs e)
{
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
//string pkgLocation;
// DTSExecResult pkgResults;
string pkgLocation = @"\\mverp\f$\Integration\SupplierIntegration\SpendTool.dtsx";
Package pkg = app.LoadPackage(pkgLocation, null);
Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = pkg.Execute();
if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
{
string err = "";
foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in pkg.Errors)
{
string error = local_DtsError.Description.ToString();
err = err + error;
}
}
else if (results == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success)
{
MessageBox.Show("Package Executed Successfully....");
}
}
//========== END TAMPIL DATA DIMENSI DAN FAKTA ==========
//========== ETL UPDATE DATA MART ==========
private void btnUpdateETL_Click(object sender, EventArgs e)
{
try
{
splashScreenManager1.ShowWaitForm();
Cursor.Current = Cursors.WaitCursor;
string lokasi = Environment.CurrentDirectory.ToString() + "\\Resources\\Package.dtsx";
DTSExecResult pkgResults_Sql;
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Microsoft.SqlServer.Dts.Runtime.Package pkg = new Microsoft.SqlServer.Dts.Runtime.Package();
pkg = app.LoadPackage(lokasi, null);
pkgResults_Sql = pkg.Execute();
splashScreenManager1.CloseWaitForm();
MessageBox.Show("Data berhasil di-Update.", "Pesan", MessageBoxButtons.OK, MessageBoxIcon.Information);
TampilData();
}
catch (System.Exception f)
{
MessageBox.Show(f.Message);
}
finally
{
Cursor.Current = Cursors.Default;
}
}
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;
}
}
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 Package(AST.Task.AstPackageNode astNode)
: base(astNode)
{
_DTSApplication = new DTS.Application();
DtsPackage = new DTS.Package();
DtsPackage.Name = StringManipulation.NameCleaner(Name);
PackageType = String.IsNullOrEmpty(astNode.PackageType) ? "ETL" : astNode.PackageType;
PackageFolder = astNode.PackageFolder;
PackagePath = astNode.PackagePath;
PackageProtectionLevel = astNode.ProtectionLevel;
PackagePassword = astNode.PackagePassword;
// vsabella: We thought about adding this in the Lowering phase.
// The reason this was not placed in Lowering is that this variable must be available
// before any other lowering can take place. Additionally i needed a single place where the
// variable name could remain constant and other lowering phase engines could refer to it.
// PreEmit
PackageRootVariable =
new Variable(PackagePathRootVariableName)
{
InheritFromPackageParentConfigurationString = "User::" + PackagePathRootVariableName,
ValueString = PathManager.TargetPath,
TypeCode = TypeCode.String
};
Children.Add(PackageRootVariable);
}
protected override void OnStart(string[] args)
{
_dataManager = new DataManager();
_app = new dts.Application();
var data = args[0];
Log.WriteErrorLog("Processing extraction for " + data);
//var indexOfPipe = data.IndexOf("|");
//_serviceName = data.Substring(0, indexOfPipe);
//Log.WriteErrorLog("Processing extraction for " + _serviceName);
_connectionString = _dataManager.GetDataConnection();
//_connectionString = data.Substring(indexOfPipe + 1, data.Length - (indexOfPipe + 1));
_serviceName = data;
//Log.WriteErrorLog("Processing extraction for " + _serviceName);
_timer = new System.Timers.Timer();
_timer.Interval = 25000;
_timer.Elapsed += _timer_Elapsed;
_timer.Enabled = true;
Log.WriteErrorLog("Process service for " + _serviceName + " now started");
RunProcess();
}
public Package(AST.Task.AstPackageNode astNode)
: base(astNode)
{
_DTSApplication = new DTS.Application();
DtsPackage = new DTS.Package();
DtsPackage.Name = StringManipulation.NameCleaner(Name);
PackageType = String.IsNullOrEmpty(astNode.PackageType) ? "ETL" : astNode.PackageType;
PackageFolder = astNode.PackageFolder;
PackagePath = astNode.PackagePath;
PackageProtectionLevel = astNode.ProtectionLevel;
PackagePassword = astNode.PackagePassword;
// vsabella: We thought about adding this in the Lowering phase.
// The reason this was not placed in Lowering is that this variable must be available
// before any other lowering can take place. Additionally i needed a single place where the
// variable name could remain constant and other lowering phase engines could refer to it.
// PreEmit
PackageRootVariable =
new Variable(PackagePathRootVariableName)
{
InheritFromPackageParentConfigurationString = "User::" + PackagePathRootVariableName,
ValueString = PathManager.TargetPath,
TypeCode = TypeCode.String
};
Children.Add(PackageRootVariable);
}
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";
}
}
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;
}
static void Main(string[] args)
{
string pkgLocation = @"D:\1542266_GetName\1542266_GetName\Package.dtsx";
Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;
Variables vars;
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(pkgLocation, null);
vars = pkg.Variables;
vars["User::import_cow"].Value = @"C:\Users\ng.phuocloc\Downloads\chip2.txt";
pkgResults = pkg.Execute(null, vars, null, null, null);
if (pkgResults == DTSExecResult.Success)
{
Console.WriteLine("Import successfully");
}
else
{
Console.WriteLine("Import failed");
}
}
public void CreatePackage()
{
App = new SSIS.Application();
SSISObject = new SSIS.Package();
SSISObject.ProtectionLevel = ProtectionLevel;
base.SetSSISObjectBaseProperties(SSISObject);
base.SetBaseProperties(SSISObject, SSISObject);
}
private void btnImport_Click(object sender, EventArgs e)
{
String importFilePath = txtFilePath.Text;
String SSISPackageFilePath = txtSSISPackageFilePath.Text;
if (importFilePath == "")
{
MessageBox.Show("Please enter the data import file path!");
return;
}
if (SSISPackageFilePath == "")
{
MessageBox.Show("Please enter the SSIS package file path!");
return;
}
// Check the data import file exists?
if (!File.Exists(importFilePath))
{
MessageBox.Show("Data import file is not exists. Please check the file path!");
return;
}
// Check the SSIS package file exists?
if (!File.Exists(SSISPackageFilePath))
{
MessageBox.Show("SSIS package file is not exists. Please check the file path!");
return;
}
// Call command line
string pkgLocation;
Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;
pkgLocation = SSISPackageFilePath;
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(pkgLocation, null);
Variables appVars = pkg.Variables;
appVars["User::FileTransactionNamePath"].Value = importFilePath;
pkgResults = pkg.Execute(null, appVars, null, null, null);
if (pkgResults == DTSExecResult.Success)
{
MessageBox.Show("Package ran successfully");
}
else
{
MessageBox.Show("Package failed");
}
//Console.WriteLine(pkgResults.ToString());
//Console.ReadKey();
}
public SsisPackage(Package logicalPackage, SSISEmitterContext context) : base(logicalPackage, context)
{
_DTSApplication = new DTS.Application();
_DTSPackage = new DTS.Package();
_DTSPackage.Name = Name.Replace(".", "_").Replace("[", " ").Replace("]", " ");
_projectManager = new SsisProject(this);
CurrentPackage = this;
}
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";
}
}
}
private void button2_Click(object sender, EventArgs e)
{
Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(_package, null);
Variables myVars = pkg.Variables;
List <string> thanhCong = new List <string>();
List <string> thatbai = new List <string>();
foreach (var file in _listFiles)
{
try
{
myVars["TransactionFilePath"].Value = file;
pkgResults = pkg.Execute(null, myVars, null, null, null);
if (pkgResults == DTSExecResult.Failure)
{
thatbai.Add(file);
}
else
{
thanhCong.Add(file);
}
}
catch
{
thatbai.Add(file);
}
}
string msg = string.Empty;
if (thanhCong.Count > 0)
{
msg += "- Import thanh cong:\n";
foreach (var file in thanhCong)
{
msg += file + "\n";
}
}
if (thatbai.Count > 0)
{
msg += "- Import that bai:\n";
foreach (var file in thatbai)
{
msg += file + "\n";
}
}
MessageBox.Show(msg);
textBox1.Text = string.Empty;
}
/// <summary>
/// Initialize Maime with Options
/// </summary>
/// <param name="options">Options used for reparation</param>
public static void Init(Options.Options options)
{
Logger.Common("Maime initializing");
_options = options;
// If debugger attached, then locate folders in solution folder.
if (System.Diagnostics.Debugger.IsAttached)
{
_projectPathFolder = Path.GetDirectoryName(Path.GetDirectoryName(Directory.GetCurrentDirectory()));
_dataFolder = Path.GetFullPath(Path.Combine(_projectPathFolder, "..\\", "Data"));
}
else // Else locate in same folder as .exe file
{
_projectPathFolder = AppDomain.CurrentDomain.BaseDirectory;
_dataFolder = Path.GetFullPath(Path.Combine(_projectPathFolder, "Data"));
}
Logger.Common($"Settings stored at {_projectPathFolder}");
Logger.Common($"Data folder location {_dataFolder}");
// ======== Settings ======== //
Logger.Common("Loading settings");
SettingsStore.Init(
new JsonSettingsProvider(_dataFolder),
new JsonEDSProvider()
);
Logger.Common("Settings loaded");
// ======== Metadata store ======== //
Logger.Common("Initializing metadata store");
MetaDataStore.Init(new JSONSnapshotProvider());
Logger.Common("Metadata store initialized");
_application = new Application();
// ======== Fetch Changes ======== //
Logger.Common("Fetching latest metadata changes");
_latestChanges = MetaDataStore.GetLatestChanges(SettingsStore.EDSSettings);
if (_latestChanges.Count == 0)
{
Logger.Error("No database(s) or change(s) found. Please create a snapshot and rerun the program.");
_databaseMetaChange = null;
return;
}
_databaseMetaChange = _latestChanges.Values.First();
Logger.Common("Finished fetching metadata changes");
Logger.Common("Maime initialized");
}
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";
}
}
public void ExtractPackageData(string packageFile) {
Microsoft.SqlServer.Dts.Runtime.Application sqlApp = new Microsoft.SqlServer.Dts.Runtime.Application();
Executables executables = sqlApp.LoadPackage(packageFile, null).Executables;
Stack<Executable> executablesStack = new Stack<Executable>(); // temporary storage for nested containers
foreach(Executable executable in executables) {
ContainerType containerType;
Executable exe = executable;
do {
if(executablesStack.Count != 0) {
exe = executablesStack.Pop();
}
if(Enum.TryParse(exe.GetType().Name, out containerType)) {
switch(containerType) {
case ContainerType.ForEachLoop:
var forEachLoop = exe as ForEachLoop;
var keyArray = forEachLoop.Executables.Cast<dynamic>().ToDictionary(x => x.Name).Keys.ToArray();
ProcessMultipleTasksContainer(forEachLoop, keyArray, executablesStack, packageFile);
break;
case ContainerType.ForLoop:
var forLoop = exe as ForLoop;
keyArray = forLoop.Executables.Cast<dynamic>().ToDictionary(x => x.Name).Keys.ToArray();
ProcessMultipleTasksContainer(forLoop, keyArray, executablesStack, packageFile);
break;
case ContainerType.Sequence:
var sequence = exe as Sequence;
keyArray = sequence.Executables.Cast<dynamic>().ToDictionary(x => x.Name).Keys.ToArray();
ProcessMultipleTasksContainer(sequence, keyArray, executablesStack, packageFile);
break;
case ContainerType.TaskHost:
ProcessSingleTaskContainer(exe, packageFile);
break;
}
}
} while(executablesStack.Count != 0);
}
if(_searchResults.Count > 0) {
// Reference: "Merge two dictionaries and remove duplicate keys and sort by the value" (http://stackoverflow.com/questions/18123538/merge-two-dictionaries-and-remove-duplicate-keys-and-sort-by-the-value)
_searchResults = _searchResults.Concat(_allResults.Where(x => !_searchResults.ContainsKey(x.Key))).ToDictionary(y => y.Key, y => y.Value);
} else {
// Create a deep copy
foreach(KeyValuePair<string, List<string>> keyValue in _allResults) {
_searchResults.Add(keyValue.Key, keyValue.Value);
}
}
}
/// <summary>
/// Metodo que se encarga de realizar la Ejecución del Paquete
/// </summary>
/// <param name="Ruta"></param>
/// <returns></returns>
public bool ProcesoEtl(string Ruta)
{
string RutaDts;
Package Paquete;
Microsoft.SqlServer.Dts.Runtime.Application Aplicacion;
DTSExecResult Ejecucion;
try
{
RutaDts = Ruta;
Aplicacion = new Microsoft.SqlServer.Dts.Runtime.Application();
Paquete = Aplicacion.LoadPackage(RutaDts, null);
//Parámetros del ETL (Si tuviera)
//Parameters par = Paquete.Parameters;
//par["dFecInicio"].Value = dFechaIni;
//par["dFecFin"].Value = dFechaFin;
Ejecucion = Paquete.Execute();
//Validando si el proceso es correcto
if (Ejecucion == DTSExecResult.Success)
{
return(true);
}
else
{
foreach (DtsError error in Paquete.Errors)
{
Error = String.Concat(Error, " ", error.Description.ToString());
}
CorreoEnvio oCorreo = new CorreoEnvio();
oCorreo.EnvioCorreo(CorreoEnviar, CorreoEnviarCc, Error, "Error Proceso ETL", 1);
return(false);
}
}
catch (Exception ex)
{
FTextos ofTextos = new FTextos();
string Usuarios = ofTextos.fGetUsuario();
string Fecha = DateTime.Now.ToString();
string Asunto;
Asunto = "ETL - Error [Helpdesk] [" + Usuarios + "] [" + Fecha + "]";
CorreoEnvio oCorreo = new CorreoEnvio();
oCorreo.EnvioCorreo(CorreoEnviar, CorreoEnviarCc, ex.ToString(), "Error Proceso ETL", 1);
throw;
}
}
public VulcanPackage(string packageName, string packageType, VulcanConfig vulcanConfig, TemplateManager templateManager, XPathNavigator packageNavigator)
{
this._packageName = packageName;
this._projectSubpath = GetSubpathFromPackageType(packageType);
this._vulcanConfig = vulcanConfig;
this._templateManager = templateManager;
this._projectManager = new ProjectManager(packageName);
_DTSApplication = new DTS.Application();
_package = new DTS.Package();
this._package.Name = this._packageName;
this._packageNavigator = packageNavigator;
this.UnSave();
}
public VulcanPackage(string packageName, string packageType, VulcanConfig vulcanConfig, TemplateManager templateManager, XPathNavigator packageNavigator)
{
this._packageName = packageName;
this._projectSubpath = GetSubpathFromPackageType(packageType);
this._vulcanConfig = vulcanConfig;
this._templateManager = templateManager;
this._projectManager = new ProjectManager(packageName);
_DTSApplication = new DTS.Application();
_package = new DTS.Package();
this._package.Name = this._packageName;
this._packageNavigator = packageNavigator;
this.UnSave();
}
public void ExecutePackage()
{
string strPackagePath = @"C:\jinshan\SSISWork\SSIS_World\ISExtensions\ISExtensions\winForm.dtsx";
SSIS_Runtime.Application oApp = new SSIS_Runtime.Application();
SSIS_Runtime.Package oPackage = new SSIS_Runtime.Package();
oPackage = oApp.LoadPackage(strPackagePath, null);
oPackage.Variables["Myvar"].Value = textBox1.Text.Trim();
//oPackage.Variables["MyParameter"].Value = textBox2.Text.Trim();
oPackage.Execute();
MessageBox.Show("Package Executed!");
}
private void ImportFile(params FileInfo[] files)
{
var app = new Microsoft.SqlServer.Dts.Runtime.Application();
var pkg = app.LoadPackage(PackageFilePath, null);
Variables myVars = pkg.Variables;
if (files == null)
{
return;
}
foreach (var file in files)
{
try
{
Log.Info($"Importing file \"{file.FullName}\"");
myVars["ConnectionString"].Value = _connectionString;
myVars["TransactionFilePath"].Value = file.FullName;
myVars["TransactionTable"].Value = _transactionTable;
var pkgResults = pkg.Execute(null, myVars, null, null, null);
if (pkgResults == DTSExecResult.Failure)
{
var newFileName = "Failed" + DateTime.Now.ToString("_dd_MM_yyyy_HH_mm_ss_") + file.Name;
Log.Error($"Import \"{file.Name}\" failed. Move to \"{ErrorFolderPath}\". Rename file to \"{newFileName}\"\n\n");
File.Move(file.FullName, ErrorFolderPath + "\\" + newFileName);
}
else
{
var newFileName = "Completed" + DateTime.Now.ToString("_dd_MM_yyyy_HH_mm_ss_") + file.Name;
Log.Info($"Import \"{file.Name}\" successfully. Move to \"{StoreFolderPath}\". Rename file to \"{newFileName}\"\n\n");
File.Move(file.FullName, StoreFolderPath + "\\" + newFileName);
}
}
catch (Exception ex)
{
Log.Error(ex.ToString());
var newFileName = "Exception" + DateTime.Now.ToString("_dd_MM_yyyy_HH_mm_ss_") + file.Name;
Log.Error($"Import \"{file.Name}\" failed. Move to \"{ErrorFolderPath}\". Rename file to \"{newFileName}\"\n\n");
File.Move(file.FullName, ErrorFolderPath + "\\" + newFileName);
}
}
}
/// <summary>
/// Metodo para lanzar las ETL
/// </summary>
/// <param name="pkgLocation"></param>
public void lanzarETL(string pkgLocation, ConfigGlassWindow parent)
{
Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;
parentWin = parent;
eventListener = new MyEventListener(parentWin);
//pkgLocation =
// @"C:\Program Files\Microsoft SQL Server\100\Samples\Integration Services" +
// @"\Package Samples\CalculatedColumns Sample\CalculatedColumns\CalculatedColumns.dtsx";
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(pkgLocation, eventListener);
//pkg.Variables.Add("strNombreServidorLocal", false, "", "ASINE1159\\SQLEXPRESS");
//pkg.Variables["strNombreServidorLocal"].Value = "ASINE1159\\SQLEXPRESS";
pkgResults = pkg.Execute(null, pkg.Variables, eventListener, null, null);
//errores.Add(pkgResults.ToString());
//Console.WriteLine(pkgResults.ToString());
//Console.ReadKey();
}
private void btnBuildDaily_Click(object sender, EventArgs e)
{
try
{
string pkgLocation;
runtime.Package pkg;
runtime.Application app;
runtime.DTSExecResult pkgResults;
MyEventListener eventListener = new MyEventListener();
pkgLocation = @"D:\SSIS\SSIS_Bank\Integration Services Project1\DB_TransToDaily.dtsx";;
app = new runtime.Application();
pkg = app.LoadPackage(pkgLocation, eventListener);
pkgResults = pkg.Execute(null, null, eventListener, null, null);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
public static int EjecutaPaquete(string NombrePaquete, string RutaHojaExcel)
{
try
{
DTSExecResult r;
Microsoft.SqlServer.Dts.Runtime.Application aplicacion = new Microsoft.SqlServer.Dts.Runtime.Application();
Package paquete = aplicacion.LoadPackage(NombrePaquete, null);
MessageBox.Show(paquete.Variables["RutaHojaExcel"].Value.ToString());
paquete.Variables["RutaHojaExcel"].Value = RutaHojaExcel;
MessageBox.Show(paquete.Variables["RutaHojaExcel"].Value.ToString());
try
{
r = paquete.Execute();
if(r == DTSExecResult.Failure || r == DTSExecResult.Canceled) {
return 1;
}
else
{
return 0;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
return -1;
}
finally
{
paquete = null;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
throw;
}
}
private void btnYearly_Click(object sender, EventArgs e)
{
try
{
string pkgLocation;
runtime.Package pkg;
runtime.Application app;
runtime.DTSExecResult pkgResults;
MyEventListener eventListener = new MyEventListener();
pkgLocation = @"D:\SSIS\SSIS_Bank\Integration Services Project1\DB_QuarterlyToYearly.dtsx";
app = new runtime.Application();
pkg = app.LoadPackage(pkgLocation, eventListener);
pkg.Variables["User::nam"].Value = DateTime.Today.Year;
pkgResults = pkg.Execute(null, null, eventListener, null, null);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
protected override void OnStart(string[] args)
{
try
{
_dataManager = new DataManager();
_app = new dts.Application();
var data = args[0];
Log.WriteErrorLog("Processing extraction for " + data);
_dataManager = new DataManager();
// List<string> _dbaseStrings = null;
_connectionString = _dataManager.GetDataConnection();
//_connectionString = "Data Source=10.0.0.96\\FINTRAKSQL2014;Initial Catalog=FintrakDB;User =sa;Password=sqluser10$;Integrated Security=False"; //_dataManager.GetDataConnections();
//Log.WriteErrorLog("Processing extraction for " + _connectionString);
// Log.WriteErrorLog("Processing extraction for test " + "Now Here");
//var indexOfPipe = data.IndexOf("|");
//_serviceName = data.Substring(0,indexOfPipe);
_serviceName = data;
Log.WriteErrorLog("Processing extraction for Passed " + _serviceName);
// _connectionString = _dbaseStrings; //data.Substring(indexOfPipe + 1, data.Length - (indexOfPipe + 1));
_timer = new System.Timers.Timer();
_timer.Interval = 25000;
_timer.Elapsed += _timer_Elapsed;
_timer.Enabled = true;
Log.WriteErrorLog("Extraction service for " + _serviceName + " started");
RunExtraction();
}
catch (Exception ex)
{
Log.WriteErrorLog(ex);
}
}
public void execute_load_SSIS_package()
{
GridView2.Visible = false;
Label9.Visible = false;
string logtime = "[ " + DateTime.Now.ToLocalTime().ToString() + " ]";
//TextBox3.Text = "";
if (DropDownList1.SelectedItem.Text == "--Please select a pay period--")
{
Response.Write("<script LANGUAGE='JavaScript' >alert('Please select a Pay Period!')</script>");
DropDownList1.Focus();
}
else
{
try
{
string fileserverfolder = ConfigurationManager.AppSettings["CIMtoT1"].ToString();
// Instantiate SSIS application object
Microsoft.SqlServer.Dts.Runtime.Application myApplication = new Microsoft.SqlServer.Dts.Runtime.Application();
string fileName3 = Path.Combine(Server.MapPath("~/Upload"), "LoadCIMTimesheetforallfiles_for T1.dtsx");
// TextBox3.Text = TextBox3.Text+logtime + "Loading import package from file system...\r\n";
//string SSISLocation = "C:\\SSIS\\KyperaToTechone_TF.dtsx";
Package myPackage = myApplication.LoadPackage(@fileName3, null);
TextBox3.Text = TextBox3.Text + logtime + "Starting loading CIM timesheet files into data warehouse table...\r\n";
ShowProcessBar();
DTSExecResult myResult = myPackage.Execute();
// Show the execution result
TextBox3.Text = TextBox3.Text + logtime + "Loading result: " + myResult.ToString() + " \r\n";
System.Threading.Thread.Sleep(2000);
ExitProcessBar();
}
catch (Exception ee)
{
TextBox3.Text = logtime + "Loading fail and Erros is: " + ee.Message + " \r\n";
}
}
}
private void ImportFile(params FileInfo[] files)
{
Package pkg;
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(_packagePath, null);
Variables myVars = pkg.Variables;
if (files == null)
{
return;
}
foreach (var file in files)
{
try
{
LogInfo("Tiến hành import file <" + file.Name + ">");
myVars["TransactionFilePath"].Value = file.FullName;
pkgResults = pkg.Execute(null, myVars, null, null, null);
if (pkgResults == DTSExecResult.Failure)
{
LogError("Import <" + file.Name + "> thất bại. Chuyển file <" + file.Name + "> sang thư mục \"" + _errorFilesPath + "\"" + Environment.NewLine + Environment.NewLine);
File.Move(file.FullName, _errorFilesPath + "\\" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss_") + file.Name);
}
else
{
LogInfo("Import <" + file.Name + "> thành công. Chuyển file <" + file.Name + "> sang thư mục \"" + _recycleBinPath + "\"" + Environment.NewLine + Environment.NewLine);
File.Move(file.FullName, _recycleBinPath + "\\" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss_") + file.Name);
}
}
catch (Exception ex)
{
LogError(ex.ToString());
LogError("Import <" + file.Name + "> thất bại. Chuyển file <" + file.Name + "> sang thư mục \"" + _errorFilesPath + "\"" + Environment.NewLine + Environment.NewLine);
File.Move(file.FullName, _errorFilesPath + "\\" + DateTime.Now.ToString("dd_MM_yyyy_HH_mm_ss_") + file.Name);
}
}
}
public SsisPackage(Package logicalPackage, SSISEmitterContext context) : base (logicalPackage, context)
{
_DTSApplication = new DTS.Application();
_DTSPackage = new DTS.Package();
_DTSPackage.Name = Name.Replace(".", "_").Replace("[", " ").Replace("]", " ");
_projectManager = new SsisProject(this);
CurrentPackage = this;
}
public void Insert_DataFlow()
{
if(Package_File==string.Empty)
{
throw new System.ArgumentException("Empty Package Name", "Package_File");
}
if (Source_Conection_Name == string.Empty)
{
throw new System.ArgumentException("Empty Source Conection Name", "Source_Conection_Name");
}
if (Destination_Conection_Name == string.Empty)
{
throw new System.ArgumentException("Empty Destination Conection Name", "Destination_Conection_Name");
}
if (Source_Table_Name == string.Empty && Source_Query==string.Empty ) //Mast fill source table or source Query
{
throw new System.ArgumentException("Empty Source Table Name and Source Query", "Source_Table_Name");
}
Source_Query = Source_Query != string.Empty ? Source_Query : "select * from " + Source_Table_Name;
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Package pkg = app.LoadPackage(Package_File, null);
ConnectionManager S_con = GetConnectionByName(pkg, Source_Conection_Name);
if (S_con == null )
{
throw new System.ArgumentException("Can not find source connection by name in Package", "Source_Conection_Name");
}
ConnectionManager D_con = GetConnectionByName(pkg, Destination_Conection_Name);
if (D_con == null)
{
throw new System.ArgumentException("Can not find destination connection by name in Package", "Destination_Conection_Name");
}
TaskHost DataFlowTaskHost = null;
if (Package_Container != string.Empty)
{
DataFlowTaskHost = getNewDataFlowTaskHost(pkg.Executables, Package_Container);
if (DataFlowTaskHost == null)
{
throw new System.ArgumentException("Can not find Sequence Container ", "Package_Container");
}
}
else
{
Executable DFT = pkg.Executables.Add("STOCK:PipelineTask");
DataFlowTaskHost = DFT as TaskHost;
}
//
string s = string.IsNullOrEmpty(Source_Table_Name) ? Destination_Table_Name.Replace("[","_") : Source_Table_Name.Replace("[", "_");
s=s.Replace("]", "_");
s=s.Replace(".", "_");
DataFlowTaskHost.Name = "DTF Load from " + s;
MainPipe DataFlowTask = DataFlowTaskHost.InnerObject as MainPipe;
// / \ : [ ] . =
//Variables 0
string d = Destination_Table_Name.Replace("[", "");
d = d.Replace("]", "");
Variable Var_DestTable = DataFlowTaskHost.Variables.Add("Destination_table", false, "User", "");
Var_DestTable.EvaluateAsExpression = true;
Var_DestTable.Expression = "\"[loading].["+(Destination_Table_Name==string.Empty?Source_Table_Name:Destination_Table_Name)+"_\"+ (DT_STR, 10, 1251) @[User::system_source_id]+\"]\"";
Variable myVar2 = DataFlowTaskHost.Variables.Add("SourceSQL", false, "User", Source_Query );
IDTSComponentMetaData100 source = DataFlowTask.ComponentMetaDataCollection.New();
source.Name = "OLE DB Source";
source.ComponentClassID = "DTSAdapter.OleDbSource";
CManagedComponentWrapper source_instance = source.Instantiate();
// Initialize the component
source_instance.ProvideComponentProperties();
// Specify the connection manager.
if (source.RuntimeConnectionCollection.Count > 0)
{
source.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(S_con);
source.RuntimeConnectionCollection[0].ConnectionManagerID =S_con.ID;
}
// Set the custom properties.
source_instance.SetComponentProperty("AccessMode", 2);
source_instance.SetComponentProperty("SqlCommand",Source_Query);
if (Source_Query.IndexOf('?') > -1)
{
string ParamMap = string.Empty ;
string cd = string.Empty; //replace string.Empty with name of variable
foreach (Variable var_day in pkg.Variables )
{
if(var_day.Name==cd)
{
ParamMap = @"""Parameter0:Input""," + var_day.ID + ";";
}
}
if(!string.IsNullOrEmpty(ParamMap))
{
source_instance.SetComponentProperty("ParameterMapping", ParamMap);
}
}
// Reinitialize the metadata.
try
{
source_instance.AcquireConnections(null);
source_instance.ReinitializeMetaData();
source_instance.ReleaseConnections();
}
catch
{
throw new System.Exception("Can not access source Table/Query");
}
IDTSComponentMetaData100 destination = DataFlowTask.ComponentMetaDataCollection.New();
destination.Name = "OLE DB Dest";
destination.ComponentClassID = "DTSAdapter.OLEDBDestination";
CManagedComponentWrapper destination_instance = destination.Instantiate();
// Initialize the component
destination_instance.ProvideComponentProperties();
// Specify the connection manager.
if (destination.RuntimeConnectionCollection.Count > 0)
{
destination.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(D_con);
destination.RuntimeConnectionCollection[0].ConnectionManagerID = D_con.ID;
}
// Set the custom properties.
destination_instance.SetComponentProperty("AccessMode", 1);
destination_instance.SetComponentProperty("OpenRowsetVariable", "User::Destination_table");
// "select * from dbo.system_source");
// Reinitialize the metadata.
try
{
destination_instance.AcquireConnections(null);
destination_instance.ReinitializeMetaData();
destination_instance.ReleaseConnections();
}
catch
{
throw new System.Exception("Can not access destination Table");
}
IDTSPath100 path = DataFlowTask.PathCollection.New();
path.AttachPathAndPropagateNotifications(source.OutputCollection[0],
destination.InputCollection[0]);
// Get the destination's default input and virtual input.
IDTSInput100 input = destination.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
// Iterate through the virtual input column collection.
foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
// Call the SetUsageType method of the destination
// to add each available virtual input column as an input column.
IDTSInputColumn100 vCol = destination_instance.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
destination_instance.MapInputColumn(input.ID, vCol.ID, input.ExternalMetadataColumnCollection[vColumn.Name].ID);
}
app.SaveToXml(Package_File, pkg, null);
}
public override void Exec()
{
ProjectItem piCurrent = null;
try
{
listNonDefaultProperties = new List<NonDefaultProperty>();
UIHierarchy solExplorer = this.ApplicationObject.ToolWindows.SolutionExplorer;
UIHierarchyItem hierItem = ((UIHierarchyItem)((System.Array)solExplorer.SelectedItems).GetValue(0));
SolutionClass solution = hierItem.Object as SolutionClass;
EnvDTE.Project p = GetSelectedProjectReference();
if (p != null)
{
Microsoft.DataWarehouse.VsIntegration.Shell.Project.Extensibility.ProjectExt projExt = (Microsoft.DataWarehouse.VsIntegration.Shell.Project.Extensibility.ProjectExt)p;
if (projExt.Kind == BIDSProjectKinds.SSAS)
{
Database db = (Database)p.Object;
ScanAnalysisServicesProperties(db);
}
else
{
this.DatabaseName = "Project: " + p.Name;
this.SSASProject = false;
try
{
using (WaitCursor cursor1 = new WaitCursor())
{
int iProgress = 0;
ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationGeneral);
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
foreach (ProjectItem pi in p.ProjectItems)
{
ApplicationObject.StatusBar.Progress(true, "Scanning package " + pi.Name, iProgress++, p.ProjectItems.Count);
string sFileName = pi.Name.ToLower();
if (!sFileName.EndsWith(".dtsx")) continue;
piCurrent = pi;
this.PackagePathPrefix = pi.Name;
Package package = GetPackageFromIntegrationServicesProjectItem(pi);
ScanIntegrationServicesProperties(package);
}
}
}
finally
{
try
{
ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationGeneral);
ApplicationObject.StatusBar.Progress(false, "", 1, 1);
}
catch { }
}
}
}
else if (solution != null)
{
this.DatabaseName = "Solution: " + System.IO.Path.GetFileNameWithoutExtension(solution.FullName);
try
{
this.DatabaseName = "Solution: " + solution.Properties.Item("Name").Value;
}
catch { }
this.SSASProject = false;
try
{
using (WaitCursor cursor1 = new WaitCursor())
{
ApplicationObject.StatusBar.Animate(true, vsStatusAnimation.vsStatusAnimationGeneral);
foreach (EnvDTE.Project proj in solution.Projects)
{
Microsoft.DataWarehouse.VsIntegration.Shell.Project.Extensibility.ProjectExt projExt = (Microsoft.DataWarehouse.VsIntegration.Shell.Project.Extensibility.ProjectExt)proj;
if (projExt.Kind == BIDSProjectKinds.SSIS)
{
int iProgress = 0;
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
foreach (ProjectItem pi in proj.ProjectItems)
{
ApplicationObject.StatusBar.Progress(true, "Scanning project " + proj.Name + " package " + pi.Name, iProgress++, proj.ProjectItems.Count);
string sFileName = pi.Name.ToLower();
if (!sFileName.EndsWith(".dtsx")) continue;
piCurrent = pi;
this.PackagePathPrefix = proj.Name + "\\" + pi.Name;
Package package = GetPackageFromIntegrationServicesProjectItem(pi);
ScanIntegrationServicesProperties(package);
}
}
}
}
}
finally
{
try
{
ApplicationObject.StatusBar.Animate(false, vsStatusAnimation.vsStatusAnimationGeneral);
ApplicationObject.StatusBar.Progress(false, "", 1, 1);
}
catch { }
}
}
else
{
ProjectItem pi = (ProjectItem)hierItem.Object;
piCurrent = pi;
Package package = GetPackageFromIntegrationServicesProjectItem(pi);
this.DatabaseName = "Package: " + package.Name;
this.SSASProject = false;
this.PackagePathPrefix = string.Empty;
ScanIntegrationServicesProperties(package);
}
//clear the cache
this.packageDefault = null;
this.dictCachedDtsObjects.Clear();
if (listNonDefaultProperties.Count == 0)
{
MessageBox.Show("No properties set to non-default values were found.", "BIDS Helper - Non-Default Properties Report");
return;
}
//pop up the form to let the user exclude properties from showing on the report
List<string> listExcludedProperties = new List<string>(this.ExcludedProperties.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries));
BIDSHelper.SSAS.NonDefaultPropertiesSelectionForm selector = new BIDSHelper.SSAS.NonDefaultPropertiesSelectionForm();
foreach (NonDefaultProperty prop in listNonDefaultProperties)
{
if (!selector.listProperties.Items.Contains(prop.PropertyName))
{
bool bChecked = !listExcludedProperties.Contains(prop.PropertyName);
selector.listProperties.Items.Add(prop.PropertyName, bChecked);
}
}
DialogResult selectorResult = selector.ShowDialog();
if (selectorResult == DialogResult.OK)
{
//remove the the report rows they unchecked
for (int i = 0; i < listNonDefaultProperties.Count; i++)
{
if (!selector.listProperties.CheckedItems.Contains(listNonDefaultProperties[i].PropertyName))
{
listNonDefaultProperties.RemoveAt(i--);
}
}
//save their prefs... keep previously prefs which haven't been changes (because an excluded property may not show up in the possible properties list each time you run the report)
foreach (object item in selector.listProperties.Items)
{
if (!selector.listProperties.CheckedItems.Contains(item)) //if excluded, then add to the excluded list
{
if (!listExcludedProperties.Contains(item.ToString()))
listExcludedProperties.Add(item.ToString());
}
else //if included, then remove from the excluded list
{
if (listExcludedProperties.Contains(item.ToString()))
listExcludedProperties.Remove(item.ToString());
}
}
this.ExcludedProperties = string.Join(",", listExcludedProperties.ToArray());
ReportViewerForm frm = new ReportViewerForm();
frm.ReportBindingSource.DataSource = this.listNonDefaultProperties;
frm.Report = "SSAS.NonDefaultProperties.rdlc";
Microsoft.Reporting.WinForms.ReportDataSource reportDataSource1 = new Microsoft.Reporting.WinForms.ReportDataSource();
reportDataSource1.Name = "BIDSHelper_NonDefaultProperty";
reportDataSource1.Value = frm.ReportBindingSource;
frm.ReportViewerControl.LocalReport.DataSources.Add(reportDataSource1);
frm.ReportViewerControl.LocalReport.ReportEmbeddedResource = frm.Report;
frm.Caption = "Non-Default Properties Report";
frm.WindowState = FormWindowState.Maximized;
frm.Show();
}
}
catch (DtsRuntimeException ex)
{
if (ex.ErrorCode == -1073659849L)
{
MessageBox.Show((piCurrent == null ? "This package" : piCurrent.Name) + " has a package password. Please open the package designer, specify the password when the dialog prompts you, then rerun the Non-Default Properties report.\r\n\r\nDetailed error was:\r\n" + ex.Message + "\r\n" + ex.StackTrace, "BIDS Helper - Password Not Specified");
}
else
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "BIDS Helper - Error" + (piCurrent == null ? string.Empty : " scanning " + piCurrent.Name));
}
}
catch (System.Exception ex)
{
MessageBox.Show(ex.Message + "\r\n" + ex.StackTrace, "BIDS Helper - Error" + (piCurrent == null ? string.Empty : " scanning " + piCurrent.Name));
}
}
private void RunDtsPackage()
{
bool isAccount = false;
try {
CloudQueueMessage cqm = packageQueue.GetMessage();
while (cqm != null) {
SSISPackageMessage packageMessage = QueueMessageBase.FromMessage<SSISPackageMessage>(cqm);
isAccount = packageMessage.PackageType.ToLower() == "account";
MSDTS.Application app = new MSDTS.Application();
//
// Load package from file system
//
string packageLocation = System.IO.Directory.GetParent(System.Reflection.Assembly.GetEntryAssembly().Location).FullName;
string configLocation = System.IO.Directory.GetParent(System.Reflection.Assembly.GetEntryAssembly().Location).FullName;
MSDTS.Package package = null;
if (isAccount) {
if (packageMessage.Fuzzy_Match_Field == SSISPackageMessage.Fuzzy_Company_Name) {
package = app.LoadPackage(packageLocation + "\\SSIS Package\\account\\fuzzy_company_name.dtsx", null);
package.ImportConfigurationFile(configLocation + "\\SSIS Package\\account\\fuzzy_company_name.dtsConfig");
}
//TO DO: Add other fuzzy field name here for account fuzzy lookup match.
} else {
//TO DO: Add other fuzzy field name here for contact fuzzy lookup match.
//package = app.LoadPackage(packageLocation + "\\SSIS Package\\contact\\ProfileMatchContacts.dtsx", null);
//package.ImportConfigurationFile(configLocation + "\\SSIS Package\\contact\\dtProfileMatchContacts.dtsConfig");
}
var pkgVars = package.Variables;
//SetMessageContacts("Reading parameters...", true);
package.VariableDispenser.LockOneForWrite("import_file_id", ref pkgVars);
pkgVars["import_file_id"].Value = packageMessage.ImportFileID;
pkgVars.Unlock();
package.VariableDispenser.LockOneForWrite("country", ref pkgVars);
pkgVars["country"].Value = packageMessage.Country;
pkgVars.Unlock();
package.VariableDispenser.LockOneForWrite("confidence", ref pkgVars);
pkgVars["confidence"].Value = packageMessage.Confidence;
pkgVars.Unlock();
package.VariableDispenser.LockOneForWrite("similarity", ref pkgVars);
pkgVars["similarity"].Value = packageMessage.Similarity;
pkgVars.Unlock();
package.VariableDispenser.LockOneForWrite("similarity_operator", ref pkgVars);
pkgVars["similarity_operator"].Value = packageMessage.SimilarityOperator;
pkgVars.Unlock();
package.VariableDispenser.LockOneForWrite("confidence_operator", ref pkgVars);
pkgVars["confidence_operator"].Value = packageMessage.ConfidenceOperator;
pkgVars.Unlock();
package.VariableDispenser.LockOneForWrite("validated", ref pkgVars);
pkgVars["validated"].Value = packageMessage.Validated;
pkgVars.Unlock();
MSDTS.DTSExecResult result = package.Execute();
package.Dispose();
string title = string.Empty;
if (isAccount) {
if (packageMessage.Fuzzy_Match_Field == SSISPackageMessage.Fuzzy_Company_Name) {
title = "AccountNotification_FuzzyCompanyName";
}
//TO DO: add other fuzzy fields for account notification
} else {
//TO DO: add other fuzzy fields for contact notification
}
packageRepository.AddNotification(new UserTextNotification() {
Title = title,
MessageText = "Success",
MessageDate = DateTime.Now,
TargetUserName = packageMessage.UserID.ToString()
});
packageQueue.DeleteMessage(cqm);
cqm = packageQueue.GetMessage();
}
} catch {
}
}
/// <summary>
/// If the package designer is already open, then use the package object on it which may have in-memory modifications.
/// If the package designer is not open, then just load the package from disk.
/// </summary>
/// <param name="pi"></param>
/// <returns></returns>
private Package GetPackageFromIntegrationServicesProjectItem(ProjectItem pi)
{
bool bIsOpen = pi.get_IsOpen(BIDSViewKinds.Designer);
if (bIsOpen)
{
Window w = pi.Open(BIDSViewKinds.Designer); //opens the designer
w.Activate();
IDesignerHost designer = w.Object as IDesignerHost;
if (designer == null) return null;
EditorWindow win = (EditorWindow)designer.GetService(typeof(Microsoft.DataWarehouse.ComponentModel.IComponentNavigator));
return win.PropertiesLinkComponent as Package;
}
else
{
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
return app.LoadPackage(pi.get_FileNames(0), null);
}
}
static void Main(string[] args)
{
// Dont try and make sense of any of this code....I still don't get it....this was a 2 day hack....seriously, rewrite it if you want to make it into a useful tool
// WARNING: I catch general exceptions. TA-DA!!
int result = -2;
OleDbConnection conn = null;
OleDbCommand entire_excel_file = null;
OleDbDataReader config_data_reader = null;
// truncate bobby tables
System.IO.StreamWriter bt = new StreamWriter("bobby_tables.sql");
bt.Close();
// populate the variables.
num_cols = Enum.GetValues(typeof(ConfigColumnNames)).Length;
//args = new string[] { "", @"C:\Users\koln01\Desktop\ESPN\AD_Scope\pip_config.xls" };
app = new Microsoft.SqlServer.Dts.Runtime.Application();
// parameter check, pretty lame one at that.
if (args.Length != 1)
{
Console.WriteLine("This program takes a single argument. The path to an Excel (xlsx) file with config parameters");
System.Environment.Exit(0);
}
//query the excel file to get the entire result set so we can traverse it.
string config_cs = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + args[0] + ";Extended Properties=\"Excel 12.0;HDR=YES\";";
try
{
conn = new OleDbConnection(config_cs);
conn.Open();
}
catch (Exception)
{
Console.WriteLine("There was an error opening the connection to the excel file you gave, make sure you gave the correct path to the correct config");
Console.WriteLine("This is the path I have: {0}", args[0]);
Console.WriteLine("IF that is the correct path, Make sure there isn't a lock on the file (ie. it is open by another program)");
System.Environment.Exit(0);
}
try
{
entire_excel_file = new OleDbCommand("select * from [PIP$] where TableName is not NULL", conn);
config_data_reader = entire_excel_file.ExecuteReader();
}
catch (Exception)
{
Console.WriteLine("I tried to query all of the info in PIP and I couldn't.");
Console.WriteLine("Do you have a tab named 'PIP' in the config file? PS: no trailing spaces or hidden chars?");
Console.WriteLine("Do you have a header called 'TableName'? PS: no trailing spaces or hidden chars?");
System.Environment.Exit(0);
}
//begin the traversal of the excel file. Keeping the current and previous so we know when we encounter a new table.
string[] current_row = new string[num_cols];
string[] previous_row = new string[num_cols];
// hashtable that hashes to a hashtable with values of string...to map the column name to its respective datatypes.
Dictionary<string, Dictionary<string, string>> hash = new Dictionary<string, Dictionary<string, string>>();
Dictionary<string, Dictionary<string, string>> metas = new Dictionary<string, Dictionary<string, string>>();
// Print all table that are about to be dropped and ask "are you sure?"
int count = 0;
string previous = "";
string current = "";
while (config_data_reader.Read())
{
current = config_data_reader[ConfigColumnNames.TableName.ToString()].ToString();
if (config_data_reader["DropTable"].ToString().Trim().ToUpper() == "TRUE" && current != previous)
{
Console.WriteLine("\t" + config_data_reader["TableName"].ToString());
count++;
}
previous = config_data_reader[ConfigColumnNames.TableName.ToString()].ToString();
}
if (count > 0)
{
Console.WriteLine("Um, you are about to drop all of these tables....are you sure you know what you are doing? [y/n/idk]");
string response = Console.ReadLine().Trim().ToLower();
while (response != "y" && response != "n" && response != "idk")
{
Console.WriteLine("Invalid Response. I am expecting one of these: y, n, idk");
response = Console.ReadLine().Trim().ToLower();
}
switch (response)
{
case "y":
break;
case "n":
Console.WriteLine("Whew, that was close.");
System.Environment.Exit(0);
break;
case "idk":
Console.WriteLine("Well, then you probably shouldn't be doing this should you.");
System.Environment.Exit(0);
break;
default:
Console.WriteLine("Ummmm.....Sum Ting Wong. You should never see dis.");
System.Environment.Exit(0);
break;
}
config_data_reader.Close();
}
// Start of the GUTS of PIP
try
{
entire_excel_file = new OleDbCommand("select * from [PIP$] where TableName is not NULL", conn);
config_data_reader = entire_excel_file.ExecuteReader();
}
catch (Exception)
{
Console.WriteLine("I tried to query all of the info in PIP and I couldn't.");
Console.WriteLine("Do you have a tab named 'PIP' in the config file? PS: no trailing spaces or hidden chars?");
Console.WriteLine("Do you have a header called 'TableName'? PS: no trailing spaces or hidden chars?");
System.Environment.Exit(0);
}
while (config_data_reader.Read())
{
// this seems a bit retarded...because it is. I don't know now to cast this to some form of an array that I can keep a previous and current on.
// someone who knows the wonderful world of .NET could probably do this some justice.
foreach (ConfigColumnNames col in Enum.GetValues(typeof(ConfigColumnNames)))
{
string col_value = "";
try
{
col_value = config_data_reader[col.ToString()].ToString();
}
catch (Exception)
{
Console.WriteLine("More than likely, this column '{0}' doesn't exist in PIP config...and PIP expects it to.", col.ToString());
Console.WriteLine("Otherwise, if you are 100% sure the column exists EXACTLY as you see it spelled in this error, check for leading/trailing spaces");
Console.WriteLine("Before and after the column name or any phantom character that don't get displayed...If you have tried all of this and it STILL fails");
Console.WriteLine("This might be worthy of a bug report.");
System.Environment.Exit(0);
}
current_row[(int)col] = col_value;
}
if (current_row[(int)ConfigColumnNames.TableName] != previous_row[(int)ConfigColumnNames.TableName])
{
#region "High Level Algo"
//TODO: this is where we build the create table script & populate the columns in the ssis package with the correct data types.
//This is also where we build the batchfile, using current_row as the list of parameters to obtain.
//Console.WriteLine("{0} is not equal to {1}", current_row[1], previous_row[1]);
// open the template package of currentrow[18]
// update flatfile connection manager with flatfile=currentrow[0]
// x = select * from [packagebot$] where TableName = currentrow[1]
// iterate through the result set.
// update flatfile columns
// update SSIS object model columns
// build create table script. execute create table script.
// save and execute the package.
// load template package so we have something to work off of.
#endregion
if (previous_row[(int)ConfigColumnNames.TemplateSSISPackage] != null)
{
hash.Add(previous_row[(int)ConfigColumnNames.FieldName], new Dictionary<string, string>() {
{"SSIS", previous_row[(int)ConfigColumnNames.SSISFieldFormat]},
{"SQL", previous_row[(int)ConfigColumnNames.SqlFieldFormat]},
{"Default", previous_row[(int)ConfigColumnNames.ClientFieldFormat]},
{"rowdelim", ConvertHexToString(previous_row[(int)ConfigColumnNames.RowDelimiter])},
{"coldelim", ConvertHexToString(previous_row[(int)ConfigColumnNames.ColumnDelimiter])},
{"precision", previous_row[(int)ConfigColumnNames.Precision]},
{"scale", previous_row[(int)ConfigColumnNames.Scale]},
{"textqualified", previous_row[(int)ConfigColumnNames.Text_Qualifier]},
{"TruncationRowDisposition", previous_row[(int)ConfigColumnNames.TruncationRowDisposition]},
{"Description", previous_row[(int)ConfigColumnNames.Field_Comments]}});
//packagename = previous_row[27]; // this isn't the real index of package name....change it when you change the config file
// this needs to be an option in the config file
string connection_string = previous_row[(int)ConfigColumnNames.ConnectionString]; // this isn't the real value, need to change it.
//"Data Source=10.1.0.110;User ID=app_etl;Password=clt2010!;Initial Catalog=" + previous_row[22] + ";Provider=NZOLEDB;Persist Security Info=True;";
bool execute = false;
// check config file to see if we should drop this table
bool drop = false;
if (previous_row[(int)ConfigColumnNames.DropTable].ToUpper().Trim() == "TRUE")
{
drop = true;
}
// Drop Recreate Reload Table
if (previous_row[(int)ConfigColumnNames.Drop_Recreate_Reload].ToUpper().Trim() == "TRUE")
{
string tablename = previous_row[(int)ConfigColumnNames.TableName];
string new_name = tablename + "_TEMP";
string cmd = "ALTER TABLE " + tablename + " RENAME TO " + new_name + ";";
string cmd2 = "INSERT INTO " + tablename + " (select * from " + new_name + ");";
StreamWriter sw = new StreamWriter(new_name + ".sql");
sw.WriteLine(cmd);
sw.WriteLine("-- CREATE TABLE is called here...but I am reusing this script {0}", tablename + ".sql");
sw.WriteLine(cmd2);
sw.Close();
// Execute the Command: this will be in a file with tablename + _TEMP.sql in the current directory
OleDbConnection connection = new OleDbConnection(connection_string);
connection.Open();
OleDbCommand command = new OleDbCommand(cmd, connection);
// Rename Table
Console.Write("Creating Temp Table {0}_TEMP...", tablename);
try
{
result = command.ExecuteNonQuery();
if (result != -1)
{//FIXME: Really you should query the number of records in the table, check to see it that matches result
Console.WriteLine("\nThe world will come to and end. The DB should return -1 for this command and it returned {0}", result);
Console.WriteLine("This was the statement '{0}'", command.CommandText);
System.Environment.Exit(0);
}
}
catch (Exception e)
{
Console.Write("Failed.\n");
Console.WriteLine("Could not alter table {0}", tablename);
Console.WriteLine("Tried to execute this statement '{0}' --- Exception is below:", command.CommandText);
Console.WriteLine(e.Message);
System.Environment.Exit(0);
}
Console.Write("Done.\n");
// Create New Table
string tf = previous_row[(int)ConfigColumnNames.TableFooter].ToString();
CreateSqlTableScript(hash, metas, tablename,connection_string, tf, conn, true, false);
Console.Write("Inserting records into Table {0}...", tablename);
// Insert Old Records into new table
command = new OleDbCommand(cmd2, connection);
try
{
result = command.ExecuteNonQuery();
if (result < 0)
{//FIXME: Really you should query the number of records in the table, check to see it that matches result
Console.WriteLine("\nBUG!! DB Returned {0}", result);
Console.WriteLine("This was the statement '{0}'", command.CommandText);
System.Environment.Exit(0);
}
}
catch (Exception e)
{
Console.Write("\nFailed.\n");
Console.WriteLine("Could not insert records.");
Console.WriteLine("Tried to execute this statement '{0}' --- Exception is below:", command.CommandText);
Console.WriteLine(e.Message);
System.Environment.Exit(0);
}
Console.Write("Done.\nInserted {0} Records\n", result);
// Drop Temp Table
Console.Write("Dropping Temp Table {0}...", tablename);
try
{
command = new OleDbCommand("DROP TABLE " + tablename + "_TEMP;", connection);
result = command.ExecuteNonQuery();
if (result != -1)
{//FIXME: Really you should query the number of records in the table, check to see it that matches result
Console.WriteLine("\nBUG!! DB Returned {0} Should have been -1", result);
Console.WriteLine("This was the statement '{0}'", "DROP TABLE " + tablename + "_TEMP;");
System.Environment.Exit(0);
}
}
catch (Exception e)
{
Console.Write("Failed.\n");
Console.WriteLine("Dropping {0}_TEMP Failed", tablename);
Console.WriteLine("Tried to execute this statement '{0}' --- Exception is below:", "DROP TABLE " + tablename + "_TEMP;");
Console.WriteLine(e.Message);
System.Environment.Exit(0);
}
Console.Write("Done.\n");
// Clean up
connection.Close();
sw.Close();
}
// check config file to see if we should execute this table
if (previous_row[(int)ConfigColumnNames.CreateTable].ToUpper().Trim() == "TRUE")
{
execute = true;
string tf = previous_row[(int)ConfigColumnNames.TableFooter];
CreateSqlTableScript(hash, metas, previous_row[(int)ConfigColumnNames.TableName], connection_string, tf, conn, execute, drop);
}
// modify the metadata structure
if (previous_row[(int)ConfigColumnNames.UpdateMetadata].ToUpper().Trim() == "TRUE")
{
string msource_connection_string = previous_row[(int)ConfigColumnNames.MetaDataConnectionString]; // this insn't the right index...needs to change
InsertMSourceEntity(msource_connection_string, previous_row);
}
// Build the package
if (previous_row[(int)ConfigColumnNames.BuildPackage].ToUpper().Trim() == "TRUE")
{
//reset the flat file connection manager with settings in the excel file
Console.Write("Building Package {0}.....", previous_row[(int)ConfigColumnNames.NewSSISPackage]);
p = app.LoadPackage(previous_row[(int)ConfigColumnNames.TemplateSSISPackage], null);
//GetDataFlowTask(p.Executables, p.Executables.GetEnumerator());
Microsoft.SqlServer.Dts.Runtime.Sequence seq = null;
Microsoft.SqlServer.Dts.Runtime.TaskHost th = null;
// looking for sequence container....this shit is very specific to package structure....I have a recursive function
// That can handle this...but not in this code.
try
{
seq = p.Executables["Sequence Container"] as Microsoft.SqlServer.Dts.Runtime.Sequence;
}
catch (Exception)
{
Console.WriteLine("Failed.\nCould not find the Sequence Container in the Package");
Console.WriteLine("the Sequence container MUST BE NAMED: 'Sequence Container'");
Console.WriteLine("Otherwise, you will always see this error message");
System.Environment.Exit(0);
}
// Looking for data flow task
try
{
th = seq.Executables["Data Flow Task"] as Microsoft.SqlServer.Dts.Runtime.TaskHost;
}
catch (Exception)
{
Console.WriteLine("Failed.\nCould not find the Data Flow Task in the Package");
Console.WriteLine("the Sequence container MUST BE NAMED: 'Data Flow Task'");
Console.WriteLine("Otherwise, you will always see this error message");
System.Environment.Exit(0);
}
// Error Checking
if (seq == null || th == null)
{
Console.WriteLine("Failed.\nSum Ting Wong. You should never see this error message");
System.Environment.Exit(0);
}
data_flow_task = th.InnerObject as MainPipe;
try
{
cm_destination = p.Connections["destination"];
}
catch (Exception)
{
Console.WriteLine("Failed\nERROR: your destination connection manager MUST BE NAMED: 'destination'");
System.Environment.Exit(0);
}
// uncomment for packages with source component, should use a try catch
//cm_source = p.Connections["source"];
//p.PackagePassword = "******";
//p.Variables.Remove("filePathName");
//p.Variables.Add("filePathName", false,"User", previous_row[21]);
cm_destination.ConnectionString = connection_string;
string coldelim = ConvertHexToString(previous_row[(int)ConfigColumnNames.ColumnDelimiter]);
string rowdelim = ConvertHexToString(previous_row[(int)ConfigColumnNames.RowDelimiter]);
string ssis_var = "@[User::filePathName]";
// uncomment the flat file stuff when working with staging that deals with flatfiles
ResetFlatFileConnectionManager(ssis_var, rowdelim, coldelim, ssis_var, previous_row[(int)ConfigColumnNames.Text_Qualifier], p.Connections);
// reset the column properties in the FlatFile Source Component
IDTSConnectionManagerFlatFile100 cmff = cm_flat_file.InnerObject as IDTSConnectionManagerFlatFile100;
ResetFlatFileColumnProperties(cmff, hash);
// modify columns; types; reinitialize source and destination components; remap columns
string table = previous_row[(int)ConfigColumnNames.TableName].Trim().ToUpper();
ModifyComponents(hash, metas, table, table);
Console.Write("{0}\n", p.Validate(p.Connections, p.Variables, null, null));
try
{
app.SaveToXml(previous_row[(int)ConfigColumnNames.NewSSISPackage], p, null);
}
catch (Exception)
{
Console.WriteLine("Failed.\nCould not save the package. Is your path name in the config correct? I have '{0}'", previous_row[(int)ConfigColumnNames.NewSSISPackage]);
Console.WriteLine("Is this package '{0}' open somewhere else?", previous_row[(int)ConfigColumnNames.NewSSISPackage]);
Console.WriteLine("Do you have the correct permissions?");
Console.WriteLine("Is there something else going on that would cause a lock?");
System.Environment.Exit(0);
}
}
if (previous_row[(int)ConfigColumnNames.ExecutePackage].ToUpper().Trim() == "TRUE")
{
if (p == null)
p = app.LoadPackage(previous_row[(int)ConfigColumnNames.NewSSISPackage], null);
Console.Write("Executing Package {0}...", previous_row[(int)ConfigColumnNames.NewSSISPackage]);
Console.WriteLine(p.Execute());
}
hash = new Dictionary<string, Dictionary<string, string>>();
metas = new Dictionary<string, Dictionary<string, string>>();
} // END IF
} // END IF
else
{
hash.Add(previous_row[(int)ConfigColumnNames.FieldName], new Dictionary<string, string>() {
{"SSIS", previous_row[(int)ConfigColumnNames.SSISFieldFormat]},
{"SQL", previous_row[(int)ConfigColumnNames.SqlFieldFormat]},
{"Default", previous_row[(int)ConfigColumnNames.ClientFieldFormat]},
{"rowdelim", ConvertHexToString(previous_row[(int)ConfigColumnNames.RowDelimiter])},
{"coldelim", ConvertHexToString(previous_row[(int)ConfigColumnNames.ColumnDelimiter])},
{"precision", previous_row[(int)ConfigColumnNames.Precision]},
{"scale", previous_row[(int)ConfigColumnNames.Scale]},
{"textqualified", previous_row[(int)ConfigColumnNames.Text_Qualifier]},
{"TruncationRowDisposition", previous_row[(int)ConfigColumnNames.TruncationRowDisposition]},
{"Description", previous_row[(int)ConfigColumnNames.Field_Comments]}});
}
// copy the values in current row to previous row.
for (int i = 0; i < num_cols; i++)
{
previous_row[i] = current_row[i];
}
} // END While
Console.WriteLine("Done.");
Console.ReadLine();
}
private void report_lbReports_SelectedIndexChanged(object sender, EventArgs e)
{
//Report server URL is the current machine (localhost)
report_rvReportViewer.ServerReport.ReportServerUrl = new System.Uri("http://localhost/reportserver");
//All reports are deployed as part of SCIPA, and as such, reports follow
//the namespacing conventions.
var reportPath = "/SCIPA.Domain.BI.Reporting/";
switch (report_lbReports.SelectedIndex)
{
case 0:
reportPath = $"{reportPath}DeviceValuesOverview";
break;
case 1:
reportPath = $"{reportPath}DeviceValuesReport";
break;
case 2:
reportPath = $"{reportPath}ValuesOverTime";
break;
case 3:
reportPath = $"{reportPath}AlarmOverview";
break;
case 4:
//Case 4 is the Excel Export SSIS package.
//Ensure the file is there
if (!System.IO.File.Exists(@"C:\scipa\Output.xls"))
{
//File doesnt exist - the blank template must exist.
System.Windows.Forms.MessageBox.Show("You do not have the blank template available. Aborting...");
return;
}
//Location of the SSIS Package.
string packageLocation = @"C:\Users\Will Whitehead\Dropbox\University\Year 4\Computing Project\"
+@"SCIPA\SCIPA.Domain.BI.Integration\ExportDeviceValuesToExcel.dtsx";
var app = new Microsoft.SqlServer.Dts.Runtime.Application();
var package = app.LoadPackage(packageLocation, null);
var packageResult = package.Execute();
//Inform users of success/fail of package.
if (packageResult == DTSExecResult.Failure)
{
DebugOutput.Print("Failed to Export data to Excel");
System.Windows.Forms.MessageBox.Show("The export process failed. Aborted.");
return;
}
else
{
DebugOutput.Print("Successfully exported data to Excel");
System.Windows.Forms.MessageBox.Show("The file successfully updated.");
return;
}
//Return to ensure that the report viewer doesn't refresh blankly.
case 5:
//Case 5 is the Values over 72 hours old being staged, backed up and removed via an SSIS package.
var msg = System.Windows.Forms.MessageBox.Show("This service will remove all values older than 72 hours from the SCIPA database. Values will first be backed up to an XLS document on your dekstop before being removed. Are you sure you want to continue?","Delete Old Data",MessageBoxButtons.YesNo,MessageBoxIcon.Exclamation);
if (msg == DialogResult.No)
{
DebugOutput.Print("Cancelled old data deletion.");
return;
}
DebugOutput.Print("Attempting to remove all old Values from the database...");
//Ensure the file is there
if (!System.IO.File.Exists(@"C:\scipa\Output Values.xls"))
{
//File doesnt exist - the blank template must exist.
System.Windows.Forms.MessageBox.Show("You do not have the blank template available. Aborting...");
return;
}
//Location of the SSIS Package.
packageLocation = @"C:\Users\Will Whitehead\Dropbox\University\Year 4\Computing Project\"
+ @"SCIPA\SCIPA.Domain.BI.Integration\ValueProcessor.dtsx";
app = new Microsoft.SqlServer.Dts.Runtime.Application();
package = app.LoadPackage(packageLocation, null);
packageResult = package.Execute();
//Inform users of success/fail of package.
if (package.ExecutionStatus!=DTSExecStatus.Completed && packageResult == DTSExecResult.Failure)
{
DebugOutput.Print("Failed to Export data to Excel. Did not delete.");
System.Windows.Forms.MessageBox.Show("The export process failed. Aborted.");
return;
}
else
{
DebugOutput.Print("Successfully exported data to Excel, data was deleted.");
System.Windows.Forms.MessageBox.Show("The file successfully updated.");
return;
}
//Return to ensure that the report viewer doesn't refresh blankly.
}
//Set the desired report.
report_rvReportViewer.ServerReport.ReportPath = reportPath;
//Load the desired report.
report_rvReportViewer.RefreshReport();
// report_rvReportViewer.Refresh();
}
private async System.Threading.Tasks.Task GenerateFiles()
{
// System.Threading.Tasks.Task t = new System.Threading.Tasks.Task
//(
// () =>
// {
Package pkg = null;
try
{
Microsoft.SqlServer.Dts.Runtime.Application app;
DTSExecResult pkgResults;
Variables vars;
String pkgLocation = @"G:\Package1.dtsx";
//String pkgLocation = @"C:\Users\parevi01\documents\visual studio 2012\Projects\ScoringApp1\ScoringApp1\Refs\Package1.dtsx";
//String pkgLocation = @"C:\Users\parevi01\Documents\Visual Studio 2008\Projects\emailtest\emailtest\Package.dtsx";
Console.WriteLine("test1");
lblUpdate.Text = "test1";
//String pkgLocation = @"C:\Users\burjuksx\Desktop\Scoring_Project_Scores\Scoring_Project_Scores\Package1.dtsx";
Console.WriteLine("test2");
lblUpdate.Text = "test2";
//String pkgLocation = @"C:\Users\parevi01\Desktop\emailtest\emailtest\Package.dtsx";
app = new Microsoft.SqlServer.Dts.Runtime.Application();
pkg = app.LoadPackage(pkgLocation, null);
vars = pkg.Variables;
//vars["User::Sub"].Value = "RISKVIEW";
vars["User::model"].Value = "RISKVIEW";
vars["User::mode"].Value = "XML";
vars["User::version"].Value = "3";
pkgResults = pkg.Execute();
if (pkgResults == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure)
{
foreach (Microsoft.SqlServer.Dts.Runtime.DtsError local_DtsError in pkg.Errors)
{
//MessageBox.Show(local_DtsError.Description);
Console.WriteLine(local_DtsError.Description);
lblUpdate.Text = local_DtsError.Description;
throw new System.InvalidOperationException("Package execute with errors !!" + local_DtsError.Description);
}
}
}
catch (Exception ex)
{
Console.WriteLine("test3");
lblUpdate.Text = "test3";
lblUpdate.Text = ex.Message;
throw ex;
}
finally
{
pkg.Dispose();
pkg = null;
}
// }
//);
// t.Start();
// await t;
}
/// <summary>
/// If the package designer is already open, then use the package object on it which may have in-memory modifications.
/// If the package designer is not open, then just load the package from disk.
/// </summary>
/// <param name="pi"></param>
/// <returns></returns>
private PackageProperties GetPackageProperties(ProjectItem pi)
{
Package package;
bool bIsOpen = pi.get_IsOpen(BIDSViewKinds.Designer);
if (bIsOpen)
{
Window w = pi.Open(BIDSViewKinds.Designer); //opens the designer
w.Activate();
IDesignerHost designer = w.Object as IDesignerHost;
if (designer == null) return null;
EditorWindow win = (EditorWindow)designer.GetService(typeof(Microsoft.DataWarehouse.ComponentModel.IComponentNavigator));
package = win.PropertiesLinkComponent as Package;
}
else
{
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
package = app.LoadPackage(pi.get_FileNames(0), null);
}
if (package == null) return null;
PackageProperties props = new PackageProperties(package);
props.DatabaseName = this.DatabaseName;
props.PackagePathPrefix = this.PackagePathPrefix;
return props;
}
