public void TestSetConnectionString()
{
string packageFilepath;
#if SQL2005
packageFilepath = UnpackToFile("UTssisUnit.TestPackages.PropertyTest.dtsx");
#elif SQL2008
packageFilepath = UnpackToFile("UTssisUnit.TestPackages.PropertyTest.dtsx");
#elif SQL2014 || SQL2012
packageFilepath = UnpackToFile("UTssisUnit.TestPackages.PropertyTest2012.dtsx");
#elif SQL2017
packageFilepath = UnpackToFile("UTssisUnit.TestPackages.PropertyTest2017.dtsx");
#endif
var ts = new SsisTestSuite();
ts.PackageList.Add("PackageA", new PackageRef("PackageA", packageFilepath, PackageStorageType.FileSystem));
var ssisTest = new Test(ts, "Test", "PackageA", null, "{7874CCC9-C3C6-40F5-9E8B-1DD62903D845}");
ssisTest.TestSetup.Commands.Add(new PropertyCommand(ts, "Set", @"\Package.Connections[localhost.AdventureWorksDW2008].Properties[ConnectionString]", "Provider=SQLNCLI11.1;Data Source=localhost;Initial Catalog=ssisUnitTestDb;Integrated Security=SSPI;Application Name=TestValue"));
ts.Tests.Add("Test", ssisTest);
ts.Tests["Test"].Asserts.Add("Assert", AddNewAssert(ts, ssisTest, "Assert", "Data Source=localhost;Initial Catalog=ssisUnitTestDb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Application Name=TestValue;", "\\Package.Connections[localhost.AdventureWorksDW2008].Properties[ConnectionString]"));
ts.Execute();
Assert.AreEqual("Data Source=localhost;Initial Catalog=ssisUnitTestDb;Provider=SQLNCLI11.1;Integrated Security=SSPI;Application Name=TestValue;",
ssisTest.InternalPackage.Connections["localhost.AdventureWorksDW2008"].ConnectionString);
Assert.AreEqual(2, ts.Statistics.GetStatistic(StatisticEnum.AssertPassedCount));
Assert.AreEqual(0, ts.Statistics.GetStatistic(StatisticEnum.AssertFailedCount));
}
public void TestExecuteWithParameters()
{
string packageFile = UnpackToFile(TestPackageResource);
var target = new SsisTestSuite();
target.Parameters["TestParameter"] = string.Empty;
target.PackageList.Add("TestPkg", new PackageRef("TestPkg", "PathToChange", PackageStorageType.FileSystem));
target.DynamicValues.Add(new DynamicValue()
{
Value = "%TestParameter%",
AppliesTo = "TestSuite/PackageList[TestPkg]/PackagePath",
});
target.Tests.Add("Test", new Test(target, "Test", "TestPkg", null, "Package"));
target.Tests["Test"].Asserts.Add("Assert", new SsisAssert(target, target.Tests["Test"], "Assert", true, false));
target.Tests["Test"].Asserts["Assert"].Command = new TestCommand();
target.Execute(new Dictionary <string, string>
{
{ "TestParameter", packageFile }
});
Assert.AreEqual(packageFile, target.PackageList["TestPkg"].PackagePath);
Assert.AreEqual(2, target.Statistics.GetStatistic(StatisticEnum.AssertPassedCount));
}
public void TestAssertCommandBoolean()
{
var target = new SsisTestSuite();
target.PackageList.Add("UT Basic Scenario", new PackageRef("UT Basic Scenario", _dtsxFilePath, PackageStorageType.FileSystem));
var ssisTest = new Test(target, "Test", "UT Basic Scenario", null, "SELECT COUNT");
target.Tests.Add("Test", ssisTest);
var ssisAssert = new SsisAssert(target, ssisTest, "Test Count", true, false, false);
ssisAssert.Command = new Commands.TestCommand();
ssisTest.Asserts.Add("Test Count", ssisAssert);
ssisAssert = new SsisAssert(target, ssisTest, "Test Count 1", "True", false, false);
ssisAssert.Command = new Commands.TestCommand();
ssisTest.Asserts.Add("Test Count 1", ssisAssert);
int testCount = 0;
try
{
testCount = target.Execute();
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
Assert.AreEqual(1, testCount);
Assert.AreEqual(1, target.Statistics.GetStatistic(StatisticEnum.TestPassedCount));
Assert.AreEqual(3, target.Statistics.GetStatistic(StatisticEnum.AssertPassedCount));
}
public void CheckProjectPathsTest()
{
string projectFilepath;
projectFilepath = UnpackToFile("UTssisUnit.TestPackages.ISPACTesting.ispac", true);
var ts = new SsisTestSuite();
ts.PackageList.Add("PackageA", new PackageRef("ExecuteSqlTask", projectFilepath, "ExecuteSqlTask.dtsx", PackageStorageType.FileSystem));
ts.SetupCommands.Commands.Add(new PropertyCommand(ts, "Set", @"\Project\ConnectionManagers[localhost.AdventureWorks2012.conmgr].Properties[ConnectionString]", "Data Source=.;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;"));
Test ssisTest = new Test(ts, "Test", projectFilepath, "ExecuteSqlTask.dtsx", null, "{B56FADB6-02EF-477B-9139-987363F8BCE3}");
ts.Tests.Add("Test", ssisTest);
ts.Tests["Test"].Asserts.Add("TestA1", AddNewAssert(ts, ssisTest, "TestA1", "Data Source=.;Initial Catalog=AdventureWorks2012;Provider=SQLNCLI11.1;Integrated Security=SSPI;Auto Translate=False;", "\\Project\\ConnectionManagers[localhost.AdventureWorks2012.conmgr].Properties[ConnectionString]"));
ts.Tests["Test"].Asserts.Add("TestA2", AddNewAssert(ts, ssisTest, "TestA2", "AdventureWorks2012", "\\Project\\ConnectionManagers[localhost.AdventureWorks2012.conmgr].Properties[InitialCatalog]"));
ts.Tests["Test"].Asserts.Add("TestA3", AddNewAssert(ts, ssisTest, "TestA3", DTSProtectionLevel.EncryptSensitiveWithUserKey, "\\Project.Properties[ProtectionLevel]"));
ts.Tests["Test"].Asserts.Add("TestA4", AddNewAssert(ts, ssisTest, "TestA4", DTSTargetServerVersion.Latest, "\\Project.Properties[TargetServerVersion]"));
ts.Tests["Test"].Asserts.Add("TestA5", AddNewAssert(ts, ssisTest, "TestA5", "ISPACTesting", "\\Project.Properties[Name]"));
var context = ts.CreateContext();
ts.Execute(context);
context.Log.ApplyTo(log => Debug.Print(log.ItemName + " :: " + string.Join(Environment.NewLine + "\t", log.Messages)));
Assert.AreEqual(6, ts.Statistics.GetStatistic(StatisticEnum.AssertPassedCount));
Assert.AreEqual(0, ts.Statistics.GetStatistic(StatisticEnum.AssertFailedCount));
}
public void TestAssertCommandFailure()
{
var target = new SsisTestSuite();
target.ConnectionList.Add("AdventureWorks", new ConnectionRef("AdventureWorks",
"Provider=SQLNCLI11;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=tempdb",
ConnectionRef.ConnectionTypeEnum.ConnectionString));
target.PackageList.Add("UT Basic Scenario", new PackageRef("UT Basic Scenario", _dtsxFilePath, PackageStorageType.FileSystem));
var ssisTest = new Test(target, "Test", "UT Basic Scenario", null, "SELECT COUNT");
target.Tests.Add("Test", ssisTest);
var ssisAssert = new SsisAssert(target, ssisTest, "Test Count", "(int)result==2", false, true);
ssisAssert.Command = new SqlCommand(target, "AdventureWorks", true, "SELECT COUNT(*) FROM sys,.tables WHERE 1='test'");
ssisTest.Asserts.Add("Test Count", ssisAssert);
int testCount = 0;
try
{
testCount = target.Execute();
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
Assert.AreEqual(1, testCount);
Assert.AreEqual(1, target.Statistics.GetStatistic(StatisticEnum.TestPassedCount));
Assert.AreEqual(1, target.Statistics.GetStatistic(StatisticEnum.AssertPassedCount));
Assert.AreEqual(1, target.Statistics.GetStatistic(StatisticEnum.AssertFailedCount));
}
public void TestDynamicAssert()
{
var target = new SsisTestSuite();
target.ConnectionList.Add("AdventureWorks", new ConnectionRef("AdventureWorks",
"Provider=SQLNCLI11;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=tempdb",
ConnectionRef.ConnectionTypeEnum.ConnectionString));
target.PackageList.Add("UT Basic Scenario", new PackageRef("UT Basic Scenario", _dtsxFilePath, PackageStorageType.FileSystem));
target.TestSuiteSetup.Commands.Add(new SqlCommand(target, "AdventureWorks", false, "CREATE TABLE dbo.Test (ID INT)"));
target.TestSuiteSetup.Commands.Add(new SqlCommand(target, "AdventureWorks", false, "INSERT INTO dbo.Test VALUES (1)"));
target.TestSuiteSetup.Commands.Add(new SqlCommand(target, "AdventureWorks", false, "INSERT INTO dbo.Test VALUES (2)"));
var ssisTest = new Test(target, "Test", "UT Basic Scenario", null, "SELECT COUNT");
target.Tests.Add("Test", ssisTest);
var ssisAssert = new SsisAssert(target, ssisTest, "Test Count", "(int)result==2", false, true);
ssisAssert.Command = new SqlCommand(target, "AdventureWorks", true, "SELECT COUNT(*) FROM dbo.Test");
ssisTest.Asserts.Add("Test Count", ssisAssert);
ssisAssert = new SsisAssert(target, ssisTest, "Test Count 2", "(int)result<=2", false, true);
ssisAssert.Command = new SqlCommand(target, "AdventureWorks", true, "SELECT COUNT(*) FROM dbo.Test");
ssisTest.Asserts.Add("Test Count 2", ssisAssert);
ssisAssert = new SsisAssert(target, ssisTest, "Test Count 3", "\"test\"==\"test\"", false, true);
ssisAssert.Command = new SqlCommand(target, "AdventureWorks", true, "SELECT COUNT(*) FROM dbo.Test");
ssisTest.Asserts.Add("Test Count 3", ssisAssert);
ssisAssert = new SsisAssert(target, ssisTest, "Test Count 4", "DateTime.Now.Date==((DateTime)result).Date", false, true);
ssisAssert.Command = new VariableCommand(target, VariableCommand.VariableOperation.Get, "System::StartTime", string.Empty);
ssisTest.Asserts.Add("Test Count 4", ssisAssert);
target.TestSuiteTeardown.Commands.Add(new SqlCommand(target, "AdventureWorks", false, "DROP TABLE dbo.Test"));
int testCount = 0;
try
{
testCount = target.Execute();
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
Assert.AreEqual(1, testCount);
Assert.AreEqual(1, target.Statistics.GetStatistic(StatisticEnum.TestPassedCount));
Assert.AreEqual(5, target.Statistics.GetStatistic(StatisticEnum.AssertPassedCount));
Assert.AreEqual(0, target.Statistics.GetStatistic(StatisticEnum.AssertFailedCount));
}
public void ExecuteTest()
{
var target = new SsisTestSuite(Helper.CreateUnitTestStream(TestXmlFilename));
foreach (var test in target.Tests.Values)
{
test.PackageLocation = _testPackageFile;
}
target.Execute();
Assert.IsTrue(true);
}
public void CheckVariousPathsTest()
{
string packageFilepath;
#if SQL2005
packageFilepath = UnpackToFile("UTssisUnit.TestPackages.PropertyTest.dtsx");
#elif SQL2008
packageFilepath = UnpackToFile("UTssisUnit.TestPackages.PropertyTest.dtsx");
#elif SQL2014 || SQL2012
packageFilepath = UnpackToFile("UTssisUnit.TestPackages.PropertyTest2012.dtsx");
#elif SQL2017
packageFilepath = UnpackToFile("UTssisUnit.TestPackages.PropertyTest2017.dtsx");
#endif
var ts = new SsisTestSuite();
ts.PackageList.Add("PackageA", new PackageRef("PackageA", packageFilepath, PackageStorageType.FileSystem));
ts.SetupCommands.Commands.Add(new PropertyCommand(ts, "Set", @"\Package\Sequence Container\Script Task.Properties[Description]", "Test Descr"));
ts.SetupCommands.Commands.Add(new PropertyCommand(ts, "Set", @"\Package\Sequence Container.Properties[Description]", "Test Descr"));
ts.SetupCommands.Commands.Add(new PropertyCommand(ts, "Set", @"\Package\Execute SQL Task.Properties[Description]", "Test Descr"));
ts.SetupCommands.Commands.Add(new PropertyCommand(ts, "Set", @"\Package.Properties[CreationDate]", "2000-01-01"));
ts.SetupCommands.Commands.Add(new PropertyCommand(ts, "Set", @"\Package.Connections[localhost.AdventureWorksDW2008].Properties[Description]", "Test Descr"));
ts.SetupCommands.Commands.Add(new PropertyCommand(ts, "Set", @"\Package.EventHandlers[OnError].Variables[System::Cancel].Properties[Value]", false));
ts.SetupCommands.Commands.Add(new PropertyCommand(ts, "Set", @"\Package.EventHandlers[OnError].Properties[Description]", "Test Descr"));
ts.SetupCommands.Commands.Add(new PropertyCommand(ts, "Set", @"\Package.EventHandlers[OnError]\Script Task.Properties[Description]", "Test Descr"));
// Added to verify work item #7188 - multiple periods in object names
ts.SetupCommands.Commands.Add(new PropertyCommand(ts, "Set", @"\Package.Connections[test.multple.periods.in.path].Properties[Description]", "Test Descr"));
Test ssisTest = new Test(ts, "Test", "PackageA", null, "{7874CCC9-C3C6-40F5-9E8B-1DD62903D845}");
ts.Tests.Add("Test", ssisTest);
ts.Tests["Test"].Asserts.Add("TestA", AddNewAssert(ts, ssisTest, "TestA", "Test Descr", "\\Package\\Sequence Container\\Script Task.Properties[Description]"));
ts.Tests["Test"].Asserts.Add("TestB", AddNewAssert(ts, ssisTest, "TestB", "Test Descr", "\\Package\\Sequence Container.Properties[Description]"));
ts.Tests["Test"].Asserts.Add("TestC", AddNewAssert(ts, ssisTest, "TestC", "Test Descr", "\\Package\\Execute SQL Task.Properties[Description]"));
ts.Tests["Test"].Asserts.Add("TestD", AddNewAssert(ts, ssisTest, "TestD", new DateTime(2000, 1, 1), "\\Package.Properties[CreationDate]"));
ts.Tests["Test"].Asserts.Add("TestE", AddNewAssert(ts, ssisTest, "TestE", "Test Descr", "\\Package.Connections[localhost.AdventureWorksDW2008].Properties[Description]"));
ts.Tests["Test"].Asserts.Add("TestF", AddNewAssert(ts, ssisTest, "TestF", false, "\\Package.EventHandlers[OnError].Variables[System::Cancel].Properties[Value]"));
ts.Tests["Test"].Asserts.Add("TestG", AddNewAssert(ts, ssisTest, "TestG", "Test Descr", "\\Package.EventHandlers[OnError].Properties[Description]"));
ts.Tests["Test"].Asserts.Add("TestH", AddNewAssert(ts, ssisTest, "TestH", "Test Descr", "\\Package.EventHandlers[OnError]\\Script Task.Properties[Description]"));
ts.Tests["Test"].Asserts.Add("TestI", AddNewAssert(ts, ssisTest, "TestI", "Test Descr", "\\Package.Connections[test.multple.periods.in.path].Properties[Description]"));
var context = ts.CreateContext();
ts.Execute(context);
context.Log.ApplyTo(log => Debug.Print(log.ItemName + " :: " + string.Join(Environment.NewLine + "\t", log.Messages)));
Assert.AreEqual(10, ts.Statistics.GetStatistic(StatisticEnum.AssertPassedCount));
Assert.AreEqual(0, ts.Statistics.GetStatistic(StatisticEnum.AssertFailedCount));
}
public void TestExecuteWithBadPackage()
{
string packageFile = UnpackToFile("UTssisUnit.TestPackages.BadPackage.dtsx");
var target = new SsisTestSuite();
target.PackageList.Add("TestPkg", new PackageRef("TestPkg", packageFile, PackageStorageType.FileSystem));
target.Tests.Add("Test", new Test(target, "Test", "TestPkg", null, "Package"));
target.Tests["Test"].Asserts.Add("Assert", new SsisAssert(target, target.Tests["Test"], "Assert", true, false));
target.Tests["Test"].Asserts["Assert"].Command = new TestCommand();
target.Execute();
Assert.AreEqual(0, target.Statistics.GetStatistic(StatisticEnum.AssertPassedCount));
Assert.AreEqual(1, target.Statistics.GetStatistic(StatisticEnum.AssertFailedCount));
}
public void RunPackageFromFileTest()
{
var packageFile = UnpackToFile("UTssisUnit.TestPackages.SimplePackage.dtsx");
var ts = new SsisTestSuite();
ts.PackageList.Add("filePkg", new PackageRef("filePkg", packageFile, PackageStorageType.FileSystem));
var test = new Test(ts, "Main", "filePkg", null, "SimplePackage");
ts.Tests.Add("Main", test);
var assert = new SsisAssert(ts, test, "A1", 0, false);
test.Asserts.Add("A1", assert);
assert.Command = new VariableCommand(ts, VariableCommand.VariableOperation.Get, "RowCount", "0");
ts.Execute();
Assert.IsTrue(true);
}
public void RunPropertyCommandSetTest()
{
var ts = new SsisTestSuite(Helper.CreateUnitTestStream(TestXmlFilename));
var packageFile = UnpackToFile("UTssisUnit.TestPackages.PropertyTest.dtsx");
ts.PackageList["PropertyTest"].PackagePath = packageFile;
var target = (PropertyCommand)ts.SetupCommands.Commands[1];
var ssisApp = new Application();
var package = ssisApp.LoadPackage(ts.PackageList["PropertyTest"].PackagePath, null);
var container = package;
object actual = target.Execute(package, container);
Assert.AreEqual(1, actual);
Assert.AreEqual(1, package.Variables["TestInt"].Value);
ts.Execute();
Assert.AreEqual(3, ts.Statistics.GetStatistic(StatisticEnum.AssertPassedCount));
}
public int RunTest(string testCase, string outputFileName, string reportLevel, IDictionary <string, string> parameters)
{
if (reportLevel.ToUpper() == REPORT_LEVEL_PASSED)
{
_reportLevel = 1;
}
else if (reportLevel.ToUpper() == REPORT_LEVEL_FAILED)
{
_reportLevel = 2;
}
else
{
_reportLevel = 0;
}
var unitTest = new SsisTestSuite(testCase);
unitTest.TestCompleted += unitRunner_TestCompleted;
unitTest.AssertCompleted += unitTest_AssertCompleted;
if (_reportLevel == 0)
{
unitTest.SetupCompleted += unitTest_SetupCompleted;
unitTest.TeardownCompleted += unitTest_TeardownCompleted;
}
object[] vals = new object[] { "Execution Time", "Package Name", "Task Name", "Test Name", "Test Result", "Passed" };
Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}\t{5}", vals);
if (outputFileName != string.Empty)
{
this.outputFileName = outputFileName;
var outputFile = new StreamWriter(outputFileName, true);
outputFile.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}\t{5}", vals);
outputFile.Close();
}
unitTest.Execute(parameters);
if (unitTest.Statistics.GetStatistic(StatisticEnum.TestFailedCount) > 0)
{
return(1);
}
return(0);
}
public void DataFlowTaskWithExpressionTest()
{
var packageFile = UnpackToFile("UTssisUnit.TestPackages.TestDataFlowExpression2012.dtsx");
var newFileName = CreateTempFile(GetTempPath("Test", true), "TestDataFlowExpression2012.dtsx");
File.Copy(packageFile, newFileName, true);
var ts = new SsisTestSuite();
var target = new Test(ts, "DataFlowExpression", newFileName, null, "Data Flow Task");
ts.Tests.Add("Test Task That Fails", target);
var assert = new SsisAssert(ts, target, "Test Anything", "true==true", false, true);
target.Asserts.Add("Test Row Count", assert);
assert.Command = new PropertyCommand(ts, "Get", "\\Package.Properties[Description]", null);
ts.Execute();
Assert.AreEqual(2, ts.Statistics.GetStatistic(StatisticEnum.AssertPassedCount));
Assert.AreEqual(0, ts.Statistics.GetStatistic(StatisticEnum.AssertFailedCount));
}
public void TestSetupAndTeardownTest()
{
var target = new SsisTestSuite(Helper.CreateUnitTestStream("UTSsisUnit_TestSetup_Teardown.xml"));
target.ConnectionList["Sandbox"].ConnectionString =
"Provider=SQLNCLI11;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=tempdb";
target.PackageList["filePkg"].PackagePath = _testPackageFile;
try
{
target.Execute();
}
catch (Exception ex)
{
Assert.Fail(ex.Message);
}
Assert.AreEqual(2, target.Statistics.GetStatistic(StatisticEnum.AssertPassedCount));
Assert.AreEqual(1, target.Statistics.GetStatistic(StatisticEnum.TestCount));
}
public void RunSuite()
{
try
{
Show();
_testSuite.Execute();
lblTestCount.Text = "Test Count: " + _testSuite.Statistics.GetStatistic(StatisticEnum.TestCount).ToString("N0");
lblTestPassed.Text = "Tests Passed: " + _testSuite.Statistics.GetStatistic(StatisticEnum.TestPassedCount).ToString("N0");
lblTestsFailed.Text = "Tests Failed: " + _testSuite.Statistics.GetStatistic(StatisticEnum.TestFailedCount).ToString("N0");
lblAssertCount.Text = "Assert Count: " + _testSuite.Statistics.GetStatistic(StatisticEnum.AssertCount).ToString("N0");
lblAssertPassed.Text = "Asserts Passed: " + _testSuite.Statistics.GetStatistic(StatisticEnum.AssertPassedCount).ToString("N0");
lblAssertsFailed.Text = "Asserts Failed: " + _testSuite.Statistics.GetStatistic(StatisticEnum.AssertFailedCount).ToString("N0");
}
catch (Exception ex)
{
MessageBox.Show("The following error occurred when executing the test: " + ex.Message);
}
}
public void ProjectParamTest()
{
var projectFile = UnpackToFile("UTssisUnit.TestPackages.ISPACTesting.ispac", true);
var ts = new SsisTestSuite();
ts.ConnectionList.Add("AdventureWorks", new ConnectionRef("AdventureWorks", "Data Source=localhost;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI;Provider=SQLNCLI11", ConnectionRef.ConnectionTypeEnum.ConnectionString, "System.Data.SqlClient"));
ts.PackageList.Add("TestPackage", new PackageRef("TestPackage", "ExecuteSqlTask.dtsx", PackageStorageType.FileSystem));
ts.PackageList["TestPackage"].ProjectPath = projectFile;
var target = new Test(ts, "ExecuteSQL", "TestPackage", null, @"\Package\Test Param");
ts.Tests.Add("Test Execute SQL", target);
var assert = new SsisAssert(ts, target, "Test Output", 71, false);
target.Asserts.Add("Test Output", assert);
assert.Command = new SqlCommand(ts, "AdventureWorks", true, "SELECT COUNT(*) FROM sys.tables");
ts.Execute();
Assert.AreEqual(2, ts.Statistics.GetStatistic(StatisticEnum.AssertPassedCount));
Assert.AreEqual(0, ts.Statistics.GetStatistic(StatisticEnum.AssertFailedCount));
}
public void RunDataCompareCommandSetWithOtherTestTest()
{
var ts = new SsisTestSuite();
var connRef = new ConnectionRef("TestConn", "Data Source=localhost;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI", ConnectionRef.ConnectionTypeEnum.AdoNet, "System.Data.SqlClient");
var pkgRef = new PackageRef("pkg", _dtsxFilePath, PackageStorageType.FileSystem);
ts.ConnectionList.Add(connRef.ReferenceName, connRef);
ts.PackageList.Add(pkgRef.Name, pkgRef);
var dataset = new Dataset(
ts,
"Test",
connRef,
false,
@"SELECT
CAST(1 AS INT) AS ColInt,
CAST('Test' AS VARCHAR(50)) AS ColVarChar,
CAST(N'Test' AS NVARCHAR(50)) AS ColNVarChar,
CAST('1900-01-01' AS DATETIME) AS ColDateTime");
ts.Datasets.Add(dataset.Name, dataset);
var target = new DataCompareCommand(ts, "Test", dataset, dataset);
var test = new Test(ts, "TestCase1", "pkg", null, "\\Package");
var assert = new SsisAssert(ts, test, "Assert1", true, false);
assert.Command = target;
test.Asserts.Add("Assert1", assert);
ts.Tests.Add("TestCase1", test);
var test2 = new Test(ts, "TestCase2", "pkg", null, "\\Package");
var assert2 = new SsisAssert(ts, test2, "Assert2", false, false);
assert2.Command = new FileCommand(ts, "Exists", @"C:\Test\Test.pkg", string.Empty);
test2.Asserts.Add("Assert2", assert2);
ts.Tests.Add("TestCase2", test2);
var actual = ts.Execute();
Assert.AreEqual(4, ts.Statistics.GetStatistic(StatisticEnum.AssertPassedCount));
}
public void TaskThatFailsTest()
{
string packageFile = UnpackToFile("UTssisUnit.TestPackages.UTBasicScenario2012.dtsx");
SsisTestSuite ts = new SsisTestSuite();
ts.PackageList.Add("PackageA", new PackageRef("PackageA", packageFile, PackageStorageType.FileSystem));
Test target = new Test(ts, "Test Task That Fails", "PackageA", null, "SELECT COUNT", DTSExecResult.Failure);
target.TestSetup.Commands.Add(new PropertyCommand(ts, "Set", "\\Package\\SELECT COUNT.Properties[SqlStatementSource]", "SELECT ''"));
ts.Tests.Add("Test Task That Fails", target);
SsisAssert assert = new SsisAssert(ts, target, "Test Row Count", 504, false, false);
target.Asserts.Add("Test Row Count", assert);
assert.Command = new VariableCommand(ts, VariableCommand.VariableOperation.Get, "User::ProductRowCount", null);
ts.Execute();
Assert.AreEqual(1, ts.Statistics.GetStatistic(StatisticEnum.AssertPassedCount));
Assert.AreEqual(1, ts.Statistics.GetStatistic(StatisticEnum.AssertFailedCount));
}
public void DataFlowComponentTest()
{
Assert.Inconclusive();
var packageFile = UnpackToFile("UTssisUnit.TestPackages.DataFlowComponent.dtsx");
var newFileName = CreateTempFile(GetTempPath("Test", true), "TestDataFlowExpression2012.dtsx");
File.Copy(packageFile, newFileName, true);
var ts = new SsisTestSuite();
ts.ConnectionList.Add(
"AdventureWorks",
new ConnectionRef(
"AdventureWorks",
"Data Source=localhost;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI;",
ConnectionRef.ConnectionTypeEnum.AdoNet,
"System.Data.SqlClient"));
ts.Datasets.Add(
"testInput",
new Dataset(
ts,
"testInput",
ts.ConnectionList["AdventureWorks"],
false,
"SELECT " + "CAST(1 AS INT) AS ColInt, " + "CAST('Test' AS VARCHAR(50)) AS ColVarChar, " + "CAST(N'Test' AS NVARCHAR(50)) AS ColNVarChar, " + "CAST('1900-01-01' AS DATETIME) AS ColDateTime"));
var target = new Test(ts, "DataFlowComponent", newFileName, null, @"Package\Data Flow Task\Derived Column");
ts.Tests.Add("Test Data Flow Derived Column", target);
target.TestSetup.Commands.Add(new ComponentInputCommand(ts, "ComponentInput", "testInput", @"Package\Data Flow Task\Derived Column.Inputs[Derived Column Input]"));
var assert = new SsisAssert(ts, target, "Test Output", true, false);
target.Asserts.Add("Test Output", assert);
assert.Command = new ComponentOutputCommand(ts, "ComponentOutput", string.Empty, string.Empty);
ts.Execute();
Assert.AreEqual(2, ts.Statistics.GetStatistic(StatisticEnum.AssertPassedCount));
Assert.AreEqual(0, ts.Statistics.GetStatistic(StatisticEnum.AssertFailedCount));
}
public void CreateNewTestSuiteTest()
{
string packageFile = UnpackToFile(TestPackageResource);
var target = new SsisTestSuite();
Assert.AreEqual(0, target.ConnectionList.Count);
target.ConnectionList.Add("AdventureWorks", new ConnectionRef("AdventureWorks",
"Provider=SQLNCLI11;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=tempdb",
ConnectionRef.ConnectionTypeEnum.ConnectionString));
Assert.AreEqual(1, target.ConnectionList.Count);
Assert.AreEqual("AdventureWorks", target.ConnectionList["AdventureWorks"].ReferenceName);
Assert.AreEqual("Provider=SQLNCLI11;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=tempdb", target.ConnectionList["AdventureWorks"].ConnectionString);
Assert.AreEqual(ConnectionRef.ConnectionTypeEnum.ConnectionString, target.ConnectionList["AdventureWorks"].ConnectionType);
target.PackageList.Add("UT Basic Scenario", new PackageRef("UT Basic Scenario", packageFile, PackageStorageType.FileSystem));
target.TestSuiteSetup.Commands.Add(new SqlCommand(target, "AdventureWorks", false, "CREATE TABLE dbo.Test (ID INT)"));
target.TestSuiteSetup.Commands.Add(new SqlCommand(target, "AdventureWorks", false, "INSERT INTO dbo.Test VALUES (1)"));
target.TestSuiteSetup.Commands.Add(new SqlCommand(target, "AdventureWorks", false, "INSERT INTO dbo.Test VALUES (2)"));
var sb = new StringBuilder();
sb.AppendLine("Test Line Count 1");
sb.AppendLine("Test Line Count 2");
sb.AppendLine("Test Line Count 3");
string path = GetTempPath("FileTest", true);
string lineCountFile = CreateTempFile(path, "SourceFile.tst", sb.ToString());
var lineCount2File = GetTemporaryFileName();
var lineCount3File = GetTemporaryFileName();
target.SetupCommands.Commands.Add(new FileCommand(target, "Copy", lineCountFile, lineCount2File));
Assert.AreEqual(0, target.Tests.Count);
var ssisTest = new Test(target, "Test", "UT Basic Scenario", null, "SELECT COUNT");
target.Tests.Add("Test", ssisTest);
Assert.AreEqual(1, target.Tests.Count);
Assert.AreEqual("Test", target.Tests["Test"].Name);
Assert.AreEqual("UT Basic Scenario", target.Tests["Test"].PackageLocation);
Assert.AreEqual("SELECT COUNT", target.Tests["Test"].Task);
target.Tests["Test"].TestSetup.Commands.Add(new FileCommand(target, "Copy", lineCountFile, lineCount3File));
var ssisAssert = new SsisAssert(target, ssisTest, "Test Count", 2, false)
{
Command = new SqlCommand(target, "AdventureWorks", true, "SELECT COUNT(*) FROM dbo.Test")
};
ssisTest.Asserts.Add("Test Count", ssisAssert);
Assert.AreEqual(1, ssisTest.Asserts.Count);
Assert.AreEqual("Test Count", ssisTest.Asserts["Test Count"].Name);
Assert.AreEqual(2, ssisTest.Asserts["Test Count"].ExpectedResult);
Assert.AreEqual(false, ssisTest.Asserts["Test Count"].TestBefore);
Assert.AreEqual("<SqlCommand name=\"\" connectionRef=\"AdventureWorks\" returnsValue=\"true\">SELECT COUNT(*) FROM dbo.Test</SqlCommand>", ssisTest.Asserts["Test Count"].Command.PersistToXml());
ssisAssert = new SsisAssert(target, ssisTest, "Test File", true, false)
{
Command = new FileCommand(target, "Exists", lineCount2File, string.Empty)
};
ssisTest.Asserts.Add("Test File", ssisAssert);
ssisAssert = new SsisAssert(target, ssisTest, "Test File 2", true, false)
{
Command = new FileCommand(target, "Exists", lineCount3File, string.Empty)
};
ssisTest.Asserts.Add("Test File 2", ssisAssert);
target.Tests["Test"].TestTeardown.Commands.Add(new FileCommand(target, "Delete", lineCount3File, string.Empty));
// TODO: Add a TestRef test
target.TeardownCommands.Commands.Add(new FileCommand(target, "Delete", lineCount2File, string.Empty));
target.TestSuiteTeardown.Commands.Add(new SqlCommand(target, "AdventureWorks", false, "DROP TABLE dbo.Test"));
var saveFile = GetTemporaryFileName();
target.Save(saveFile);
Assert.IsTrue(File.Exists(saveFile));
int testCount = target.Execute();
Assert.AreEqual(1, testCount);
Assert.AreEqual(1, target.Statistics.GetStatistic(StatisticEnum.TestPassedCount));
Assert.AreEqual(4, target.Statistics.GetStatistic(StatisticEnum.AssertPassedCount));
Assert.AreEqual(0, target.Statistics.GetStatistic(StatisticEnum.AssertFailedCount));
Assert.IsFalse(File.Exists(lineCount2File));
// TODO: add ability to gracefully handle bad package refs - right now it blows the test case out of the water - no teardown
target = new SsisTestSuite(saveFile);
testCount = target.Execute();
Assert.AreEqual(1, testCount);
Assert.AreEqual(1, target.Statistics.GetStatistic(StatisticEnum.TestPassedCount));
Assert.AreEqual(4, target.Statistics.GetStatistic(StatisticEnum.AssertPassedCount));
Assert.AreEqual(0, target.Statistics.GetStatistic(StatisticEnum.AssertFailedCount));
Assert.IsFalse(File.Exists(lineCount2File));
}
public void SQL_MERGE_Users_Empty_table()
{
// a new test suite
SsisTestSuite ts = new SsisTestSuite();
// the package to test; three times .., because test dll is in .\bin\Development
PackageRef p = new PackageRef(
"15_Users_Dataset",
@"..\..\..\Assets\ispac\ssisUnitLearning.ispac",
"15_Users_Dataset.dtsx",
PackageStorageType.FileSystem
);
// the connection for the datasets
ConnectionRef c = new ConnectionRef(
"ssisUnitLearningDB",
@"Provider=SQLNCLI11.1;Data Source=.\SQL2017;Integrated Security=SSPI;Initial Catalog=ssisUnitLearningDB;Auto Translate=False",
ConnectionRef.ConnectionTypeEnum.ConnectionString
);
// let the test suite know about the ConnectionRef and the PackageRef
ts.ConnectionList.Add(c.ReferenceName, c);
ts.PackageList.Add(p.Name, p);
// expected and actual datasets
Dataset expected = new Dataset(ts, "Empty table test: expected dataset", c, false, @"SELECT *
FROM(
VALUES
(CAST('Name 1' AS VARCHAR(50)), CAST('Login 1' AS CHAR(12)), CAST(1 AS BIT), CAST(1 AS INT), CAST(2 AS TINYINT), CAST(0 AS BIT)),
(CAST('Name 2' AS VARCHAR(50)), CAST('Login 2' AS CHAR(12)), CAST(1 AS BIT), CAST(2 AS INT), CAST(2 AS TINYINT), CAST(0 AS BIT)),
(CAST('Name 3' AS VARCHAR(50)), CAST('Login 3' AS CHAR(12)), CAST(0 AS BIT), CAST(3 AS INT), CAST(2 AS TINYINT), CAST(0 AS BIT))
)x(Name, Login, IsActive, Id, SourceSystemId, IsDeleted)
ORDER BY Id; ");
Dataset actual = new Dataset(ts, "Empty table test: actual dataset", c, false, @"SELECT
Name,
Login,
IsActive,
SourceId,
SourceSystemId,
IsDeleted
FROM dbo.Users
ORDER BY SourceId;");
// add the datasets to the test suite
ts.Datasets.Add(expected.Name, expected);
ts.Datasets.Add(actual.Name, actual);
// the test
Test t = new Test(ts, "SQL MERGE Users: Empty table", "15_Users_Dataset", null, "{FB549B65-6F0D-4794-BA8E-3FF975A6AE0B}");
ts.Tests.Add(t.Name, t);
// test setup
SqlCommand s1 = new SqlCommand(ts, "ssisUnitLearningDB", false, @"WITH stgUsers AS (
SELECT *
FROM (
VALUES
('Name 1', 'Login 1', 1, 1, 2, -1),
('Name 2', 'Login 2', 1, 2, 2, -1),
('Name 3', 'Login 3', 0, 3, 2, -1)
)x (Name, Login, IsActive, Id, SourceSystemId, InsertedAuditId)
)
INSERT INTO stg.Users (
Name, Login, IsActive, Id, SourceSystemId, InsertedAuditId
)
SELECT
Name, Login, IsActive, Id, SourceSystemId, InsertedAuditId
FROM stgUsers
;");
// add the setup to the test
t.TestSetup.Commands.Add(s1);
// test asserts and asserts' commands
SsisAssert a1 = new SsisAssert(ts, t, "Assert: Added 3 records", 3, false);
a1.Command = new SqlCommand(ts, "ssisUnitLearningDB", true, "SELECT COUNT(*) FROM dbo.Users;");
SsisAssert a2 = new SsisAssert(ts, t, "Assert: dbo.Users has expected records", true, false);
a2.Command = new DataCompareCommand(ts, "", expected, actual);
// add the asserts to the test
t.Asserts.Add(a1.Name, a1);
t.Asserts.Add(a2.Name, a2);
// test teardown
SqlCommand t1 = new SqlCommand(ts, "ssisUnitLearningDb", false, "TRUNCATE TABLE stg.Users;");
SqlCommand t2 = new SqlCommand(ts, "ssisUnitLearningDb", false, "TRUNCATE TABLE dbo.Users;");
// add the teardown commands to the test
// add the setup to the test
t.TestTeardown.Commands.Add(t1);
t.TestTeardown.Commands.Add(t2);
// execute the test suite
ts.Execute();
// verify if everything is OK; we expect 3 asserts to pass
Assert.AreEqual(3, ts.Statistics.GetStatistic(StatisticEnum.AssertPassedCount));
// show me the XML version of the test - only in debug mode (internal method)
//System.Console.Write(ts.PersistToXml);
}
public void TestMethod()
{
var testSuite = new SsisTestSuite();
var projectPath = config.ProjectPath;
var template = "Product.dtsx";
var templatePath = config.TemplatePath;
PackageRef p = new PackageRef("ProductPackage", projectPath, template, SsisUnit.Enums.PackageStorageType.FileSystem);
var conn = new System.Data.SqlClient.SqlConnectionStringBuilder(config.ConnectionString);
var DatabaseConnectionString = $"{ conn.ConnectionString};Provider=SQLNCLI11.1;Auto Translate=False;Integrated Security =SSPI;";
ConnectionRef con1 = new ConnectionRef("Destination",
DatabaseConnectionString,
ConnectionRef.ConnectionTypeEnum.ConnectionString
);
var originalProductListConnection = new PropertyCommand(testSuite)
{
Name = "OriginalProductList",
PropertyPath = @"\Package.Connections[OriginalProductList].Properties[ConnectionString]",
Operation = PropertyCommand.PropertyOperation.Set,
Value = config.OriginalFilePath
};
//var stagedProductListConnection = new PropertyCommand(testSuite)
//{
// Name="StagedProductList",
// PropertyPath = @"\Package.Connections[StagedProductList].Properties[ConnectionString]",
// Operation =PropertyCommand.PropertyOperation.Set,
// Value= @"C:\Users\nishant-mishra\source\repos\SSIS_UnitTesting\temp\ProductList.txt"
//};
testSuite.SetupCommands.Commands.Add(originalProductListConnection);
//testSuite.SetupCommands.Commands.Add(stagedProductListConnection);
testSuite.ConnectionList.Add(con1.ReferenceName, con1);
testSuite.PackageList.Add(p.Name, p);
//VariableCommand var1 = new VariableCommand(testSuite)
//{
// Name = "fileExists",
// Operation = VariableCommand.VariableOperation.Set,
// Value = "false"
//};
//testSuite.SetupCommands.Commands.Add(var1);
Test test1 = new Test(testSuite, "Verify No of RecordsinTable", templatePath + template, null, "{74BF5B2C-1017-4015-8B4A-36EEEFD44C06}");
testSuite.Tests.Add(test1.Name, test1);
//SqlCommand command1 = new SqlCommand(testSuite, "Destination", false, "select count(1) from dbo.Product");
//test1.TestSetup.Commands.Add(command1);
var assert1 = new SsisAssert(testSuite, test1, "Assert: Product has records", 6, false)
{
Command = new SqlCommand(testSuite, "Destination", true, "select count(1) from dbo.Product;")
};
test1.Asserts.Add(assert1.Name, assert1);
testSuite.Execute();
Assert.AreEqual(2, testSuite.Statistics.GetStatistic(SsisUnitBase.Enums.StatisticEnum.AssertPassedCount));
}
