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...."); } }
public void Execute() { if (DtsExecutable != null) { var errorHandler = new ErrorEvents(); DTS.DTSExecResult execResult = DtsExecutable.Execute(null, null, errorHandler, null, null); if (execResult != Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success || errorHandler.ValidationErrorCount > 0) { MessageEngine.Trace(AstNamedNode, Severity.Warning, "V0108", "Failed to execute {0} during compilation", Name); } } }
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); }
protected void SafeExecute(DTS.Executable executable) { Vulcan.Common.ErrorEvents errorHandler = new Vulcan.Common.ErrorEvents(); DTS.DTSExecResult execResult = executable.Execute(null, null, errorHandler, null, null); if (execResult == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success && errorHandler.ValidationErrorCount == 0) { Message.Trace( Severity.Notification, "Success executing Task {0}", VulcanPackage.ExtractNameFromDTSExecutable(executable) ); } else { Message.Trace( Severity.Error, "Error executing Task {0}: Result = {1} (BugBug: SSIS always returns success) but ErrorCount = {2}", VulcanPackage.ExtractNameFromDTSExecutable(executable), execResult, errorHandler.ValidationErrorCount ); } }
public void Execute() { if (_executable != null) { MessageEngine.Global.Trace(Severity.Alert, "Executing DTS Package {0}", Name); ErrorEvents errorHandler = new ErrorEvents(); DTS.DTSExecResult execResult = _executable.Execute(null, null, errorHandler, null, null); if (execResult == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success && errorHandler.ValidationErrorCount == 0) { MessageEngine.Global.Trace(Severity.Notification, "Success executing Task {0}", Name); } else { MessageEngine.Global.Trace( Severity.Error, "Error executing Task {0}: Result = {1} (BugBug: SSIS always returns success) but ErrorCount = {2}", Name, execResult, errorHandler.ValidationErrorCount ); } } }
public void AutoThreadingTest() { Microsoft.SqlServer.Dts.Runtime.Package package; IDTSComponentMetaData100 multipleHash; CManagedComponentWrapper multipleHashInstance; String lineageString; MainPipe dataFlowTask; // Microsoft.SqlServer.Dts.Runtime.Application app; StaticTestUtilities.BuildSSISPackage(out package, out multipleHash, out multipleHashInstance, out lineageString, out dataFlowTask /*, out app */); multipleHash.CustomPropertyCollection[Utility.MultipleThreadPropName].Value = MultipleHash.MultipleThread.Auto; int outputID = multipleHash.OutputCollection[0].ID; int outputColumnPos = multipleHash.OutputCollection[0].OutputColumnCollection.Count; // Add output column SHA1BinaryOutput (SHA1, Binary) IDTSOutputColumn100 SHA1BinaryOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "SHA1BinaryOutput", "SHA1 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); SHA1BinaryOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Binary; SHA1BinaryOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.SHA1; SHA1BinaryOutput.Name = "SHA1BinaryOutput"; SHA1BinaryOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.SHA1, MultipleHash.OutputTypeEnumerator.Binary, SHA1BinaryOutput); // Add output column SHA1HexOutput (SHA1, HexString) IDTSOutputColumn100 SHA1HexOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "SHA1HexOutput", "SHA1 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); SHA1HexOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.HexString; SHA1HexOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.SHA1; SHA1HexOutput.Name = "SHA1HexOutput"; SHA1HexOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.SHA1, MultipleHash.OutputTypeEnumerator.HexString, SHA1HexOutput); // Add output column SHA1BaseOutput (SHA1, Base64String) IDTSOutputColumn100 SHA1BaseOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "SHA1BaseOutput", "SHA1 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); SHA1BaseOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Base64String; SHA1BaseOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.SHA1; SHA1BaseOutput.Name = "SHA1BaseOutput"; SHA1BaseOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.SHA1, MultipleHash.OutputTypeEnumerator.Base64String, SHA1BaseOutput); // Add SQL CE Destination // Add SQL CE Connection ConnectionManager sqlCECM = null; IDTSComponentMetaData100 sqlCETarget = null; CManagedComponentWrapper sqlCEInstance = null; StaticTestUtilities.CreateSQLCEComponent(package, dataFlowTask, sqlCEDatabaseName, sqlCEPassword, "TestRecords", out sqlCECM, out sqlCETarget, out sqlCEInstance); StaticTestUtilities.CreatePath(dataFlowTask, multipleHash.OutputCollection[0], sqlCETarget, sqlCEInstance); // Create a package events handler, to catch the output when running. PackageEventHandler packageEvents = new PackageEventHandler(); // Execute the package Microsoft.SqlServer.Dts.Runtime.DTSExecResult result = package.Execute(null, null, packageEvents as IDTSEvents, null, null); foreach (String message in packageEvents.eventMessages) { Debug.WriteLine(message); } // Make sure the package worked. Assert.AreEqual(Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, result, "Execution Failed"); // Connect to the SQLCE database SqlCeConnection connection = new SqlCeConnection(StaticTestUtilities.connectionString(sqlCEDatabaseName, sqlCEPassword)); try { if (connection.State == ConnectionState.Closed) { connection.Open(); } SqlCeCommand sqlCommand = new SqlCeCommand("SELECT * FROM [TestRecords] ORDER BY [StringData]", connection); SqlCeDataReader sqlData = sqlCommand.ExecuteReader(CommandBehavior.Default); int rowCount = 0; while (sqlData.Read()) { rowCount++; switch (rowCount) { case 1: StaticTestUtilities.testValues20("SHA1", sqlData, "NullRow", null, "7404459d2254bfcd2d55cddf90fb29c751012310", "dARFnSJUv80tVc3fkPspx1EBIxA="); break; case 2: StaticTestUtilities.testValues20("SHA1", sqlData, "StringData1", "MoreStringData1", "a007b9d8891448bf08ab2e87dc36cc4fadc7ec94", "oAe52IkUSL8Iqy6H3DbMT63H7JQ="); break; case 3: StaticTestUtilities.testValues20("SHA1", sqlData, "StringData2", "MoreStringData2", "b7e5cdd69e366cabd2ecbabe23e2e270cf1d908e", "t+XN1p42bKvS7Lq+I+LicM8dkI4="); break; case 4: StaticTestUtilities.testValues20("SHA1", sqlData, "StringData3", "MoreStringData3", "4b135b551bcb7383c6a3c3151db51ab5862a0a65", "SxNbVRvLc4PGo8MVHbUatYYqCmU="); break; case 5: StaticTestUtilities.testValues20("SHA1", sqlData, "StringData4", "MoreStringData4", "053d62760fea89724505e4f15fbc7d299cf6423f", "BT1idg/qiXJFBeTxX7x9KZz2Qj8="); break; default: Assert.Fail(string.Format("Account has to many records AccountCode {0}, AccountName {1}", sqlData.GetInt32(1), sqlData.GetString(2))); break; } } Assert.AreEqual(5, rowCount, "Rows in TestRecords"); } finally { if (connection != null) { connection.Close(); connection.Dispose(); } } }
public void CalculateHashMurmurHash3aTest() { Microsoft.SqlServer.Dts.Runtime.Package package; IDTSComponentMetaData100 multipleHash; CManagedComponentWrapper multipleHashInstance; String lineageString; MainPipe dataFlowTask; // Microsoft.SqlServer.Dts.Runtime.Application app; StaticTestUtilities.BuildSSISPackage(out package, out multipleHash, out multipleHashInstance, out lineageString, out dataFlowTask /*, out app */); int outputID = multipleHash.OutputCollection[0].ID; int outputColumnPos = multipleHash.OutputCollection[0].OutputColumnCollection.Count; // Add output column Murmur3aBinaryOutput (Murmur3a, Binary) IDTSOutputColumn100 Murmur3aBinaryOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "Murmur3aBinaryOutput", "Murmur3a Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); Murmur3aBinaryOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Binary; Murmur3aBinaryOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.MurmurHash3a; Murmur3aBinaryOutput.Name = "Murmur3aBinaryOutput"; Murmur3aBinaryOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; // Add output column Murmur3aHexOutput (Murmur3a, HexString) IDTSOutputColumn100 Murmur3aHexOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "Murmur3aHexOutput", "Murmur3a Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); Murmur3aHexOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.HexString; Murmur3aHexOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.MurmurHash3a; Murmur3aHexOutput.Name = "Murmur3aHexOutput"; Murmur3aHexOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.MurmurHash3a, MultipleHash.OutputTypeEnumerator.HexString, Murmur3aHexOutput); // Add output column Murmur3aBaseOutput (Murmur3a, Base64String) IDTSOutputColumn100 Murmur3aBaseOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "Murmur3aBaseOutput", "Murmur3a Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); Murmur3aBaseOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Base64String; Murmur3aBaseOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.MurmurHash3a; Murmur3aBaseOutput.Name = "Murmur3aBaseOutput"; Murmur3aBaseOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.MurmurHash3a, MultipleHash.OutputTypeEnumerator.Base64String, Murmur3aBaseOutput); // Add SQL CE Connection ConnectionManager sqlCECM = null; IDTSComponentMetaData100 sqlCETarget = null; CManagedComponentWrapper sqlCEInstance = null; StaticTestUtilities.CreateSQLCEComponent(package, dataFlowTask, sqlCEDatabaseName, sqlCEPassword, "TestRecordsMurmur3a", out sqlCECM, out sqlCETarget, out sqlCEInstance); StaticTestUtilities.CreatePath(dataFlowTask, multipleHash.OutputCollection[0], sqlCETarget, sqlCEInstance); // Uncomment the following line if you want to save the DTSX package. // Although you will have to add app to the BuildSSISPackage as an out to make this work. // app.SaveToXml(@".\test.dtsx", package, null); // Create a package events handler, to catch the output when running. PackageEventHandler packageEvents = new PackageEventHandler(); // Execute the package Microsoft.SqlServer.Dts.Runtime.DTSExecResult result = package.Execute(null, null, packageEvents as IDTSEvents, null, null); foreach (String message in packageEvents.eventMessages) { Debug.WriteLine(message); } // Make sure the package worked. Assert.AreEqual(Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, result, "Execution Failed"); // Connect to the SQLCE database SqlCeConnection connection = new SqlCeConnection(StaticTestUtilities.connectionString(sqlCEDatabaseName, sqlCEPassword)); try { if (connection.State == ConnectionState.Closed) { connection.Open(); } SqlCeCommand sqlCommand = new SqlCeCommand("SELECT * FROM [TestRecordsMurmur3a] ORDER BY [StringData]", connection); SqlCeDataReader sqlData = sqlCommand.ExecuteReader(CommandBehavior.Default); int rowCount = 0; while (sqlData.Read()) { rowCount++; switch (rowCount) { case 1: StaticTestUtilities.testValues16("Murmur3a", sqlData, "NullRow", null, "a6b1e7adea05d62eee4a69b75bb6fa0f", "prHnreoF1i7uSmm3W7b6Dw=="); break; case 2: StaticTestUtilities.testValues16("Murmur3a", sqlData, "StringData1", "MoreStringData1", "dd8bc7d17663e60762a651a0cf9c4587", "3YvH0XZj5gdiplGgz5xFhw=="); break; case 3: StaticTestUtilities.testValues16("Murmur3a", sqlData, "StringData2", "MoreStringData2", "a9a7e8b837da471597612542cb991d60", "qafouDfaRxWXYSVCy5kdYA=="); break; case 4: StaticTestUtilities.testValues16("Murmur3a", sqlData, "StringData3", "MoreStringData3", "c7b26b54eae2b2b3bbc6517442f87850", "x7JrVOrisrO7xlF0Qvh4UA=="); break; case 5: StaticTestUtilities.testValues16("Murmur3a", sqlData, "StringData4", "MoreStringData4", "52e3a27aa7a40b6bf9f7e12ffdbdab6d", "UuOieqekC2v59+Ev/b2rbQ=="); break; default: Assert.Fail(string.Format("Account has to many records AccountCode {0}, AccountName {1}", sqlData.GetInt32(1), sqlData.GetString(2))); break; } } Assert.AreEqual(5, rowCount, "Rows in TestRecords"); } finally { if (connection != null) { connection.Close(); connection.Dispose(); } } }
private void InitializeTasks() { long a0 = CensusPackage.CertificateContext; X509Certificate a1 = CensusPackage.CertificateObject; bool a2 = CensusPackage.CheckSignatureOnLoad; string a3 = CensusPackage.CheckpointFileName; DTSCheckpointUsage a4 = CensusPackage.CheckpointUsage; Configurations a5 = CensusPackage.Configurations; DateTime a6 = CensusPackage.CreationDate; string a7 = CensusPackage.CreatorComputerName; string a8 = CensusPackage.CreatorName; Connections a9 = CensusPackage.Connections; IDTSEvents b0 = CensusPackage.DesignEvents; string b1 = CensusPackage.DesignTimeProperties; string b2 = CensusPackage.DumpDescriptor; bool b3 = CensusPackage.DumpOnAnyError; bool b4 = CensusPackage.EnableConfigurations; bool b5 = CensusPackage.EnableDump; bool b6 = CensusPackage.EncryptCheckpoints; DtsErrors b7 = CensusPackage.Errors; Executables b8 = CensusPackage.Executables; ExtendedProperties b9 = CensusPackage.ExtendedProperties; bool c0 = CensusPackage.FailPackageOnFailure; bool c1 = CensusPackage.HasExpressions; bool c2 = CensusPackage.IgnoreConfigurationsOnLoad; bool c3 = CensusPackage.InteractiveMode; LogProviders c4 = CensusPackage.LogProviders; int c5 = CensusPackage.MaxConcurrentExecutables; bool c6 = CensusPackage.OfflineMode; //var c7 = CensusPackage.PackagePassword; DTSPriorityClass c8 = CensusPackage.PackagePriorityClass; DTSPackageType c9 = CensusPackage.PackageType; PackageUpgradeOptions d0 = CensusPackage.PackageUpgradeOptions; Parameters d1 = CensusPackage.Parameters; PrecedenceConstraints d2 = CensusPackage.PrecedenceConstraints; IDTSProject100 d3 = CensusPackage.Project; DtsProperties d4 = CensusPackage.Properties; DTSProtectionLevel d5 = CensusPackage.ProtectionLevel; bool d6 = CensusPackage.SafeRecursiveProjectPackageExecution; bool d7 = CensusPackage.SaveCheckpoints; bool d8 = CensusPackage.SuppressConfigurationWarnings; bool d9 = CensusPackage.UpdateObjects; int e0 = CensusPackage.VersionBuild; string e1 = CensusPackage.VersionComments; string e2 = CensusPackage.VersionGUID; int e3 = CensusPackage.VersionMajor; int e4 = CensusPackage.VersionMinor; DtsWarnings e5 = CensusPackage.Warnings; string e6 = CensusPackage.CreationName; bool e7 = CensusPackage.DebugMode; bool e8 = CensusPackage.DelayValidation; string e9 = CensusPackage.Description; bool f0 = CensusPackage.Disable; bool f1 = CensusPackage.DisableEventHandlers; DtsEventHandlers f2 = CensusPackage.EventHandlers; EventInfos f3 = CensusPackage.EventInfos; int f4 = CensusPackage.ExecutionDuration; DTSExecResult f5 = CensusPackage.ExecutionResult; DTSExecStatus f6 = CensusPackage.ExecutionStatus; bool f7 = CensusPackage.FailParentOnFailure; bool f8 = CensusPackage.FailParentOnFailure; Variables f9 = CensusPackage.Variables; VariableDispenser g0 = CensusPackage.VariableDispenser; DTSTransactionOption g1 = CensusPackage.TransactionOption; bool g2 = CensusPackage.SuspendRequired; DateTime g3 = CensusPackage.StopTime; DateTime g4 = CensusPackage.StartTime; ISite g5 = CensusPackage.Site; DtsContainer g6 = CensusPackage.Parent; string g7 = CensusPackage.Name; int g8 = CensusPackage.MaximumErrorCount; LoggingOptions g9 = CensusPackage.LoggingOptions; DTSLoggingMode h0 = CensusPackage.LoggingMode; LogEntryInfos h1 = CensusPackage.LogEntryInfos; int h2 = CensusPackage.LocaleID; IsolationLevel h3 = CensusPackage.IsolationLevel; bool h4 = CensusPackage.IsDefaultLocaleID; string h5 = CensusPackage.ID; object h6 = CensusPackage.ForcedExecutionValue; bool h7 = CensusPackage.ForceExecutionValue; DTSForcedExecResult h8 = CensusPackage.ForceExecutionResult; Console.WriteLine(); }
public void CalculateHashSHA384Test() { Microsoft.SqlServer.Dts.Runtime.Package package; IDTSComponentMetaData100 multipleHash; CManagedComponentWrapper multipleHashInstance; String lineageString; MainPipe dataFlowTask; // Microsoft.SqlServer.Dts.Runtime.Application app; StaticTestUtilities.BuildSSISPackage(out package, out multipleHash, out multipleHashInstance, out lineageString, out dataFlowTask /*, out app */); int outputID = multipleHash.OutputCollection[0].ID; int outputColumnPos = multipleHash.OutputCollection[0].OutputColumnCollection.Count; // Add output column SHA384BinaryOutput (SHA384, Binary) IDTSOutputColumn100 SHA384BinaryOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "SHA384BinaryOutput", "SHA384 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); SHA384BinaryOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Binary; SHA384BinaryOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.SHA384; SHA384BinaryOutput.Name = "SHA384BinaryOutput"; SHA384BinaryOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.SHA384, MultipleHash.OutputTypeEnumerator.Binary, SHA384BinaryOutput); // Add output column SHA384HexOutput (SHA384, HexString) IDTSOutputColumn100 SHA384HexOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "SHA384HexOutput", "SHA384 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); SHA384HexOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.HexString; SHA384HexOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.SHA384; SHA384HexOutput.Name = "SHA384HexOutput"; SHA384HexOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.SHA384, MultipleHash.OutputTypeEnumerator.HexString, SHA384HexOutput); // Add output column SHA384BaseOutput (SHA384, Base64String) IDTSOutputColumn100 SHA384BaseOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "SHA384BaseOutput", "SHA384 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); SHA384BaseOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Base64String; SHA384BaseOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.SHA384; SHA384BaseOutput.Name = "SHA384BaseOutput"; SHA384BaseOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.SHA384, MultipleHash.OutputTypeEnumerator.Base64String, SHA384BaseOutput); // Add SQL CE Destination // Add SQL CE Connection ConnectionManager sqlCECM = null; IDTSComponentMetaData100 sqlCETarget = null; CManagedComponentWrapper sqlCEInstance = null; StaticTestUtilities.CreateSQLCEComponent(package, dataFlowTask, sqlCEDatabaseName, sqlCEPassword, "TestRecords", out sqlCECM, out sqlCETarget, out sqlCEInstance); StaticTestUtilities.CreatePath(dataFlowTask, multipleHash.OutputCollection[0], sqlCETarget, sqlCEInstance); // Create a package events handler, to catch the output when running. PackageEventHandler packageEvents = new PackageEventHandler(); // Execute the package Microsoft.SqlServer.Dts.Runtime.DTSExecResult result = package.Execute(null, null, packageEvents as IDTSEvents, null, null); foreach (String message in packageEvents.eventMessages) { Debug.WriteLine(message); } // Make sure the package worked. Assert.AreEqual(Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, result, "Execution Failed"); // Connect to the SQLCE database SqlCeConnection connection = new SqlCeConnection(StaticTestUtilities.connectionString(sqlCEDatabaseName, sqlCEPassword)); try { if (connection.State == ConnectionState.Closed) { connection.Open(); } SqlCeCommand sqlCommand = new SqlCeCommand("SELECT * FROM [TestRecords] ORDER BY [StringData]", connection); SqlCeDataReader sqlData = sqlCommand.ExecuteReader(CommandBehavior.Default); int rowCount = 0; while (sqlData.Read()) { rowCount++; switch (rowCount) { case 1: StaticTestUtilities.testValues48("SHA384", sqlData, "NullRow", null, "08b53cabec16f989febb7594542989474cc68bc88f5ef25ef3d3eaa5c587c5bdc54dc88f6c2af8dab2dd496d66c5f4aa", "CLU8q+wW+Yn+u3WUVCmJR0zGi8iPXvJe89PqpcWHxb3FTciPbCr42rLdSW1mxfSq"); break; case 2: StaticTestUtilities.testValues48("SHA384", sqlData, "StringData1", "MoreStringData1", "432aad9910a1232a4699f8d998211dd74ace4dc63cc4617e2c02c6d5f582c22f2ce829e81237ec3b2b261c943dd24deb", "QyqtmRChIypGmfjZmCEd10rOTcY8xGF+LALG1fWCwi8s6CnoEjfsOysmHJQ90k3r"); break; case 3: StaticTestUtilities.testValues48("SHA384", sqlData, "StringData2", "MoreStringData2", "7e0022b353057d6596a499021300f6f08a82d0dbe36b3f413c0f08b5ba0b740b8fd91d1860e81720487eb624952cd15d", "fgAis1MFfWWWpJkCEwD28IqC0Nvjaz9BPA8ItboLdAuP2R0YYOgXIEh+tiSVLNFd"); break; case 4: StaticTestUtilities.testValues48("SHA384", sqlData, "StringData3", "MoreStringData3", "bbeb7ffe8289295e0cd0a3ed7b75ea03b9e7e25116da56569f1e85b2dd30f42903cd0e6b8cf33c3c6690bb1a105b8393", "u+t//oKJKV4M0KPte3XqA7nn4lEW2lZWnx6Fst0w9CkDzQ5rjPM8PGaQuxoQW4OT"); break; case 5: StaticTestUtilities.testValues48("SHA384", sqlData, "StringData4", "MoreStringData4", "f0adce5888a63f94b69551b13e612e165eaa25bafa768faf8fc0221bfd906c3eded45ed838f3c095c5a3ce962cc8b32f", "8K3OWIimP5S2lVGxPmEuFl6qJbr6do+vj8AiG/2QbD7e1F7YOPPAlcWjzpYsyLMv"); break; default: Assert.Fail(string.Format("Account has to many records AccountCode {0}, AccountName {1}", sqlData.GetInt32(1), sqlData.GetString(2))); break; } } Assert.AreEqual(5, rowCount, "Rows in TestRecords"); } finally { if (connection != null) { connection.Close(); connection.Dispose(); } } }
public void CalculateHashSHA512Test() { Microsoft.SqlServer.Dts.Runtime.Package package; IDTSComponentMetaData100 multipleHash; CManagedComponentWrapper multipleHashInstance; String lineageString; MainPipe dataFlowTask; // Microsoft.SqlServer.Dts.Runtime.Application app; StaticTestUtilities.BuildSSISPackage(out package, out multipleHash, out multipleHashInstance, out lineageString, out dataFlowTask /*, out app */); int outputID = multipleHash.OutputCollection[0].ID; int outputColumnPos = multipleHash.OutputCollection[0].OutputColumnCollection.Count; // Add output column SHA512BinaryOutput (SHA512, Binary) IDTSOutputColumn100 SHA512BinaryOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "SHA512BinaryOutput", "SHA512 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); SHA512BinaryOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Binary; SHA512BinaryOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.SHA512; SHA512BinaryOutput.Name = "SHA512BinaryOutput"; SHA512BinaryOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.SHA512, MultipleHash.OutputTypeEnumerator.Binary, SHA512BinaryOutput); // Add output column SHA512HexOutput (SHA512, HexString) IDTSOutputColumn100 SHA512HexOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "SHA512HexOutput", "SHA512 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); SHA512HexOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.HexString; SHA512HexOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.SHA512; SHA512HexOutput.Name = "SHA512HexOutput"; SHA512HexOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.SHA512, MultipleHash.OutputTypeEnumerator.HexString, SHA512HexOutput); // Add output column SHA512BaseOutput (SHA512, Base64String) IDTSOutputColumn100 SHA512BaseOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "SHA512BaseOutput", "SHA512 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); SHA512BaseOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Base64String; SHA512BaseOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.SHA512; SHA512BaseOutput.Name = "SHA512BaseOutput"; SHA512BaseOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.SHA512, MultipleHash.OutputTypeEnumerator.Base64String, SHA512BaseOutput); // Add SQL CE Destination // Add SQL CE Connection ConnectionManager sqlCECM = null; IDTSComponentMetaData100 sqlCETarget = null; CManagedComponentWrapper sqlCEInstance = null; StaticTestUtilities.CreateSQLCEComponent(package, dataFlowTask, sqlCEDatabaseName, sqlCEPassword, "TestRecords", out sqlCECM, out sqlCETarget, out sqlCEInstance); StaticTestUtilities.CreatePath(dataFlowTask, multipleHash.OutputCollection[0], sqlCETarget, sqlCEInstance); // Create a package events handler, to catch the output when running. PackageEventHandler packageEvents = new PackageEventHandler(); // Execute the package Microsoft.SqlServer.Dts.Runtime.DTSExecResult result = package.Execute(null, null, packageEvents as IDTSEvents, null, null); foreach (String message in packageEvents.eventMessages) { Debug.WriteLine(message); } // Make sure the package worked. Assert.AreEqual(Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, result, "Execution Failed"); // Connect to the SQLCE database SqlCeConnection connection = new SqlCeConnection(StaticTestUtilities.connectionString(sqlCEDatabaseName, sqlCEPassword)); try { if (connection.State == ConnectionState.Closed) { connection.Open(); } SqlCeCommand sqlCommand = new SqlCeCommand("SELECT * FROM [TestRecords] ORDER BY [StringData]", connection); SqlCeDataReader sqlData = sqlCommand.ExecuteReader(CommandBehavior.Default); int rowCount = 0; while (sqlData.Read()) { rowCount++; switch (rowCount) { case 1: StaticTestUtilities.testValues64("SHA512", sqlData, "NullRow", null, "50c86f4f943e637eb2c42db429731031e210b4d9f5b50abc874e89273405ce75d2a409f85964c54420ec0de3d8626850f850c8a8591c50c692e44e17cd305849", "UMhvT5Q+Y36yxC20KXMQMeIQtNn1tQq8h06JJzQFznXSpAn4WWTFRCDsDePYYmhQ+FDIqFkcUMaS5E4XzTBYSQ=="); break; case 2: StaticTestUtilities.testValues64("SHA512", sqlData, "StringData1", "MoreStringData1", "0ef1f38cf79cba4bdceb303ceed5372cb1236b04f4860b705345e5e6a922303dc6a4aa9646c2649bc437790d3fd4b936672659423b86a52ec95a4eb50ba791a3", "DvHzjPecukvc6zA87tU3LLEjawT0hgtwU0Xl5qkiMD3GpKqWRsJkm8Q3eQ0/1Lk2ZyZZQjuGpS7JWk61C6eRow=="); break; case 3: StaticTestUtilities.testValues64("SHA512", sqlData, "StringData2", "MoreStringData2", "3c6257a9044773472704a838cacc6ef738b6bd8e580011c20a2fed85a2c710aad16b825858f779857f6b3fedf1e4821c3970aaccfbef5cb7406b0800dfbc1029", "PGJXqQRHc0cnBKg4ysxu9zi2vY5YABHCCi/thaLHEKrRa4JYWPd5hX9rP+3x5IIcOXCqzPvvXLdAawgA37wQKQ=="); break; case 4: StaticTestUtilities.testValues64("SHA512", sqlData, "StringData3", "MoreStringData3", "b7695273172e0fda459c3e165ae7a971e0ea592beda60c35fb93e320aad8e4d8dbaeacd2f0f9d660ede1179add67fd057ce2301c7f9eca665abd770ced38d872", "t2lScxcuD9pFnD4WWuepceDqWSvtpgw1+5PjIKrY5NjbrqzS8PnWYO3hF5rdZ/0FfOIwHH+eymZavXcM7TjYcg=="); break; case 5: StaticTestUtilities.testValues64("SHA512", sqlData, "StringData4", "MoreStringData4", "52924dd94fe06c5b00f3659cd368be6cb39730c95836228a48eca9aa3447958027fb6d25c75af03b8ac19b035a3c72ee5a26a0a6672b89d4d9dd94ce16727f04", "UpJN2U/gbFsA82Wc02i+bLOXMMlYNiKKSOypqjRHlYAn+20lx1rwO4rBmwNaPHLuWiagpmcridTZ3ZTOFnJ/BA=="); break; default: Assert.Fail(string.Format("Account has to many records AccountCode {0}, AccountName {1}", sqlData.GetInt32(1), sqlData.GetString(2))); break; } } Assert.AreEqual(5, rowCount, "Rows in TestRecords"); } finally { if (connection != null) { connection.Close(); connection.Dispose(); } } }
public void CalculateHashCRC32Test() { Microsoft.SqlServer.Dts.Runtime.Package package; IDTSComponentMetaData100 multipleHash; CManagedComponentWrapper multipleHashInstance; String lineageString; MainPipe dataFlowTask; // Microsoft.SqlServer.Dts.Runtime.Application app; StaticTestUtilities.BuildSSISPackage(out package, out multipleHash, out multipleHashInstance, out lineageString, out dataFlowTask/*, out app */); int outputID = multipleHash.OutputCollection[0].ID; int outputColumnPos = multipleHash.OutputCollection[0].OutputColumnCollection.Count; // Add output column CRC32BinaryOutput (CRC32, Binary) IDTSOutputColumn100 CRC32BinaryOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "BinaryOutput", "CRC32 Hash of the input"); CRC32BinaryOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Binary; CRC32BinaryOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.CRC32; CRC32BinaryOutput.Name = "BinaryOutput"; CRC32BinaryOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.CRC32, MultipleHash.OutputTypeEnumerator.Binary, CRC32BinaryOutput); // Add output column CRC32HexOutput (CRC32, HexString) IDTSOutputColumn100 CRC32HexOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "HexOutput", "CRC32 Hash of the input"); CRC32HexOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.HexString; CRC32HexOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.CRC32; CRC32HexOutput.Name = "HexOutput"; CRC32HexOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.CRC32, MultipleHash.OutputTypeEnumerator.HexString, CRC32HexOutput); // Add output column CRC32BaseOutput (CRC32, Base64String) IDTSOutputColumn100 CRC32BaseOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "BaseOutput", "CRC32 Hash of the input"); CRC32BaseOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Base64String; CRC32BaseOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.CRC32; CRC32BaseOutput.Name = "BaseOutput"; CRC32BaseOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.CRC32, MultipleHash.OutputTypeEnumerator.Base64String, CRC32BaseOutput); // Add SQL CE Destination // Add SQL CE Connection ConnectionManager sqlCECM = null; IDTSComponentMetaData100 sqlCETarget = null; CManagedComponentWrapper sqlCEInstance = null; StaticTestUtilities.CreateSQLCEComponent(package, dataFlowTask, sqlCEDatabaseName, sqlCEPassword, "TestRecords", out sqlCECM, out sqlCETarget, out sqlCEInstance); StaticTestUtilities.CreatePath(dataFlowTask, multipleHash.OutputCollection[0], sqlCETarget, sqlCEInstance); // Create a package events handler, to catch the output when running. PackageEventHandler packageEvents = new PackageEventHandler(); // Execute the package Microsoft.SqlServer.Dts.Runtime.DTSExecResult result = package.Execute(null, null, packageEvents as IDTSEvents, null, null); foreach (String message in packageEvents.eventMessages) { Debug.WriteLine(message); } // Make sure the package worked. Assert.AreEqual(Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, result, "Execution Failed"); // Connect to the SQLCE database SqlCeConnection connection = new SqlCeConnection(StaticTestUtilities.connectionString(sqlCEDatabaseName, sqlCEPassword)); try { if (connection.State == ConnectionState.Closed) { connection.Open(); } SqlCeCommand sqlCommand = new SqlCeCommand("SELECT * FROM [TestRecords] ORDER BY [StringData]", connection); SqlCeDataReader sqlData = sqlCommand.ExecuteReader(CommandBehavior.Default); int rowCount = 0; while (sqlData.Read()) { rowCount++; switch (rowCount) { case 1: StaticTestUtilities.testValues4("CRC32", sqlData, "NullRow", null, "b72e9896", "ty6Ylg=="); break; case 2: StaticTestUtilities.testValues4("CRC32", sqlData, "StringData1", "MoreStringData1", "4fa69232", "T6aSMg=="); break; case 3: StaticTestUtilities.testValues4("CRC32", sqlData, "StringData2", "MoreStringData2", "12988cea", "EpiM6g=="); break; case 4: StaticTestUtilities.testValues4("CRC32", sqlData, "StringData3", "MoreStringData3", "631f9b57", "Yx+bVw=="); break; case 5: StaticTestUtilities.testValues4("CRC32", sqlData, "StringData4", "MoreStringData4", "f8bed23b", "+L7SOw=="); break; default: Assert.Fail(string.Format("Account has to many records AccountCode {0}, AccountName {1}", sqlData.GetInt32(1), sqlData.GetString(2))); break; } } Assert.AreEqual(5, rowCount, "Rows in TestRecords"); } finally { if (connection != null) { connection.Close(); connection.Dispose(); } } }
public void CalculateHashSHA256Test() { Microsoft.SqlServer.Dts.Runtime.Package package; IDTSComponentMetaData100 multipleHash; CManagedComponentWrapper multipleHashInstance; String lineageString; MainPipe dataFlowTask; // Microsoft.SqlServer.Dts.Runtime.Application app; StaticTestUtilities.BuildSSISPackage(out package, out multipleHash, out multipleHashInstance, out lineageString, out dataFlowTask /*, out app */); int outputID = multipleHash.OutputCollection[0].ID; int outputColumnPos = multipleHash.OutputCollection[0].OutputColumnCollection.Count; // Add output column SHA256BinaryOutput (SHA256, Binary) IDTSOutputColumn100 SHA256BinaryOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "SHA256BinaryOutput", "SHA256 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); SHA256BinaryOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Binary; SHA256BinaryOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.SHA256; SHA256BinaryOutput.Name = "SHA256BinaryOutput"; SHA256BinaryOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.SHA256, MultipleHash.OutputTypeEnumerator.Binary, SHA256BinaryOutput); // Add output column SHA256HexOutput (SHA256, HexString) IDTSOutputColumn100 SHA256HexOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "SHA256HexOutput", "SHA256 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); SHA256HexOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.HexString; SHA256HexOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.SHA256; SHA256HexOutput.Name = "SHA256HexOutput"; SHA256HexOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.SHA256, MultipleHash.OutputTypeEnumerator.HexString, SHA256HexOutput); // Add output column SHA256BaseOutput (SHA256, Base64String) IDTSOutputColumn100 SHA256BaseOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "SHA256BaseOutput", "SHA256 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); SHA256BaseOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Base64String; SHA256BaseOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.SHA256; SHA256BaseOutput.Name = "SHA256BaseOutput"; SHA256BaseOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.SHA256, MultipleHash.OutputTypeEnumerator.Base64String, SHA256BaseOutput); // Add SQL CE Destination // Add SQL CE Connection ConnectionManager sqlCECM = null; IDTSComponentMetaData100 sqlCETarget = null; CManagedComponentWrapper sqlCEInstance = null; StaticTestUtilities.CreateSQLCEComponent(package, dataFlowTask, sqlCEDatabaseName, sqlCEPassword, "TestRecords", out sqlCECM, out sqlCETarget, out sqlCEInstance); StaticTestUtilities.CreatePath(dataFlowTask, multipleHash.OutputCollection[0], sqlCETarget, sqlCEInstance); // Create a package events handler, to catch the output when running. PackageEventHandler packageEvents = new PackageEventHandler(); // Execute the package Microsoft.SqlServer.Dts.Runtime.DTSExecResult result = package.Execute(null, null, packageEvents as IDTSEvents, null, null); foreach (String message in packageEvents.eventMessages) { Debug.WriteLine(message); } // Make sure the package worked. Assert.AreEqual(Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, result, "Execution Failed"); // Connect to the SQLCE database SqlCeConnection connection = new SqlCeConnection(StaticTestUtilities.connectionString(sqlCEDatabaseName, sqlCEPassword)); try { if (connection.State == ConnectionState.Closed) { connection.Open(); } SqlCeCommand sqlCommand = new SqlCeCommand("SELECT * FROM [TestRecords] ORDER BY [StringData]", connection); SqlCeDataReader sqlData = sqlCommand.ExecuteReader(CommandBehavior.Default); int rowCount = 0; while (sqlData.Read()) { rowCount++; switch (rowCount) { case 1: StaticTestUtilities.testValues32("SHA256", sqlData, "NullRow", null, "48c17d6227bdf750df1ef91751f5fa15425b25bc5f965802ca0e95fac5eda7cc", "SMF9Yie991DfHvkXUfX6FUJbJbxfllgCyg6V+sXtp8w="); break; case 2: StaticTestUtilities.testValues32("SHA256", sqlData, "StringData1", "MoreStringData1", "4d511426ecd9e8206ab72db0c8c6f9636b87dbc7c0c7a9fe1164bf86a7913eed", "TVEUJuzZ6CBqty2wyMb5Y2uH28fAx6n+EWS/hqeRPu0="); break; case 3: StaticTestUtilities.testValues32("SHA256", sqlData, "StringData2", "MoreStringData2", "4052c948bac0b3498a71e2ac7ad507b310908de1bdc2443cdcf31601d496bcc9", "QFLJSLrAs0mKceKsetUHsxCQjeG9wkQ83PMWAdSWvMk="); break; case 4: StaticTestUtilities.testValues32("SHA256", sqlData, "StringData3", "MoreStringData3", "c4e88c32401a0ee6c0f73412eb49dace1626118feccfddf9623960f3adcf8a40", "xOiMMkAaDubA9zQS60nazhYmEY/sz935Yjlg863PikA="); break; case 5: StaticTestUtilities.testValues32("SHA256", sqlData, "StringData4", "MoreStringData4", "4eda1ed84bc97ff2640ed39dc11a4da010de9dc74f42e1c8f562e4ff01bc8ebd", "Ttoe2EvJf/JkDtOdwRpNoBDencdPQuHI9WLk/wG8jr0="); break; default: Assert.Fail(string.Format("Account has to many records AccountCode {0}, AccountName {1}", sqlData.GetInt32(1), sqlData.GetString(2))); break; } } Assert.AreEqual(5, rowCount, "Rows in TestRecords"); } finally { if (connection != null) { connection.Close(); connection.Dispose(); } } }
protected void btnUpload_Click(object sender, EventArgs e) { WriteLine("Go"); if (!fupMovementUpload.HasFile) { lblStatus.Text = @"Please select a file for uploading."; return; } if (Path.GetExtension(fupMovementUpload.FileName).ToLower() != ".txt") { lblStatus.Text = @"You must first select a .txt file for uploading."; return; } string country = ddlCountryList.SelectedItem.Value; if (country == countryDummy) { lblStatus.Text = @"Please select a country from the drop down menu."; return; } WriteLine("start"); if (_fplm.GetStatus().Contains(FleetPlanOptions.Running.ToString())) { _fplm.Update(); return; } try { string fp = ddlFleetPlan.SelectedItem.Text; string uploadTypeName = ddlAdditionDeletion.SelectedItem.Text; string savedFileName = country + "_" + fp + "_" + uploadTypeName + ".txt"; string path = ConfigAccess.GetFleetPlanUploadLocation(); string fullSavedFileName = Path.Combine(path, savedFileName); WriteLine("1"); fupMovementUpload.SaveAs(fullSavedFileName); WriteLine("2"); //file uploaded call ssis package string SSISpackagePath = ConfigAccess.GetSSISPackagePath(); WriteLine("3"); Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application(); WriteLine("4"); using (Package package = app.LoadPackage(SSISpackagePath, null)) { WriteLine("5"); //Global Package Variable Variables vars = package.Variables; string SSISpackageVariable = ConfigAccess.GetSSISPackageVariable(); WriteLine("6"); vars[SSISpackageVariable].Value = savedFileName; Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute(); WriteLine("7"); if (results == DTSExecResult.Success) { //create archive var fleetPlanID = Convert.ToInt32(ddlFleetPlan.SelectedItem.Value); var isAddition = Convert.ToBoolean(Convert.ToInt32(ddlAdditionDeletion.SelectedItem.Value)); var user = this.Page.RadUsername() + " (" + this.Page.RadUserId() + ")"; bll.ForecastOperationalFleetUpdate(DateTime.Today, fleetPlanID, country); bll.FleetPlanEntryUploadArchiveCreate(user, fupMovementUpload.FileName, savedFileName, fleetPlanID, country, isAddition); lblStatus.Text = @"Upload status: File uploaded! - Processing file status: " + results.ToString(); //_fplm.UpdateTables(); } else { StringBuilder errorBuilder = new StringBuilder(); foreach (DtsError error in package.Errors) { errorBuilder.AppendLine(error.Description); lblStatus.Text = "File uploaded, but error occured during processing, " + results.ToString() + " ,\n " + errorBuilder.ToString(); } } } } catch (Exception ex) { lblStatus.Text = @"Operation failed. The following error occured: " + ex.Message; } finally { BindView(); } }
public void CalculateHashMD5Test() { Microsoft.SqlServer.Dts.Runtime.Package package; IDTSComponentMetaData100 multipleHash; CManagedComponentWrapper multipleHashInstance; String lineageString; MainPipe dataFlowTask; // Microsoft.SqlServer.Dts.Runtime.Application app; StaticTestUtilities.BuildSSISPackage(out package, out multipleHash, out multipleHashInstance, out lineageString, out dataFlowTask /*, out app */); int outputID = multipleHash.OutputCollection[0].ID; int outputColumnPos = multipleHash.OutputCollection[0].OutputColumnCollection.Count; // Add output column MD5BinaryOutput (MD5, Binary) IDTSOutputColumn100 MD5BinaryOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "MD5BinaryOutput", "MD5 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); MD5BinaryOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Binary; MD5BinaryOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.MD5; MD5BinaryOutput.Name = "MD5BinaryOutput"; MD5BinaryOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; // Add output column MD5HexOutput (MD5, HexString) IDTSOutputColumn100 MD5HexOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "MD5HexOutput", "MD5 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); MD5HexOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.HexString; MD5HexOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.MD5; MD5HexOutput.Name = "MD5HexOutput"; MD5HexOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.MD5, MultipleHash.OutputTypeEnumerator.HexString, MD5HexOutput); // Add output column MD5BaseOutput (MD5, Base64String) IDTSOutputColumn100 MD5BaseOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "MD5BaseOutput", "MD5 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); MD5BaseOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Base64String; MD5BaseOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.MD5; MD5BaseOutput.Name = "MD5BaseOutput"; MD5BaseOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.MD5, MultipleHash.OutputTypeEnumerator.Base64String, MD5BaseOutput); // Add SQL CE Destination // Add SQL CE Connection ConnectionManager sqlCECM = null; IDTSComponentMetaData100 sqlCETarget = null; CManagedComponentWrapper sqlCEInstance = null; StaticTestUtilities.CreateSQLCEComponent(package, dataFlowTask, sqlCEDatabaseName, sqlCEPassword, "TestRecords", out sqlCECM, out sqlCETarget, out sqlCEInstance); StaticTestUtilities.CreatePath(dataFlowTask, multipleHash.OutputCollection[0], sqlCETarget, sqlCEInstance); // Create a package events handler, to catch the output when running. PackageEventHandler packageEvents = new PackageEventHandler(); // Execute the package Microsoft.SqlServer.Dts.Runtime.DTSExecResult result = package.Execute(null, null, packageEvents as IDTSEvents, null, null); foreach (String message in packageEvents.eventMessages) { Debug.WriteLine(message); } // Make sure the package worked. Assert.AreEqual(Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, result, "Execution Failed"); // Connect to the SQLCE database SqlCeConnection connection = new SqlCeConnection(StaticTestUtilities.connectionString(sqlCEDatabaseName, sqlCEPassword)); try { if (connection.State == ConnectionState.Closed) { connection.Open(); } SqlCeCommand sqlCommand = new SqlCeCommand("SELECT * FROM [TestRecords] ORDER BY [StringData]", connection); SqlCeDataReader sqlData = sqlCommand.ExecuteReader(CommandBehavior.Default); int rowCount = 0; while (sqlData.Read()) { rowCount++; switch (rowCount) { case 1: StaticTestUtilities.testValues16("MD5", sqlData, "NullRow", null, "ad0ff38c612ba6550f7f991d8d451557", "rQ/zjGErplUPf5kdjUUVVw=="); break; case 2: StaticTestUtilities.testValues16("MD5", sqlData, "StringData1", "MoreStringData1", "35ec7260ec3b96b84e026111f8d7c966", "NexyYOw7lrhOAmER+NfJZg=="); break; case 3: StaticTestUtilities.testValues16("MD5", sqlData, "StringData2", "MoreStringData2", "85070590507e30f622e85b3de2fd1be7", "hQcFkFB+MPYi6Fs94v0b5w=="); break; case 4: StaticTestUtilities.testValues16("MD5", sqlData, "StringData3", "MoreStringData3", "56c4813f94449bae1db11116a983a515", "VsSBP5REm64dsREWqYOlFQ=="); break; case 5: StaticTestUtilities.testValues16("MD5", sqlData, "StringData4", "MoreStringData4", "687502290576828a03b30658121389c2", "aHUCKQV2gooDswZYEhOJwg=="); break; default: Assert.Fail(string.Format("Account has to many records AccountCode {0}, AccountName {1}", sqlData.GetInt32(1), sqlData.GetString(2))); break; } } Assert.AreEqual(5, rowCount, "Rows in TestRecords"); } finally { if (connection != null) { connection.Close(); connection.Dispose(); } } }
public void CalculateHashRipeMD160Test() { Microsoft.SqlServer.Dts.Runtime.Package package; IDTSComponentMetaData100 multipleHash; CManagedComponentWrapper multipleHashInstance; String lineageString; MainPipe dataFlowTask; // Microsoft.SqlServer.Dts.Runtime.Application app; StaticTestUtilities.BuildSSISPackage(out package, out multipleHash, out multipleHashInstance, out lineageString, out dataFlowTask /*, out app */); int outputID = multipleHash.OutputCollection[0].ID; int outputColumnPos = multipleHash.OutputCollection[0].OutputColumnCollection.Count; // Add output column RipeMD160BinaryOutput (RipeMD160, Binary) IDTSOutputColumn100 RipeMD160BinaryOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "RipeMD160BinaryOutput", "RipeMD160 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); RipeMD160BinaryOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Binary; RipeMD160BinaryOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.RipeMD160; RipeMD160BinaryOutput.Name = "RipeMD160BinaryOutput"; RipeMD160BinaryOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.RipeMD160, MultipleHash.OutputTypeEnumerator.Binary, RipeMD160BinaryOutput); // Add output column RipeMD160HexOutput (RipeMD160, HexString) IDTSOutputColumn100 RipeMD160HexOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "RipeMD160HexOutput", "RipeMD160 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); RipeMD160HexOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.HexString; RipeMD160HexOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.RipeMD160; RipeMD160HexOutput.Name = "RipeMD160HexOutput"; RipeMD160HexOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.RipeMD160, MultipleHash.OutputTypeEnumerator.HexString, RipeMD160HexOutput); // Add output column RipeMD160BaseOutput (RipeMD160, Base64String) IDTSOutputColumn100 RipeMD160BaseOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "RipeMD160BaseOutput", "RipeMD160 Hash of the input"); //multipleHash.OutputCollection[0].OutputColumnCollection.New(); RipeMD160BaseOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Base64String; RipeMD160BaseOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.RipeMD160; RipeMD160BaseOutput.Name = "RipeMD160BaseOutput"; RipeMD160BaseOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.RipeMD160, MultipleHash.OutputTypeEnumerator.Base64String, RipeMD160BaseOutput); // Add SQL CE Destination // Add SQL CE Connection ConnectionManager sqlCECM = null; IDTSComponentMetaData100 sqlCETarget = null; CManagedComponentWrapper sqlCEInstance = null; StaticTestUtilities.CreateSQLCEComponent(package, dataFlowTask, sqlCEDatabaseName, sqlCEPassword, "TestRecords", out sqlCECM, out sqlCETarget, out sqlCEInstance); StaticTestUtilities.CreatePath(dataFlowTask, multipleHash.OutputCollection[0], sqlCETarget, sqlCEInstance); // Create a package events handler, to catch the output when running. PackageEventHandler packageEvents = new PackageEventHandler(); // Execute the package Microsoft.SqlServer.Dts.Runtime.DTSExecResult result = package.Execute(null, null, packageEvents as IDTSEvents, null, null); foreach (String message in packageEvents.eventMessages) { Debug.WriteLine(message); } // Make sure the package worked. Assert.AreEqual(Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, result, "Execution Failed"); // Connect to the SQLCE database SqlCeConnection connection = new SqlCeConnection(StaticTestUtilities.connectionString(sqlCEDatabaseName, sqlCEPassword)); if (connection.State == ConnectionState.Closed) { connection.Open(); } SqlCeCommand sqlCommand = new SqlCeCommand("SELECT * FROM [TestRecords] ORDER BY [StringData]", connection); try { SqlCeDataReader sqlData = sqlCommand.ExecuteReader(CommandBehavior.Default); int rowCount = 0; while (sqlData.Read()) { rowCount++; switch (rowCount) { case 1: StaticTestUtilities.testValues20("RipeMD160", sqlData, "NullRow", null, "83c5a4d2b3d607c4dc49829011a16cc9415ac865", "g8Wk0rPWB8TcSYKQEaFsyUFayGU="); break; case 2: StaticTestUtilities.testValues20("RipeMD160", sqlData, "StringData1", "MoreStringData1", "95878786667e4fb8baf5e93e3d7560ead8939551", "lYeHhmZ+T7i69ek+PXVg6tiTlVE="); break; case 3: StaticTestUtilities.testValues20("RipeMD160", sqlData, "StringData2", "MoreStringData2", "616a8ed8b2210e16da0dcfa9a126e7e0bc305b39", "YWqO2LIhDhbaDc+poSbn4LwwWzk="); break; case 4: StaticTestUtilities.testValues20("RipeMD160", sqlData, "StringData3", "MoreStringData3", "c3a0013e3c3fc7d4e8e078ae165cb54d6ec91023", "w6ABPjw/x9To4HiuFly1TW7JECM="); break; case 5: StaticTestUtilities.testValues20("RipeMD160", sqlData, "StringData4", "MoreStringData4", "d570b9cd0e00f44afb85d6c5e1ccaccc17280fd3", "1XC5zQ4A9Er7hdbF4cyszBcoD9M="); break; default: Assert.Fail(string.Format("Account has to many records AccountCode {0}, AccountName {1}", sqlData.GetInt32(1), sqlData.GetString(2))); break; } } Assert.AreEqual(5, rowCount, "Rows in TestRecords"); } finally { if (connection != null) { connection.Close(); connection.Dispose(); } } }
public void CalculateHashFNV1a64Test() { Microsoft.SqlServer.Dts.Runtime.Package package; IDTSComponentMetaData100 multipleHash; CManagedComponentWrapper multipleHashInstance; String lineageString; MainPipe dataFlowTask; // Microsoft.SqlServer.Dts.Runtime.Application app; StaticTestUtilities.BuildSSISPackage(out package, out multipleHash, out multipleHashInstance, out lineageString, out dataFlowTask /*, out app */); int outputID = multipleHash.OutputCollection[0].ID; int outputColumnPos = multipleHash.OutputCollection[0].OutputColumnCollection.Count; // Add output column CRC32BinaryOutput (CRC32, Binary) IDTSOutputColumn100 FNV1a64BinaryOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "BinaryOutput", "FNV1a64 Hash of the input"); FNV1a64BinaryOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Binary; FNV1a64BinaryOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.FNV1a64; FNV1a64BinaryOutput.Name = "BinaryOutput"; FNV1a64BinaryOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.FNV1a64, MultipleHash.OutputTypeEnumerator.Binary, FNV1a64BinaryOutput); // Add output column FNV1a64HexOutput (FNV1a64, HexString) IDTSOutputColumn100 FNV1a64HexOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "HexOutput", "FNV1a64 Hash of the input"); FNV1a64HexOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.HexString; FNV1a64HexOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.FNV1a64; FNV1a64HexOutput.Name = "HexOutput"; FNV1a64HexOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.FNV1a64, MultipleHash.OutputTypeEnumerator.HexString, FNV1a64HexOutput); // Add output column FNV1a64BaseOutput (FNV1a64, Base64String) IDTSOutputColumn100 FNV1a64BaseOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "BaseOutput", "FNV1a64 Hash of the input"); FNV1a64BaseOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Base64String; FNV1a64BaseOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.FNV1a64; FNV1a64BaseOutput.Name = "BaseOutput"; FNV1a64BaseOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.FNV1a64, MultipleHash.OutputTypeEnumerator.Base64String, FNV1a64BaseOutput); // Add SQL CE Destination // Add SQL CE Connection ConnectionManager sqlCECM = null; IDTSComponentMetaData100 sqlCETarget = null; CManagedComponentWrapper sqlCEInstance = null; StaticTestUtilities.CreateSQLCEComponent(package, dataFlowTask, sqlCEDatabaseName, sqlCEPassword, "TestRecords", out sqlCECM, out sqlCETarget, out sqlCEInstance); StaticTestUtilities.CreatePath(dataFlowTask, multipleHash.OutputCollection[0], sqlCETarget, sqlCEInstance); // Create a package events handler, to catch the output when running. PackageEventHandler packageEvents = new PackageEventHandler(); // Execute the package Microsoft.SqlServer.Dts.Runtime.DTSExecResult result = package.Execute(null, null, packageEvents as IDTSEvents, null, null); foreach (String message in packageEvents.eventMessages) { Debug.WriteLine(message); } // Make sure the package worked. Assert.AreEqual(Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, result, "Execution Failed"); // Connect to the SQLCE database SqlCeConnection connection = new SqlCeConnection(StaticTestUtilities.connectionString(sqlCEDatabaseName, sqlCEPassword)); try { if (connection.State == ConnectionState.Closed) { connection.Open(); } SqlCeCommand sqlCommand = new SqlCeCommand("SELECT * FROM [TestRecords] ORDER BY [StringData]", connection); SqlCeDataReader sqlData = sqlCommand.ExecuteReader(CommandBehavior.Default); int rowCount = 0; while (sqlData.Read()) { rowCount++; switch (rowCount) { case 1: StaticTestUtilities.testValues8("FNV1a64", sqlData, "NullRow", null, "758396eaa4bce12f", "dYOW6qS84S8="); break; case 2: StaticTestUtilities.testValues8("FNV1a64", sqlData, "StringData1", "MoreStringData1", "3269fd2c40e86d04", "Mmn9LEDobQQ="); break; case 3: StaticTestUtilities.testValues8("FNV1a64", sqlData, "StringData2", "MoreStringData2", "eb95ee14223bee87", "65XuFCI77oc="); break; case 4: StaticTestUtilities.testValues8("FNV1a64", sqlData, "StringData3", "MoreStringData3", "e8976e5edde8b3bc", "6JduXt3os7w="); break; case 5: StaticTestUtilities.testValues8("FNV1a64", sqlData, "StringData4", "MoreStringData4", "a39fa068f937ff57", "o5+gaPk3/1c="); break; default: Assert.Fail(string.Format("Account has to many records AccountCode {0}, AccountName {1}", sqlData.GetInt32(1), sqlData.GetString(2))); break; } } Assert.AreEqual(5, rowCount, "Rows in TestRecords"); } finally { if (connection != null) { connection.Close(); connection.Dispose(); } } }
static public string ProcessZipCodeFile(string FilePath) { string _message = null; //use this when the SSIS import fails. Database db = DatabaseFactory.CreateDatabase("BRGCaseMail"); try { //ZipCodeImportService.DeleteAll(); //// Read and display lines from the file until the end of //// the file is reached. //using (TextReader tr = File.OpenText(FilePath)) //{ // String line; // int i=0; // while ((line = tr.ReadLine()) != null) // { // i++; // ZipCodeImport _data = new ZipCodeImport(line); // //skip first line // if (_data.ZipCode != "ZipCode") // { // Insert(_data); // } // } // System.Data.Common.DbCommand dbCommand = db.GetStoredProcCommand("p_ImportZipCodeData"); // db.ExecuteNonQuery(dbCommand); // return true; // } //execute the dts package //Load DTSX Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application(); Package package = app.LoadPackage(System.Configuration.ConfigurationManager.AppSettings.Get("ZipCodeImportDTSXFile"), null); //Specify Excel Connection From DTSX Connection Manager Debug.WriteLine(package.Connections["SourceConnectionFlatFile"].ConnectionString); package.Connections["SourceConnectionFlatFile"].ConnectionString = FilePath; Debug.WriteLine(package.Connections["DestinationConnectionOLEDB"].ConnectionString); package.Connections["DestinationConnectionOLEDB"].ConnectionString = ConfigurationManager.AppSettings["DTSConnectionString"]; // //Execute DTSX. Microsoft.SqlServer.Dts.Runtime.DTSExecResult results = package.Execute(); if (results == DTSExecResult.Success) { //yay! System.Data.Common.DbCommand dbCommand = db.GetStoredProcCommand("p_ImportZipCodeData"); db.ExecuteNonQuery(dbCommand); return("ZipCode Import Success!"); } else { _message = string.Empty; foreach (DtsError _err in package.Errors) { _message += _err.Description + ". "; } _message += "Please see you system adminstrator. "; return(_message); } } catch (Exception ex) { _message += "Error: " + ex.ToString() + "Please see your system adminstrator. "; return(_message); } }
public void xxHashWithinSSISTest() { Microsoft.SqlServer.Dts.Runtime.Package package; IDTSComponentMetaData100 multipleHash; CManagedComponentWrapper multipleHashInstance; String lineageString; MainPipe dataFlowTask; StaticTestUtilities.BuildSSISPackage(out package, out multipleHash, out multipleHashInstance, out lineageString, out dataFlowTask); int outputID = multipleHash.OutputCollection[0].ID; int outputColumnPos = multipleHash.OutputCollection[0].OutputColumnCollection.Count; // Add output column xxHashBinaryOutput (xxHash, Binary) IDTSOutputColumn100 xxHashBinaryOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "xxHashBinaryOutput", "xxHash Hash of the input"); xxHashBinaryOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Binary; xxHashBinaryOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.xxHash; xxHashBinaryOutput.Name = "xxHashBinaryOutput"; xxHashBinaryOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.xxHash, MultipleHash.OutputTypeEnumerator.Binary, xxHashBinaryOutput); // Add output column xxHashHexOutput (xxHash, HexString) IDTSOutputColumn100 xxHashHexOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "xxHashHexOutput", "xxHash Hash of the input"); xxHashHexOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.HexString; xxHashHexOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.xxHash; xxHashHexOutput.Name = "xxHashHexOutput"; xxHashHexOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.xxHash, MultipleHash.OutputTypeEnumerator.HexString, xxHashHexOutput); // Add output column xxHashBaseOutput (xxHash, Base64String) IDTSOutputColumn100 xxHashBaseOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "xxHashBaseOutput", "xxHash Hash of the input"); xxHashBaseOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Base64String; xxHashBaseOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.xxHash; xxHashBaseOutput.Name = "xxHashBaseOutput"; xxHashBaseOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString; Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.xxHash, MultipleHash.OutputTypeEnumerator.Base64String, xxHashBaseOutput); // Add SQL CE Destination // Add SQL CE Connection ConnectionManager sqlCECM = null; IDTSComponentMetaData100 sqlCETarget = null; CManagedComponentWrapper sqlCEInstance = null; CreateSQLCEComponent(package, dataFlowTask, "TestRecordsxxHash", out sqlCECM, out sqlCETarget, out sqlCEInstance); CreatePath(dataFlowTask, multipleHash.OutputCollection[0], sqlCETarget, sqlCEInstance); // Create a package events handler, to catch the output when running. PackageEventHandler packageEvents = new PackageEventHandler(); // Execute the package Microsoft.SqlServer.Dts.Runtime.DTSExecResult result = package.Execute(null, null, packageEvents as IDTSEvents, null, null); foreach (String message in packageEvents.eventMessages) { Debug.WriteLine(message); } // Make sure the package worked. Assert.AreEqual(Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success, result, "Execution Failed"); // Connect to the SQLCE database SqlCeConnection connection = new SqlCeConnection(connectionString()); try { if (connection.State == ConnectionState.Closed) { connection.Open(); } SqlCeCommand sqlCommand = new SqlCeCommand("SELECT * FROM [TestRecordsxxHash] ORDER BY [StringData]", connection); SqlCeDataReader sqlData = sqlCommand.ExecuteReader(CommandBehavior.Default); int rowCount = 0; while (sqlData.Read()) { rowCount++; switch (rowCount) { case 1: StaticTestUtilities.testValues8("xxHash", sqlData, "NullRow", null, "63d75a5b2781fb7f", "Y9daWyeB+38="); break; case 2: StaticTestUtilities.testValues8("xxHash", sqlData, "StringData1", "MoreStringData1", "e5f10f243a28ead1", "5fEPJDoo6tE="); break; case 3: StaticTestUtilities.testValues8("xxHash", sqlData, "StringData2", "MoreStringData2", "191f4ed7e4958618", "GR9O1+SVhhg="); break; case 4: StaticTestUtilities.testValues8("xxHash", sqlData, "StringData3", "MoreStringData3", "fb80f19f00ce45b7", "+4DxnwDORbc="); break; case 5: StaticTestUtilities.testValues8("xxHash", sqlData, "StringData4", "MoreStringData4", "c056c66a492d32fc", "wFbGakktMvw="); break; default: Assert.Fail(string.Format("Account has to many records AccountCode {0}, AccountName {1}", sqlData.GetInt32(1), sqlData.GetString(2))); break; } } Assert.AreEqual(5, rowCount, "Rows in TestRecords"); } finally { if (connection != null) { connection.Close(); connection.Dispose(); } } }