public void TestValidateExtraCustomProperty()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
IDTSComponentMetaData100 speechToText = dataFlowTask.ComponentMetaDataCollection.New();
speechToText.ComponentClassID = typeof(Martin.SQLServer.Dts.SSISSpeechToText).AssemblyQualifiedName;
CManagedComponentWrapper speechToTextInstance = speechToText.Instantiate();
speechToTextInstance.ProvideComponentProperties();
speechToText.CustomPropertyCollection.New();
DTSValidationStatus actual = speechToTextInstance.Validate();
DTSValidationStatus expected = DTSValidationStatus.VS_ISCORRUPT;
Assert.AreEqual(expected, actual);
Assert.AreEqual("[Error] SSIS Speech To Text: There is either to many or not enough custom properties.", events.errorMessages[0]);
}
public void TestOutputColumn_Timecode_Missing()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
IDTSComponentMetaData100 speechToText = dataFlowTask.ComponentMetaDataCollection.New();
speechToText.ComponentClassID = typeof(Martin.SQLServer.Dts.SSISSpeechToText).AssemblyQualifiedName;
CManagedComponentWrapper speechToTextInstance = speechToText.Instantiate();
speechToTextInstance.ProvideComponentProperties();
speechToText.CustomPropertyCollection[Utility.SubscriptionKeyPropName].Value = "NotTheDefault";
// Before this is default setup for a clean component
speechToText.OutputCollection[0].OutputColumnCollection.RemoveObjectByID(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputTimecodeColumnName].ID);
IDTSOutputColumn100 tempCol = speechToText.OutputCollection[0].OutputColumnCollection.New();
tempCol.Name = "TempCol";
tempCol.SetDataTypeProperties(DataType.DT_STR, 10, 0, 0, 1252);
DTSValidationStatus actual = speechToTextInstance.Validate();
DTSValidationStatus expected = DTSValidationStatus.VS_ISCORRUPT;
Assert.AreEqual(expected, actual);
Assert.AreEqual("[Error] SSIS Speech To Text: Required Output Column Timecode is missing.", events.errorMessages[0]);
}
public void TestOutputColumn_InvalidCustomProperty()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
IDTSComponentMetaData100 speechToText = dataFlowTask.ComponentMetaDataCollection.New();
speechToText.ComponentClassID = typeof(Martin.SQLServer.Dts.SSISSpeechToText).AssemblyQualifiedName;
CManagedComponentWrapper speechToTextInstance = speechToText.Instantiate();
speechToTextInstance.ProvideComponentProperties();
speechToText.CustomPropertyCollection[Utility.SubscriptionKeyPropName].Value = "NotTheDefault";
// Before this is default setup for a clean component
IDTSCustomProperty100 cp = speechToText.OutputCollection[0].OutputColumnCollection[0].CustomPropertyCollection.New();
cp.Name = "IAmInvalid";
cp.Value = "IAmInvalid";
DTSValidationStatus actual = speechToTextInstance.Validate();
DTSValidationStatus expected = DTSValidationStatus.VS_ISCORRUPT;
Assert.AreEqual(expected, actual);
Assert.AreEqual("[Error] SSIS Speech To Text: Output Column InputChannel has invalid property IAmInvalid.", events.errorMessages[0]);
}
public void TestValidateOK()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
IDTSComponentMetaData100 speechToText = dataFlowTask.ComponentMetaDataCollection.New();
speechToText.ComponentClassID = typeof(Martin.SQLServer.Dts.SSISSpeechToText).AssemblyQualifiedName;
CManagedComponentWrapper speechToTextInstance = speechToText.Instantiate();
speechToTextInstance.ProvideComponentProperties();
speechToText.CustomPropertyCollection[Utility.SubscriptionKeyPropName].Value = "NotTheDefault";
DTSValidationStatus actual = speechToTextInstance.Validate();
DTSValidationStatus expected = DTSValidationStatus.VS_ISVALID;
Assert.AreEqual(expected, actual);
Assert.AreEqual(0, events.errorMessages.Count, "There are error messages");
}
public void TestAddNumberOfRowsOutputColumns()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
IDTSComponentMetaData100 textFileSplitter = dataFlowTask.ComponentMetaDataCollection.New();
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
textFileSplitter.Name = "Row Splitter Test";
textFileSplitter.ComponentClassID = typeof(Martin.SQLServer.Dts.TextFileSplitter).AssemblyQualifiedName;
CManagedComponentWrapper instance = textFileSplitter.Instantiate();
instance.ProvideComponentProperties();
IDTSOutput100 actual = textFileSplitter.OutputCollection.New();
ManageColumns.AddNumberOfRowsOutputColumns(actual);
Assert.AreEqual(3, actual.OutputColumnCollection.Count, "Number of Columns is wrong");
Assert.AreEqual(MessageStrings.KeyValueColumnName, actual.OutputColumnCollection[0].Name, "Column Name is wrong");
Assert.AreEqual(DataType.DT_STR, actual.OutputColumnCollection[0].DataType, "DataType is wrong");
Assert.AreEqual(255, actual.OutputColumnCollection[0].Length, "Length is wrong");
Assert.AreEqual(MessageStrings.KeyValueColumnDescription, actual.OutputColumnCollection[0].Description, "Description is wrong");
Assert.AreEqual(MessageStrings.NumberOfRowsColumnName, actual.OutputColumnCollection[1].Name, "Column Name is wrong");
Assert.AreEqual(DataType.DT_I8, actual.OutputColumnCollection[1].DataType, "DataType is wrong");
Assert.AreEqual(0, actual.OutputColumnCollection[1].Length, "Length is wrong");
Assert.AreEqual(MessageStrings.NumberOfRowsColumnDescription, actual.OutputColumnCollection[1].Description, "Description is wrong");
Assert.AreEqual(MessageStrings.KeyValueStatusColumnName, actual.OutputColumnCollection[2].Name, "Column Name is wrong");
Assert.AreEqual(DataType.DT_STR, actual.OutputColumnCollection[2].DataType, "DataType is wrong");
Assert.AreEqual(255, actual.OutputColumnCollection[2].Length, "Length is wrong");
Assert.AreEqual(MessageStrings.KeyValueStatusColumnDescription, actual.OutputColumnCollection[2].Description, "Description is wrong");
}
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);
}
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);
}
public void TestSetOutputColumnDefaults()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
IDTSComponentMetaData100 textFileSplitter = dataFlowTask.ComponentMetaDataCollection.New();
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
textFileSplitter.Name = "Row Splitter Test";
textFileSplitter.ComponentClassID = typeof(Martin.SQLServer.Dts.TextFileSplitter).AssemblyQualifiedName;
CManagedComponentWrapper instance = textFileSplitter.Instantiate();
instance.ProvideComponentProperties();
IDTSOutput100 output = textFileSplitter.OutputCollection.New();
IDTSOutputColumn100 actual = output.OutputColumnCollection.New();
ManageColumns.SetOutputColumnDefaults(actual, 1252);
Assert.AreEqual(DataType.DT_STR, actual.DataType, "DataType is wrong");
Assert.AreEqual(255, actual.Length, "Length is wrong");
Assert.AreEqual(DTSRowDisposition.RD_NotUsed, actual.ErrorRowDisposition, "Row Disposition is wrong");
Assert.AreEqual(DTSRowDisposition.RD_NotUsed, actual.TruncationRowDisposition, "Truncate Disposition is wrong");
}
/// <summary>
/// Constructs a graph given a name
/// </summary>
/// <param name="name">Name of package</param>
/// <returns>Graph</returns>
public static Graph CreateGraph(string name)
{
Logger.Common($"Creating graph {name}");
Package package = _application.LoadPackage(NameToPath(name), null);
return(new Graph(_application, package, name, _options));
}
//========== 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 void TestChangeRowsProcessedOutputTypeToData()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
IDTSComponentMetaData100 textFileSplitter = dataFlowTask.ComponentMetaDataCollection.New();
textFileSplitter.Name = "Row Splitter Test";
textFileSplitter.ComponentClassID = typeof(Martin.SQLServer.Dts.TextFileSplitter).AssemblyQualifiedName;
CManagedComponentWrapper instance = textFileSplitter.Instantiate();
instance.ProvideComponentProperties();
Boolean exceptionThrown = false;
try
{
instance.SetOutputProperty(textFileSplitter.OutputCollection[3].ID, ManageProperties.typeOfOutput, Utilities.typeOfOutputEnum.DataRecords);
}
catch (COMException ex)
{
Assert.AreEqual(MessageStrings.CantChangeOutputProperties("RowsProcessed"), ex.Message, "Exception Message Wrong");
exceptionThrown = true;
}
Assert.IsTrue(exceptionThrown, "Exception Not Thrown");
}
public void TestExecuteRecogniseAsyncBasic()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
IDTSComponentMetaData100 speechToText = dataFlowTask.ComponentMetaDataCollection.New();
speechToText.ComponentClassID = typeof(Martin.SQLServer.Dts.SSISSpeechToText).AssemblyQualifiedName;
CManagedComponentWrapper speechToTextInstance = speechToText.Instantiate();
speechToTextInstance.ProvideComponentProperties();
speechToText.CustomPropertyCollection[Utility.SubscriptionKeyPropName].Value = config.AppSettings.Settings["subscriptionKey"].Value;
Uri targetURI = new Uri(@"wss://speech.platform.bing.com/api/service/recognition/continuous");
SpeechToTextImplement testMe = new SpeechToTextImplement(speechToText, "en-us", targetURI, config.AppSettings.Settings["subscriptionKey"].Value);
string filename = "Ex_Pro_1.mp3";
testMe.ExecuteRecogniseAsync(filename).Wait();
Assert.IsTrue(testMe.Results.Count > 0);
}
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;
}
private void RunPackage(Extraction extraction, ExtractionJob job, ExtractionTrigger extractionTrigger)
{
var packagePath = extraction.PackagePath + extraction.PackageName + ".dtsx";
dts.Package package = _app.LoadPackage(packagePath, null);
Log.WriteErrorLog(string.Format("Starting package execution for {0}-{1}.", _serviceName, extraction.Title));
//Update tigger to Running
extractionTrigger.Status = PackageStatus.Running;
extractionTrigger.Remark = "Package currently running...";
_dataManager.UpdateExtractionTrigger(_connectionString, extractionTrigger);
//DD-mon-YYYY
extraction.ScriptText = extraction.ScriptText.Replace("@StartDate", job.StartDate.GetOracleDate());
extraction.ScriptText = extraction.ScriptText.Replace("@EndDate", job.EndDate.GetOracleDate());
extraction.ScriptText = extraction.ScriptText.Replace("@FYYear", string.Format("FY{0}", job.EndDate.Year.ToString()));
extraction.ScriptText = extraction.ScriptText.Replace("@Year", string.Format("{0}", job.EndDate.Year.ToString()));
extraction.ScriptText = extraction.ScriptText.Replace("@Period", string.Format("M{0}", job.EndDate.Month.ToString().PadLeft(2, '0')));
package.Variables["StorProc"].Value = extraction.ScriptText;//"Script", true, "", extraction.ScriptText);
var result = package.Execute();
if (result == dts.DTSExecResult.Success)
{
int ssisDuration = 0;
//int.TryParse(Math.Round((package.ExecutionDuration / 1000.00), 2).ToString(), out ssisDuration);
ssisDuration = (package.ExecutionDuration / 1000);
Log.WriteErrorLog(string.Format("Package execution for {0}-{1} successfull.", _serviceName, extraction.Title));
//Update tigger to Done
extractionTrigger.Status = PackageStatus.Done;
//extractionTrigger.Remark = string.Format("{0}{1}lines extracted ({2})", string.Format("Package {0}-{1} successfully executed: ", _serviceName, extraction.Title), package.Variables["RecCount"].Value.ToString(), DateTimeExtensions.GetRealTime(ssisDuration));
extractionTrigger.Remark = string.Format("{0}{1}lines extracted ({2})", string.Format("Package {0}-{1} successfully executed: ", _serviceName, extraction.Title), package.Variables["RecCount"].Value.ToString(), HHMMSS(ssisDuration));
_dataManager.UpdateExtractionTrigger(_connectionString, extractionTrigger);
//_Exp = _ProcessName & ": " & _ssisCount & " lines extracted (" & HHMMSS(_ssisDuration) & ")"
}
else
{
string errorMessage = string.Empty;
foreach (var error in package.Errors)
{
errorMessage += error.Description;
}
Log.WriteErrorLog(string.Format("Package execution for {0}-{1} failed.{2}", _serviceName, extraction.Title, errorMessage));
//Update tigger to Fail
extractionTrigger.Status = PackageStatus.Fail;
extractionTrigger.Remark = string.Format("Package {0}-{1} failed.", _serviceName, extraction.Title) + "\n" + errorMessage;
_dataManager.UpdateExtractionTrigger(_connectionString, extractionTrigger);
}
}
public void Reload()
{
this.Save();
string qualifiedProjectPath = QualifiedProjectPath;
DirectoryInfo packageDirectoryInfo = System.IO.Directory.CreateDirectory(qualifiedProjectPath);
qualifiedProjectPath = packageDirectoryInfo.FullName
+ _packageName
+ Resources.ExtensionDTSXProjectFile;
this._package = _DTSApplication.LoadPackage(qualifiedProjectPath, null);
}
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 TestIDTSOutputCreator()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
IDTSComponentMetaData100 textFileSplitter = dataFlowTask.ComponentMetaDataCollection.New();
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
textFileSplitter.Name = "Row Splitter Test";
textFileSplitter.ComponentClassID = typeof(Martin.SQLServer.Dts.TextFileSplitter).AssemblyQualifiedName;
CManagedComponentWrapper instance = textFileSplitter.Instantiate();
instance.ProvideComponentProperties();
IDTSOutput100 output = textFileSplitter.OutputCollection.New();
output.Name = "New # Output";
output.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
ManageProperties.AddOutputProperties(output.CustomPropertyCollection);
ManageColumns.AddNumberOfRowsOutputColumns(output);
ManageProperties propertyManager = new ManageProperties();
propertyManager.PostErrorEvent += new PostErrorDelegate(this.PostError);
errorMessages = new List <string>();
SSISOutput actual = new SSISOutput(output, null);
Assert.IsNotNull(actual, "SSISOutput is null");
Assert.IsNotNull(actual.CustomPropertyCollection, "Custom Property Collection is null");
Assert.IsNotNull(actual.OutputColumnCollection, "Column Collection is null");
Assert.AreEqual(3, actual.CustomPropertyCollection.Count, "Custom Property Collection Count is Wrong");
Assert.AreEqual(3, actual.OutputColumnCollection.Count, "Output Column Collection Count is Wrong");
Assert.IsNotNull(ManageProperties.GetPropertyValue(actual.CustomPropertyCollection, ManageProperties.typeOfOutput));
Assert.IsNotNull(ManageProperties.GetPropertyValue(actual.CustomPropertyCollection, ManageProperties.rowTypeValue));
Assert.IsNotNull(ManageProperties.GetPropertyValue(actual.CustomPropertyCollection, ManageProperties.masterRecordID));
Assert.AreEqual("_NewOutput", actual.Name, "Name is incorrect");
Assert.AreEqual(DTSRowDisposition.RD_NotUsed, actual.ErrorRowDisposition, "Error Row Disposition is incorrect");
}
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!");
}
public static void CreateSQLCEComponent(Microsoft.SqlServer.Dts.Runtime.Package package, MainPipe dataFlowTask, string sqlCEDatabaseName, string sqlCEPassword, String tableName, out ConnectionManager sqlCECM, out IDTSComponentMetaData100 sqlCETarget, out CManagedComponentWrapper sqlCEInstance)
{
// Add SQL CE Connection
sqlCECM = package.Connections.Add("SQLMOBILE");
sqlCECM.ConnectionString = connectionString(sqlCEDatabaseName, sqlCEPassword);
sqlCECM.Name = "SQLCE Destination " + tableName;
sqlCETarget = dataFlowTask.ComponentMetaDataCollection.New();
sqlCETarget.ComponentClassID = typeof(Microsoft.SqlServer.Dts.Pipeline.SqlCEDestinationAdapter).AssemblyQualifiedName;
sqlCEInstance = sqlCETarget.Instantiate();
sqlCEInstance.ProvideComponentProperties();
sqlCETarget.Name = "SQLCE Target " + tableName;
sqlCETarget.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(sqlCECM);
sqlCETarget.RuntimeConnectionCollection[0].ConnectionManagerID = sqlCECM.ID;
sqlCETarget.CustomPropertyCollection["Table Name"].Value = tableName;
sqlCEInstance.AcquireConnections(null);
sqlCEInstance.ReinitializeMetaData();
sqlCEInstance.ReleaseConnections();
}
public static DtsExecutionResult RunPackage(string path, Dictionary <string, Object> parameters)
{
DtsExecutionResult result = null;
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Microsoft.SqlServer.Dts.Runtime.Package package = null;
package = app.LoadPackage(path, null, false);
var x = package.Variables;
foreach (var item in x)
{
string p = item.Name;
//Debug.Print(p.ToString());
if (parameters.ContainsKey(p))
{
item.Value = parameters[item.Name];
}
}
Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute();
if (results == DTSExecResult.Failure)
{
foreach (var item in package.Errors)
{
string s = string.Format("{0}{1}{2}{1}{3}{1}{4}", item.Description, Environment.NewLine, item.ErrorCode, item.Source, item.SubComponent);
Exception ex = new Exception(s);
logger.Log(ex);
}
}
result = new DtsExecutionResult(results);
foreach (var item in package.Variables)
{
if (!item.SystemVariable)
{
result.Add(item.Name, item.Value);
}
}
return(result);
}
public void TestChangeErrorOutputSetColumnProperty()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
IDTSComponentMetaData100 textFileSplitter = dataFlowTask.ComponentMetaDataCollection.New();
textFileSplitter.Name = "Row Splitter Test";
textFileSplitter.ComponentClassID = typeof(Martin.SQLServer.Dts.TextFileSplitter).AssemblyQualifiedName;
CManagedComponentWrapper instance = textFileSplitter.Instantiate();
instance.ProvideComponentProperties();
IDTSOutput100 errorOutput = textFileSplitter.OutputCollection[1];
ManageProperties.AddMissingOutputColumnProperties(errorOutput.OutputColumnCollection[0].CustomPropertyCollection);
Boolean exceptionThrown = false;
try
{
instance.SetOutputColumnProperty(errorOutput.ID, errorOutput.OutputColumnCollection[0].ID, ManageProperties.dotNetFormatString, String.Empty);
}
catch (COMException ex)
{
Assert.AreEqual(MessageStrings.CantChangeOutputProperties("Error"), ex.Message, "Exception Message Wrong");
exceptionThrown = true;
}
Assert.IsTrue(exceptionThrown, "Exception Not Thrown");
}
private void RunPackage(Processes processes, ProcessJob job, ProcessTrigger processTrigger)
{
var packagePath = processes.PackagePath + processes.PackageName + ".dtsx";
dts.Package package = _app.LoadPackage(packagePath, null);
Log.WriteErrorLog(string.Format("Starting package execution for {0}---{1}.", _serviceName, processes.Title));
//Update tigger to Running
processTrigger.Status = PackageStatus.Running;
processTrigger.Remark = "Package currently running...";
_dataManager.UpdateProcessTrigger(_connectionString, processTrigger);
var result = package.Execute();
if (result == dts.DTSExecResult.Success)
{
int ssisDuration = 0;
ssisDuration = (package.ExecutionDuration / 1000);
// int.TryParse(Math.Round((package.ExecutionDuration / 1000.00), 2).ToString(), out ssisDuration);
var statusMessage = string.Format("Package execution for {0}-{1} successfull.", _serviceName, processes.Title);
try
{
if (_currentProcess == Convert.ToInt32(processId) && Count() != 0)
{
//processTrigger.Remark = string.Format("Package {0}-{1} successfully executed,however cannot proceed with the next process ", _serviceName, processes.Title) + "Duration: (" + HHMMSS(ssisDuration) + ")";
processTrigger.Remark = string.Format("Package {0}-{1} successfully executed,however cannot proceed with the next process ", _serviceName, processes.Title) + "Duration- HH:MM:SS: (" + HHMMSS(ssisDuration) + ")";
processTrigger.Status = PackageStatus.Done;
}
else
{
processTrigger.Remark = string.Format("Package {0}-{1} successfully executed: ", _serviceName, processes.Title) + "Duration- HH:MM:SS: (" + HHMMSS(ssisDuration) + ")";
processTrigger.Status = PackageStatus.Done;
}
//processTrigger.Remark = string.Format("Package {0}-{1} successfully executed: ", _serviceName, processes.Title) + "Duration: (" + HHMMSS(ssisDuration) + ")";
//processTrigger.Status = PackageStatus.Done;
var message = package.Variables["Message"].Value.ToString();
if (!string.IsNullOrEmpty(message))
{
char firstLevelSeparator = '/';
char secondLevelSeparator = '|';
var firstLevels = message.Split(firstLevelSeparator);
var secondLevels = firstLevels[2].Split(secondLevelSeparator);
if (firstLevels[1] == "Failed")
{
statusMessage = string.Format("Package execution for {0}-{1} for operation {2} Fail.", _serviceName, processes.Title, firstLevels[0]) + "Duration- HH:MM:SS: (" + HHMMSS(ssisDuration) + ")";
processTrigger.Remark = string.Format("Package {0}-{1} for operation {2} execution failed: ", _serviceName, processes.Title, firstLevels[0]) + "Duration- HH:MM:SS: (" + HHMMSS(ssisDuration) + ")/n";
foreach (var s in secondLevels)
{
processTrigger.Remark += s + "/n";
}
processTrigger.Status = PackageStatus.Fail;
Log.WriteErrorLog(processTrigger.Remark);
}
else
{
//if (_redcount != 0)
//{
// statusMessage = string.Format("Package execution for {0}-{1} for operation {2} successful.", _serviceName, processes.Title, firstLevels[0]) + "Duration: (" + HHMMSS(ssisDuration) + ")";
// processTrigger.Remark = string.Format("Package {0}-{1} for operation {2} execution successful: ", _serviceName, processes.Title, firstLevels[0]) + "Duration: (" + HHMMSS(ssisDuration) + ")";
//}
//else
//{
// statusMessage = string.Format("Package execution for {0}-{1} for operation {2} successful,however cannot proceed with the nest process", _serviceName, processes.Title, firstLevels[0]) + "Duration: (" + HHMMSS(ssisDuration) + ")";
// processTrigger.Remark = string.Format("Package {0}-{1} for operation {2} execution successful:however cannot proceed with the nest process ", _serviceName, processes.Title, firstLevels[0]) + "Duration: (" + HHMMSS(ssisDuration) + ")";
//}
statusMessage = string.Format("Package execution for {0}-{1} for operation {2} successful.", _serviceName, processes.Title, firstLevels[0]) + "Duration- HH:MM:SS: (" + HHMMSS(ssisDuration) + ")";
processTrigger.Remark = string.Format("Package {0}-{1} for operation {2} execution successful: ", _serviceName, processes.Title, firstLevels[0]) + "Duration- HH:MM:SS: (" + HHMMSS(ssisDuration) + ")";
foreach (var s in secondLevels)
{
processTrigger.Remark += s;
}
processTrigger.Status = PackageStatus.Done;
Log.WriteErrorLog(processTrigger.Remark);
}
}
else
{
Log.WriteErrorLog("Message variable is empty.");
}
}
catch (Exception ex)
{
Log.WriteErrorLog(ex.Message);
}
Log.WriteErrorLog(statusMessage);
//Update tigger to Done
_dataManager.UpdateProcessTrigger(_connectionString, processTrigger);
//_Exp = _ProcessName & ": " & _ssisCount & " lines extracted (" & HHMMSS(_ssisDuration) & ")"
}
else
{
string errorMessage = string.Empty;
foreach (var error in package.Errors)
{
errorMessage += error.Description;
}
Log.WriteErrorLog(string.Format("Package execution for {0}-{1} failed.", _serviceName, processes.Title));
//Update tigger to Fail
processTrigger.Status = PackageStatus.Fail;
processTrigger.Remark = string.Format("Package {0}-{1} failed.", _serviceName, processes.Title) + "\n" + errorMessage;
_dataManager.UpdateProcessTrigger(_connectionString, processTrigger);
}
}
public void Reload()
{
this.Save();
string qualifiedProjectPath = QualifiedProjectPath;
DirectoryInfo packageDirectoryInfo = System.IO.Directory.CreateDirectory(qualifiedProjectPath);
qualifiedProjectPath = packageDirectoryInfo.FullName
+ _packageName
+ Resources.ExtensionDTSXProjectFile;
this._package = _DTSApplication.LoadPackage(qualifiedProjectPath, null);
}
public ActionResult DownloadBrewReport(String _BatchKey, bool CreateLogFile = false)
{
// Parameterhandling
const string FILENAME_TYPE = "xlsx";
const string EXCEL_TEMPLATE_NAME = "ExcelReport_BatchBrew_Template" + "." + FILENAME_TYPE;
const string SSIS_PACKAGE = "ExcelReport_BatchBrew.dtsx";
const string FOLDER_BASE_SOURCE = "Areas\\PlantiT.Web.HeinekenMassafra.MES\\Excel";
const string FOLDER_BIN = "bin";
const string FOLDER_TEMPLATE = "template";
const string FOLDER_BASE_OUTPUT = "Areas\\PlantiT.Web.HeinekenMassafra.MES\\Excel\\output";
const string LOG_FILE_NAME = "DownloadBrewBatch_Log.txt";
const int WAITTIME_IN_MS = 1000;
const int MAXWAITTIME_IN_MS = 30000;
String sLocation = Assembly.GetExecutingAssembly().Location; // Get current directory
Logger logger = null;
long nBatchKey = 0;
string sBatchName = String.Empty;
string sBatchNumber = String.Empty;
string sSAPMaterial = String.Empty;
string sSAP_Batch = String.Empty;
string sBrewLine = String.Empty;
string sShortMaterial = String.Empty;
string sSourcePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, FOLDER_BASE_SOURCE);
string sDestinationPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, FOLDER_BASE_OUTPUT);
string sDestinationFilePath = String.Empty;
string sFileName = String.Empty;
DateTime dtCheckTime = DateTime.Now;
List <CBatch> listBatch = null;
// Log generieren
if (CreateLogFile)
{
logger = new Logger(Path.Combine(sDestinationPath, LOG_FILE_NAME), true);
}
// Batch Key ermitteln
try
{
nBatchKey = Convert.ToInt64(_BatchKey);
}
catch
{
return(new HttpNotFoundResult(String.Format("Invalid BatchKey: {0}", _BatchKey)));
}
// Batchdaten ermitteln
String odataURI = String.Format("odata/ods/ZWebSAPInterfaceStatuses?$select=_BatchKey,BatchName,SAPMaterial,SAP_Batch,BatchProcessStartTime&$filter=_BatchKey eq {0}", _BatchKey);
PGatewayConnection gatewayConnection = DependencyResolver.Current.GetService(typeof(PGatewayConnection)) as PGatewayConnection;
try
{
listBatch = gatewayConnection.ReadItem <CODataResponse <CBatch> >(odataURI).Value;
}
catch
{
return(new HttpNotFoundResult(String.Format("OData query invalid for BatchKey: {0}", _BatchKey)));
}
// Daten prüfen
if (listBatch == null || listBatch.Where(x => x._BatchKey == nBatchKey).Count() == 0)
{
return(new HttpNotFoundResult(String.Format("Data not found for BatchKey: {0}", _BatchKey)));
}
// Namen von Auftrag ermitteln
sBatchName = listBatch.Where(x => x._BatchKey == nBatchKey).FirstOrDefault().BatchName;
sBatchNumber = sBatchName.Substring(0, 12).Substring(sBatchName.Substring(0, 12).Length - 4, 4);
sSAPMaterial = listBatch.Where(x => x._BatchKey == nBatchKey).FirstOrDefault().SAPMaterial;
sSAP_Batch = listBatch.Where(x => x._BatchKey == nBatchKey).FirstOrDefault().SAP_Batch;
sBrewLine = sSAP_Batch.Substring(5, 1);
sShortMaterial = sSAPMaterial.Substring(sSAPMaterial.Length - 5);
string FILENAME_PREFIX = (listBatch.Where(x => x._BatchKey == nBatchKey).FirstOrDefault().BatchProcessStartTime).ToString("yy");
// BatchName prüfen
if (sBatchName.Equals(String.Empty))
{
return(new HttpNotFoundResult(String.Format("No Batchname found for BatchKey: {0}", _BatchKey)));
}
// Nach Datei suchen
sFileName = FILENAME_PREFIX + "_" + sBatchNumber + "_" + sBrewLine + "_1_" + sShortMaterial;
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Microsoft.SqlServer.Dts.Runtime.Package package = null;
MyEventListener eventListener = new MyEventListener();
//Load the SSIS Package which will be executed
app.PackagePassword = "******";
String sPackageFilePath = Path.Combine(sSourcePath, FOLDER_BIN, SSIS_PACKAGE);
// Execute
try
{
// Load package
package = app.LoadPackage(sPackageFilePath, eventListener);
// Set values
Variables vars = package.Variables;
vars["User::InputBatchKey"].Value = nBatchKey;
vars["User::InputDestinationFile"].Value = sFileName + "." + FILENAME_TYPE;
vars["User::InputDestinationPath"].Value = sDestinationPath;
vars["User::InputTemplateFilePath"].Value = Path.Combine(sSourcePath, FOLDER_TEMPLATE, EXCEL_TEMPLATE_NAME);
vars["User::InputCreateLogFile"].Value = CreateLogFile;
vars["User::InputLogFilePath"].Value = (logger != null) ? logger.FilePath : String.Empty;
// Log erzeugen
if (logger != null)
{
logger.Log("InputBatchKey", vars["User::InputBatchKey"].Value.ToString());
logger.Log("InputDestinationFile", vars["User::InputDestinationFile"].Value.ToString());
logger.Log("InputDestinationPath", vars["User::InputDestinationPath"].Value.ToString());
logger.Log("InputTemplateFilePath", vars["User::InputTemplateFilePath"].Value.ToString());
}
vars["InputToOverwrite"].Value = true;
DTSExecResult results = package.Execute(null, null, eventListener, null, null);
}
catch (Exception ex)
{
if (logger != null)
{
logger.Log("Error", String.Format("Error: {0} // Innermessage: {1}", ex.Message, (ex.InnerException != null) ? ex.InnerException.Message : String.Empty));
}
return(new HttpNotFoundResult(String.Format("Error: {0} // Innermessage: {1}", ex.Message, (ex.InnerException != null) ? ex.InnerException.Message : String.Empty)));
}
// zyklisch prüfen ob Datei vorhanden ist, maximal MAXWAITTIME_IN_MS
for (int i = 0; i < MAXWAITTIME_IN_MS; i += WAITTIME_IN_MS)
{
// erstmal eine Sekunde zeit geben, zyklisch prüfen
System.Threading.Thread.Sleep(WAITTIME_IN_MS);
// alle Dateien des Pfades holen die dem Suchmuster entsprechen
DirectoryInfo dirInfo = new DirectoryInfo(Path.Combine(sDestinationPath));
var filesInPath = dirInfo.EnumerateFiles(String.Format("{0}*.{1}", sFileName, FILENAME_TYPE));
var file = filesInPath.Where(x => x.Name.Contains(sFileName)).OrderByDescending(x => x.LastWriteTime).FirstOrDefault();
// nur welche die eben erstellt wurde
if (file == null)
{
continue;
}
// Falls eben erstellt...
if (file.LastWriteTime >= dtCheckTime)
{
// gefunden, Abbruch
sFileName = file.Name;
break;
}
}
// Pfad mit Datei erzeugen
sDestinationFilePath = Path.Combine(sDestinationPath, sFileName);
// Datei vorhanden?
if (!System.IO.File.Exists(sDestinationFilePath))
{
return(new HttpNotFoundResult(String.Format("Excel file could not created for BatchKey: {0}", _BatchKey)));
}
// Create data return stream
byte[] fileData = System.IO.File.ReadAllBytes(sDestinationFilePath);
string contentType = System.Web.MimeMapping.GetMimeMapping(sDestinationFilePath);
// Datei entfernen
System.IO.File.Delete(sDestinationFilePath);
var cd = new System.Net.Mime.ContentDisposition()
{
// for example foo.bak
FileName = sFileName,
// always prompt the user for downloading, set to true if you want
// the browser to try to show the file inline
Inline = false,
};
Response.AppendHeader("Content-Disposition", cd.ToString());
return(File(fileData, contentType));
}
private void LoadPackage()
{
string contents = String.Empty;
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
using (StreamReader r = new StreamReader("DemoPackageWithoutDesignTimeProperties.dtsx"))
{
contents = r.ReadToEnd();
}
package.LoadFromXML(contents, null);
ControlFlowGraphModelElement controlFlowGraphModelElement = new ControlFlowGraphModelElement();
controlFlowGraphModelElement.Initialize(package as IDTSSequence);
ModelElement m = controlFlowGraphModelElement.GetModelElement("{FFE5461A-385C-4A35-984A-91B6A8E43119}");
GraphModelElement graphModelElement = new GraphModelElement();
graphModelElement.Container = controlFlowGraphModelElement.Container;
GraphLayout graphLayout = GraphLayout.GetLayout(graphModelElement);
GraphControl graphControl = new GraphControl();
graphControl.Width = 1000;
graphControl.Height = 1000;
graphControl.DataContext = m;
GraphPanelEx p1 = new GraphPanelEx();
p1.Width = 1000;
p1.Height = 1000;
p1.GraphModel = graphModelElement;
p1.UpdateEx(true);
GraphControlEx graphControlEx = new GraphControlEx();
graphControlEx.DataContext = controlFlowGraphModelElement.Container;
graphControlEx.AssignLayout(controlFlowGraphModelElement.Container, new Point(100, 100));
graphControlEx.UpdateLayout();
GraphModelElementEx graphModelElementEx = new GraphModelElementEx();
graphModelElementEx.GraphControl = graphControl;
graphModelElementEx.Container = controlFlowGraphModelElement.Container;
graphModelElementEx.RefreshGraph(controlFlowGraphModelElement.Container);
GraphPanelEx graphPanelEx = new GraphPanelEx();
graphPanelEx.Width = 1000;
graphPanelEx.Height = 1000;
graphPanelEx.DataContext = graphModelElement;
graphPanelEx.GraphModel = graphModelElement;
graphPanelEx.LayoutEngine = new Microsoft.SqlServer.Graph.LayoutEngine.Sugiyama.MsaglLayoutGraph();
graphPanelEx.UpdateEx();
//MethodInfo dynMethod = graphPanelEx.GetType().GetMethod("CalculateLayout", BindingFlags.NonPublic | BindingFlags.Instance);
//dynMethod.Invoke(this, new object[] { false });
graphLayout.ApplyLayout(graphModelElementEx);
ContainerModelElementEx x = controlFlowGraphModelElement.Container as ContainerModelElementEx;
var children = ContainerModelElementEx.GetAllChildren(controlFlowGraphModelElement.Container);
var bounds = ContainerModelElementEx.GetBounds(x.Children);
graphModelElementEx.RefreshGraph(graphModelElementEx.Container);
LayoutGraph layoutGraph = new LayoutGraph();
graphLayout.AppendLayout(controlFlowGraphModelElement.Container.Children);
graphLayout.ApplyLayout(controlFlowGraphModelElement);
// Should contain the serialized GraphLayout XML Data with correct coordinates...
var graphLayoutXml = SerializerHelper.Save(graphModelElement);
}
static void Main(string[] args)
{
string testDTSX = "LargeExample.dtsx";
string path = Path.Combine(SSISFolder, testDTSX);
// Traverse through every single EDS and save a snapshot of it
//foreach (EDSSettings EDS in SettingsStore.EDSSettings.Where(e => e.Name != "Template"))
//{
// MetaDataSnapshot metaDataSnapshot = new MetaDataSnapshot(EDS.ConnectionString);
// Console.WriteLine(metaDataSnapshot);
// MetaDataStore.Provider.SaveSnapshot(EDS, metaDataSnapshot);
//}
// Key = EDS connection string
Dictionary <string, DatabaseMetaChange> latestChanges = MetaDataStore.GetLatestChanges(SettingsStore.EDSSettings);
_databaseMetaChange = latestChanges.Values.First(); //TODO: change this later.
// Print out changes
Console.WriteLine(_databaseMetaChange);
// Create a new application to host packages, and load a package within it
Application application = new Application();
Package package = application.LoadPackage(path, null);
Graph g = new Graph(application, package, testDTSX, new Options.Options())
{
Options = new Options.Options()
};
// Print attributes, which is used in graph
List <Dictionary <string, Attribute> > list = g.AttributeTable.Values.SelectMany(d => d.Values).ToList();
List <Attribute> attributes = list.SelectMany(d => d.Values).ToList();
string s = string.Join("\n", attributes.Select(a => $" -{a.Id}/{a.AttributeRef?.ID}: {a.Name}"));
Console.WriteLine(s);
// Iterate through all meta changes
foreach (TableMetaChange tableMetaChange in _databaseMetaChange.Tables.Values)
{
foreach (ColumnMetaChange columnMetaChange in tableMetaChange.Columns.Values.Where(c => c.ColumnChanges > 0))
{
foreach (ColumnChanges change in columnMetaChange.ListChanges())
{
g.Alter(columnMetaChange, change);
}
}
}
// Validate package before saving it
package.Validate(package.Connections, package.Variables, new PackageValidateErrorEvent(), null);
// Save package
application.SaveToXml(Path.Combine(TestOutputFolder, "packages", testDTSX), package, null);
Console.WriteLine("Done");
Console.ReadKey();
}
public override object Execute(object project, Package package, DtsContainer container)
{
#if SQL2012 || SQL2014 || SQL2017
object returnValue;
CommandParentType commandParentType = GetCommandParentType();
try
{
OnCommandStarted(new CommandStartedEventArgs(DateTime.Now, CommandName, null, null, commandParentType));
Parameter parameter;
if (ParameterType == ParameterType.Project)
{
Project currentProject = project as Project;
if (currentProject == null)
{
throw new Exception("The package's project was not loaded.");
}
parameter = currentProject.Parameters[ParameterName];
if (parameter == null)
{
throw new Exception(string.Format("The project parameter {0} could not be found.", ParameterName ?? "<NULL>"));
}
}
else
{
if (package == null)
{
throw new ArgumentNullException("package", "The package was not passed correctly or loaded.");
}
parameter = package.Parameters[ParameterName];
if (parameter == null)
{
throw new Exception(string.Format("The package parameter {0} could not be found.", ParameterName ?? "<NULL>"));
}
}
if (Operation == VariableCommand.VariableOperation.Get)
{
returnValue = parameter.Value;
}
else
{
returnValue = parameter.Value = Convert.ChangeType(Value, parameter.DataType);
}
OnCommandCompleted(new CommandCompletedEventArgs(DateTime.Now, CommandName, null, null, string.Format("The {0} command has completed.", CommandName), commandParentType));
}
catch (Exception ex)
{
OnCommandFailed(new CommandFailedEventArgs(DateTime.Now, CommandName, null, null, ex.Message, commandParentType));
throw;
}
return(returnValue);
#else
return(null);
#endif
}
public static void BuildSSISPackage(out Microsoft.SqlServer.Dts.Runtime.Package package, out IDTSComponentMetaData100 multipleHash, out CManagedComponentWrapper multipleHashInstance, out String lineageString, out MainPipe dataFlowTask, out Microsoft.SqlServer.Dts.Runtime.Application app)
{
package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
dataFlowTask = thMainPipe.InnerObject as MainPipe;
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
// Create a flat file source
ConnectionManager flatFileConnectionManager = package.Connections.Add("FLATFILE");
flatFileConnectionManager.Properties["Format"].SetValue(flatFileConnectionManager, "Delimited");
flatFileConnectionManager.Properties["Name"].SetValue(flatFileConnectionManager, "Flat File Connection");
flatFileConnectionManager.Properties["ConnectionString"].SetValue(flatFileConnectionManager, @".\TextDataToBeHashed.txt");
flatFileConnectionManager.Properties["ColumnNamesInFirstDataRow"].SetValue(flatFileConnectionManager, false);
flatFileConnectionManager.Properties["HeaderRowDelimiter"].SetValue(flatFileConnectionManager, "\r\n");
flatFileConnectionManager.Properties["TextQualifier"].SetValue(flatFileConnectionManager, "\"");
flatFileConnectionManager.Properties["DataRowsToSkip"].SetValue(flatFileConnectionManager, 0);
flatFileConnectionManager.Properties["Unicode"].SetValue(flatFileConnectionManager, false);
flatFileConnectionManager.Properties["CodePage"].SetValue(flatFileConnectionManager, 1252);
// Create the columns in the flat file
IDTSConnectionManagerFlatFile100 flatFileConnection = flatFileConnectionManager.InnerObject as IDTSConnectionManagerFlatFile100;
IDTSConnectionManagerFlatFileColumn100 StringDataColumn = flatFileConnection.Columns.Add();
StringDataColumn.ColumnDelimiter = ",";
StringDataColumn.ColumnType = "Delimited";
StringDataColumn.DataType = DataType.DT_STR;
StringDataColumn.DataPrecision = 0;
StringDataColumn.DataScale = 0;
StringDataColumn.MaximumWidth = 255;
((IDTSName100)StringDataColumn).Name = "StringData";
IDTSConnectionManagerFlatFileColumn100 MoreStringColumn = flatFileConnection.Columns.Add();
MoreStringColumn.ColumnDelimiter = ",";
MoreStringColumn.ColumnType = "Delimited";
MoreStringColumn.DataType = DataType.DT_STR;
MoreStringColumn.DataPrecision = 0;
MoreStringColumn.DataScale = 0;
MoreStringColumn.MaximumWidth = 255;
((IDTSName100)MoreStringColumn).Name = "MoreString";
IDTSConnectionManagerFlatFileColumn100 DateColumn = flatFileConnection.Columns.Add();
DateColumn.ColumnDelimiter = ",";
DateColumn.ColumnType = "Delimited";
DateColumn.DataType = DataType.DT_DATE;
DateColumn.DataPrecision = 0;
DateColumn.DataScale = 0;
DateColumn.MaximumWidth = 0;
((IDTSName100)DateColumn).Name = "DateColumn";
IDTSConnectionManagerFlatFileColumn100 IntegerColumn = flatFileConnection.Columns.Add();
IntegerColumn.ColumnDelimiter = ",";
IntegerColumn.ColumnType = "Delimited";
IntegerColumn.DataType = DataType.DT_I4;
IntegerColumn.DataPrecision = 0;
IntegerColumn.DataScale = 0;
IntegerColumn.MaximumWidth = 0;
((IDTSName100)IntegerColumn).Name = "IntegerColumn";
IDTSConnectionManagerFlatFileColumn100 NumericColumn = flatFileConnection.Columns.Add();
NumericColumn.ColumnDelimiter = "\r\n";
NumericColumn.ColumnType = "Delimited";
NumericColumn.DataType = DataType.DT_NUMERIC;
NumericColumn.DataPrecision = 15;
NumericColumn.DataScale = 2;
NumericColumn.MaximumWidth = 0;
((IDTSName100)NumericColumn).Name = "NumericColumn";
app = new Microsoft.SqlServer.Dts.Runtime.Application();
IDTSComponentMetaData100 flatFileSource = dataFlowTask.ComponentMetaDataCollection.New();
flatFileSource.ComponentClassID = app.PipelineComponentInfos["Flat File Source"].CreationName;
// Get the design time instance of the Flat File Source Component
var flatFileSourceInstance = flatFileSource.Instantiate();
flatFileSourceInstance.ProvideComponentProperties();
flatFileSource.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(flatFileConnectionManager);
flatFileSource.RuntimeConnectionCollection[0].ConnectionManagerID = flatFileConnectionManager.ID;
// Reinitialize the metadata.
flatFileSourceInstance.AcquireConnections(null);
flatFileSourceInstance.ReinitializeMetaData();
flatFileSourceInstance.ReleaseConnections();
flatFileSource.CustomPropertyCollection["RetainNulls"].Value = true;
//[MD5BinaryOutput] varbinary(16), [MD5HexOutput] varchar(34), [MD5BaseOutput] varchar(24))";
multipleHash = dataFlowTask.ComponentMetaDataCollection.New();
multipleHash.ComponentClassID = typeof(Martin.SQLServer.Dts.MultipleHash).AssemblyQualifiedName;
multipleHashInstance = multipleHash.Instantiate();
multipleHashInstance.ProvideComponentProperties();
multipleHash.Name = "Multiple Hash Test";
multipleHashInstance.ReinitializeMetaData();
// Create the path from source to destination.
StaticTestUtilities.CreatePath(dataFlowTask, flatFileSource.OutputCollection[0], multipleHash, multipleHashInstance);
// Select the input columns.
IDTSInput100 multipleHashInput = multipleHash.InputCollection[0];
IDTSVirtualInput100 multipleHashvInput = multipleHashInput.GetVirtualInput();
foreach (IDTSVirtualInputColumn100 vColumn in multipleHashvInput.VirtualInputColumnCollection)
{
multipleHashInstance.SetUsageType(multipleHashInput.ID, multipleHashvInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
// Add the output columns
// Generate the Lineage String
lineageString = String.Empty;
foreach (IDTSInputColumn100 inputColumn in multipleHashInput.InputColumnCollection)
{
if (lineageString == String.Empty)
{
lineageString = String.Format("#{0}", inputColumn.LineageID);
}
else
{
lineageString = String.Format("{0},#{1}", lineageString, inputColumn.LineageID);
}
}
}
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();
}
public ActionResult DownloadBatchOverviewReport(String _TemplateKey, String _1stBatchKey, String _2ndBatchKey, String _BatchTypeKey, String _MaterialKey, String StartTime, String EndTime, bool CreateLogFile = false)
{
const string FILENAME_PREFIX = "BatchOverview_";
//const string FILENAME_PREFIX = "BatchOverview";
const string FILENAME_TYPE = "xlsx";
const string EXCEL_TEMPLATE_NAME = "ExcelReport_BatchOverview_Template.xlsx";
const string SSIS_PACKAGE = "ExcelReport_BatchOverview.dtsx";
const string FOLDER_BASE_SOURCE = "Areas\\PlantiT.Web.HeinekenMassafra.MES\\Excel";
const string FOLDER_BIN = "bin";
const string FOLDER_TEMPLATE = "template";
const string FOLDER_BASE_OUTPUT = "Areas\\PlantiT.Web.HeinekenMassafra.MES\\Excel\\output";
//const string FOLDER_BASE_OUTPUT = "Areas\\PlantiT.Web.HeinekenMassafra.MES\\Excel\\output\\";
const string LOG_FILE_NAME = "DownloadBatchOverview_Log.txt";
const int WAITTIME_IN_MS = 1000;
const int MAXWAITTIME_IN_MS = 30000;
String sLocation = Assembly.GetExecutingAssembly().Location; // Get current directory
Logger logger = null;
long nTemplateKey = 0;
long n1stBatchKey = 0;
long n2ndBatchKey = 0;
long nBatchTypeKey = 0;
long nMaterialKey = 0;
DateTime dtStartTime = DateTime.MaxValue;
DateTime dtEndTime = DateTime.MinValue;
string sSourcePath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, FOLDER_BASE_SOURCE);
string sDestinationPath = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, FOLDER_BASE_OUTPUT);
string sDestinationFilePath = String.Empty;
string sFileName = String.Empty;
DateTime dtCheckTime = DateTime.Now;
// Log generieren
if (CreateLogFile)
{
logger = new Logger(Path.Combine(sDestinationPath, LOG_FILE_NAME), true);
}
// Parameter ermitteln
// TemplateKey
try
{
nTemplateKey = Convert.ToInt64(_TemplateKey);
}
catch { }
// 1stBatchKey
try
{
n1stBatchKey = Convert.ToInt64(_1stBatchKey);
}
catch { }
// 2ndBatchKey
try
{
n2ndBatchKey = Convert.ToInt64(_2ndBatchKey);
}
catch { }
// BatchTypeKey
try
{
nBatchTypeKey = Convert.ToInt64(_BatchTypeKey);
}
catch { }
// MaterialKey
try
{
nMaterialKey = Convert.ToInt64(_MaterialKey);
}
catch { }
// StartTime
try
{
dtStartTime = Convert.ToDateTime(StartTime);
}
catch { }
// EndTime
try
{
dtEndTime = Convert.ToDateTime(EndTime);
}
catch { }
// Nach Datei suchen
sFileName = FILENAME_PREFIX + DateTime.Now.ToString().Replace(":", "").Replace('/', '.').Replace('-', '.');
//sFileName = FILENAME_PREFIX;
Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Microsoft.SqlServer.Dts.Runtime.Package package = null;
MyEventListener eventListener = new MyEventListener();
//Load the SSIS Package which will be executed
app.PackagePassword = "******";
String sPackageFilePath = Path.Combine(sSourcePath, FOLDER_BIN, SSIS_PACKAGE);
// Execute
try
{
// Load package
package = app.LoadPackage(sPackageFilePath, eventListener);
// Set values
Variables vars = package.Variables;
vars["User::InputTemplateKey"].Value = nTemplateKey;
vars["User::Input1stBatchKey"].Value = n1stBatchKey;
vars["User::Input2ndBatchKey"].Value = n2ndBatchKey;
vars["User::InputBatchTypeKey"].Value = nBatchTypeKey;
vars["User::InputMaterialKey"].Value = nMaterialKey;
vars["User::InputStartTime"].Value = dtStartTime;
vars["User::InputEndTime"].Value = dtEndTime;
vars["User::InputDestinationFile"].Value = sFileName + "." + FILENAME_TYPE;
vars["User::InputDestinationPath"].Value = sDestinationPath;
vars["User::InputTemplateFilePath"].Value = Path.Combine(sSourcePath, FOLDER_TEMPLATE, EXCEL_TEMPLATE_NAME);
vars["User::InputCreateLogFile"].Value = CreateLogFile;
vars["User::InputLogFilePath"].Value = (logger != null) ? logger.FilePath : String.Empty;
// Log erzeugen
if (logger != null)
{
logger.Log("InputTemplateKey", vars["User::InputTemplateKey"].Value.ToString());
logger.Log("Input1stBatchKey", vars["User::Input1stBatchKey"].Value.ToString());
logger.Log("Input2ndBatchKey", vars["User::Input2ndBatchKey"].Value.ToString());
logger.Log("InputBatchTypeKey", vars["User::InputBatchTypeKey"].Value.ToString());
logger.Log("InputMaterialKey", vars["User::InputMaterialKey"].Value.ToString());
logger.Log("InputStartTime", vars["User::InputStartTime"].Value.ToString());
logger.Log("InputEndTime", vars["User::InputEndTime"].Value.ToString());
logger.Log("InputDestinationFile", vars["User::InputDestinationFile"].Value.ToString());
logger.Log("InputDestinationPath", vars["User::InputDestinationPath"].Value.ToString());
logger.Log("InputTemplateFilePath", vars["User::InputTemplateFilePath"].Value.ToString());
}
vars["InputToOverwrite"].Value = true;
DTSExecResult results = package.Execute(null, null, eventListener, null, null);
}
catch (Exception ex)
{
if (logger != null)
{
logger.Log("Error", String.Format("Error: {0} // Innermessage: {1}", ex.Message, (ex.InnerException != null) ? ex.InnerException.Message : String.Empty));
}
return(new HttpNotFoundResult(String.Format("Error: {0} // Innermessage: {1}", ex.Message, (ex.InnerException != null) ? ex.InnerException.Message : String.Empty)));
}
// zyklisch prüfen ob Datei vorhanden ist, maximal MAXWAITTIME_IN_MS
for (int i = 0; i < MAXWAITTIME_IN_MS; i += WAITTIME_IN_MS)
{
if (package.Errors.Count > 0)
{
foreach (var item in package.Errors)
{
if (logger != null)
{
logger.Log("Error", item.Description);
}
}
break;
}
// erstmal eine Sekunde zeit geben, zyklisch prüfen
System.Threading.Thread.Sleep(WAITTIME_IN_MS);
// alle Dateien des Pfades holen die dem Suchmuster entsprechen
DirectoryInfo dirInfo = new DirectoryInfo(Path.Combine(sDestinationPath));
var filesInPath = dirInfo.EnumerateFiles(String.Format("{0}*.{1}", sFileName, FILENAME_TYPE));
var file = filesInPath.Where(x => x.Name.Contains(sFileName)).OrderByDescending(x => x.LastWriteTime).FirstOrDefault();
// nur welche die eben erstellt wurde
if (file == null)
{
continue;
}
// Falls eben erstellt...
if (file.LastWriteTime >= dtCheckTime)
{
// gefunden, Abbruch
sFileName = file.Name;
break;
}
}
// Pfad mit Datei erzeugen
sDestinationFilePath = Path.Combine(sDestinationPath, sFileName);
// Datei vorhanden?
if (!System.IO.File.Exists(sDestinationFilePath))
{
return(new HttpNotFoundResult("Excel file could not created!"));
}
// Create data return stream
byte[] fileData = System.IO.File.ReadAllBytes(sDestinationFilePath);
string contentType = System.Web.MimeMapping.GetMimeMapping(sDestinationFilePath);
// Datei entfernen
System.IO.File.Delete(sDestinationFilePath);
var cd = new System.Net.Mime.ContentDisposition()
{
// for example foo.bak
FileName = sFileName,
// always prompt the user for downloading, set to true if you want
// the browser to try to show the file inline
Inline = false,
};
Response.AppendHeader("Content-Disposition", cd.ToString());
return(File(fileData, contentType));
}
public static void BuildSSISPackage(out Microsoft.SqlServer.Dts.Runtime.Package package, out IDTSComponentMetaData100 multipleHash, out CManagedComponentWrapper multipleHashInstance, out String lineageString, out MainPipe dataFlowTask)
{
Microsoft.SqlServer.Dts.Runtime.Application app;
BuildSSISPackage(out package, out multipleHash, out multipleHashInstance, out lineageString, out dataFlowTask, out app);
}
public void TestProvideComponentProperties()
{
Microsoft.SqlServer.Dts.Runtime.Package package = new Microsoft.SqlServer.Dts.Runtime.Package();
Executable exec = package.Executables.Add("STOCK:PipelineTask");
Microsoft.SqlServer.Dts.Runtime.TaskHost thMainPipe = exec as Microsoft.SqlServer.Dts.Runtime.TaskHost;
MainPipe dataFlowTask = thMainPipe.InnerObject as MainPipe;
ComponentEventHandler events = new ComponentEventHandler();
dataFlowTask.Events = DtsConvert.GetExtendedInterface(events as IDTSComponentEvents);
IDTSComponentMetaData100 speechToText = dataFlowTask.ComponentMetaDataCollection.New();
speechToText.ComponentClassID = typeof(Martin.SQLServer.Dts.SSISSpeechToText).AssemblyQualifiedName;
CManagedComponentWrapper speechToTextInstance = speechToText.Instantiate();
speechToTextInstance.ProvideComponentProperties();
int intExpected = 7; // Add 1 to the number that the component added, as there is a custom MS one as well.
int intActual = speechToText.CustomPropertyCollection.Count;
Assert.AreEqual(intExpected, intActual, "Custom Property Collection Count is wrong");
IDTSCustomProperty100 cpActual = speechToText.CustomPropertyCollection[Utility.SubscriptionKeyPropName];
Assert.AreEqual(Utility.SubscriptionKeyPropName, cpActual.Name, "Property name is wrong");
Assert.AreEqual(DTSCustomPropertyExpressionType.CPET_NOTIFY, cpActual.ExpressionType, "Expression type on SubscriptionKeyPropName is wrong");
cpActual = speechToText.CustomPropertyCollection[Utility.OperationModePropName];
Assert.AreEqual(Utility.OperationModePropName, cpActual.Name, "Property name is wrong");
Assert.AreEqual(DTSCustomPropertyExpressionType.CPET_NONE, cpActual.ExpressionType, "Expression type on OperationModePropName is wrong");
Assert.AreEqual(typeof(SSISSpeechToText.OperationModeEnum).AssemblyQualifiedName, cpActual.TypeConverter, "Type Converter on OperationModePropName is wrong");
cpActual = speechToText.CustomPropertyCollection[Utility.LanguagePropName];
Assert.AreEqual(cpActual.Name, Utility.LanguagePropName, "Property name is wrong");
Assert.AreEqual(cpActual.ExpressionType, DTSCustomPropertyExpressionType.CPET_NONE, "Expression type on LanguagePropName is wrong");
Assert.AreEqual(cpActual.TypeConverter, typeof(SSISSpeechToText.SpeechLanguageEnum).AssemblyQualifiedName, "Type Converter on LanguagePropName is wrong");
cpActual = speechToText.CustomPropertyCollection[Utility.ChannelSeparationPropName];
Assert.AreEqual(cpActual.Name, Utility.ChannelSeparationPropName, "Property name is wrong");
Assert.AreEqual(cpActual.ExpressionType, DTSCustomPropertyExpressionType.CPET_NONE, "Expression type on ChannelSeparationPropName is wrong");
Assert.AreEqual(cpActual.TypeConverter, typeof(SSISSpeechToText.ChannelSeparationEnum).AssemblyQualifiedName, "Type Converter on ChannelSeparationPropName is wrong");
cpActual = speechToText.CustomPropertyCollection[Utility.ShortPhraseUrlPropName];
Assert.AreEqual(Utility.ShortPhraseUrlPropName, cpActual.Name, "Property name is wrong");
Assert.AreEqual(DTSCustomPropertyExpressionType.CPET_NOTIFY, cpActual.ExpressionType, "Expression type on ShortPhraseUrlPropName is wrong");
Assert.AreEqual(string.Empty, cpActual.TypeConverter, "Type Converter on AuthenticationUriPropName is wrong");
cpActual = speechToText.CustomPropertyCollection[Utility.LongPhraseUrlPropName];
Assert.AreEqual(Utility.LongPhraseUrlPropName, cpActual.Name, "Property name is wrong");
Assert.AreEqual(DTSCustomPropertyExpressionType.CPET_NOTIFY, cpActual.ExpressionType, "Expression type on LongPhraseUrlPropName is wrong");
Assert.AreEqual(string.Empty, cpActual.TypeConverter, "Type Converter on AuthenticationUriPropName is wrong");
intExpected = 1;
intActual = speechToText.InputCollection.Count;
Assert.AreEqual(intExpected, intActual, "Input Collection Count is wrong");
Assert.AreEqual(speechToText.InputCollection[0].Name, "Input", "Input Name is Wrong");
Assert.AreEqual(speechToText.InputCollection[0].InputColumnCollection.Count, 0, "There are input columns");
intExpected = 1;
intActual = speechToText.OutputCollection.Count;
Assert.AreEqual(intExpected, intActual, "Output Collection Count is wrong");
Assert.AreEqual(speechToText.OutputCollection[0].Name, "SpeechOutput", "Output Name is Wrong");
Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection.Count, 3, "The number of output columns is wrong");
Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputChannelColumnName].Name, Utility.OutputChannelColumnName, "Output column name is wrong");
Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputChannelColumnName].DataType, DataType.DT_STR, "Output column OutputSpeechColumnName datatype is wrong");
Assert.AreEqual((SSISSpeechToText.OutputTypeEnum)speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputChannelColumnName].CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value, SSISSpeechToText.OutputTypeEnum.Channel, "Output column OutputChannelColumnName purpose is wrong");
Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputSpeechColumnName].Name, Utility.OutputSpeechColumnName, "Output column name is wrong");
Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputSpeechColumnName].DataType, DataType.DT_TEXT, "Output column OutputSpeechColumnName datatype is wrong");
Assert.AreEqual((SSISSpeechToText.OutputTypeEnum)speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputSpeechColumnName].CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value, SSISSpeechToText.OutputTypeEnum.Speech, "Output column OutputSpeechColumnName purpose is wrong");
Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputTimecodeColumnName].Name, Utility.OutputTimecodeColumnName, "Output column name is wrong");
Assert.AreEqual(speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputTimecodeColumnName].DataType, DataType.DT_DBTIME2, "Output column OutputTimecodeColumnName datatype is wrong");
Assert.AreEqual((SSISSpeechToText.OutputTypeEnum)speechToText.OutputCollection[0].OutputColumnCollection[Utility.OutputTimecodeColumnName].CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value, SSISSpeechToText.OutputTypeEnum.Timecode, "Output column OutputTimecodeColumnName purpose is wrong");
}
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 override object Execute(Package package, DtsContainer container)
{
return(Execute((object)null, package, container));
}
