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 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 AddAssert()
{
if (!(treeTest.SelectedNode.Tag is Test))
{
return;
}
Test test = (Test)treeTest.SelectedNode.Tag;
int counter = 1;
while (test.Asserts.ContainsKey("Assert" + counter.ToString(CultureInfo.InvariantCulture)))
{
counter++;
}
SsisAssert assert = new SsisAssert(_testSuite, test, "Assert" + counter.ToString(CultureInfo.InvariantCulture), null, false);
test.Asserts.Add(assert.Name, assert);
TreeNode tn = new TreeNode(assert.Name)
{
Tag = assert
};
treeTest.SelectedNode.Nodes.Add(tn);
tn.EnsureVisible();
treeTest.SelectedNode = tn;
}
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 TestAssertCreation()
{
string assertXml = "<Assert name=\"Test\" expectedResult=\"\" testBefore=\"false\" expression=\"false\" ";
assertXml += "/>";
var testSuite = new SsisTestSuite();
var ssisTest = new Test(testSuite, "Test", _dtsxFilePath, null, "SELECT COUNT");
var ssisAssert = new SsisAssert(testSuite, ssisTest, "Test", null, false, false);
Assert.AreEqual(assertXml, ssisAssert.PersistToXml());
}
public void PersistToXmlTest()
{
string assertXml = "<Assert name=\"Test\" expectedResult=\"1\" testBefore=\"false\" expression=\"false\">";
assertXml += "<SqlCommand name=\"\" connectionRef=\"AdventureWorks\" returnsValue=\"true\">";
assertXml += "SELECT COUNT(*) FROM Production.Product";
assertXml += "</SqlCommand>";
assertXml += "</Assert>";
var testSuite = new SsisTestSuite();
var ssisTest = new Test(testSuite, "Test", _dtsxFilePath, null, "SELECT COUNT");
var target = new SsisAssert(testSuite, ssisTest, assertXml);
Assert.AreEqual(assertXml, target.PersistToXml());
}
private void TreeTestBeforeSelect(object sender, TreeViewCancelEventArgs e)
{
if (treeTest.SelectedNode == null)
{
return;
}
_originalItem = treeTest.SelectedNode.Tag;
IValidate item = _originalItem as IValidate;
if (item != null)
{
Validate(item, treeTest.SelectedNode);
}
// Update Tree for label changes
ConnectionRef connectionRef = _originalItem as ConnectionRef;
if (connectionRef != null)
{
treeTest.SelectedNode.Text = connectionRef.ReferenceName;
return;
}
PackageRef packageRef = _originalItem as PackageRef;
if (packageRef != null)
{
treeTest.SelectedNode.Text = packageRef.Name;
return;
}
Test test = _originalItem as Test;
if (test != null)
{
treeTest.SelectedNode.Text = test.Name;
return;
}
SsisAssert ssisAssert = _originalItem as SsisAssert;
if (ssisAssert != null)
{
treeTest.SelectedNode.Text = ssisAssert.Name;
}
}
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 SsisAssertConstructorTest2()
{
var testSuite = new SsisTestSuite();
var ssisTest = new Test(testSuite, "Test", _dtsxFilePath, null, "SELECT COUNT");
string assertXml = "<Assert name=\"Test\" expectedResult=\"1\" testBefore=\"false\" expression=\"false\">";
assertXml += "<SqlCommand name=\"\" connectionRef=\"AdventureWorks\" returnsValue=\"true\">";
assertXml += "SELECT COUNT(*) FROM Production.Product";
assertXml += "</SqlCommand>";
assertXml += "</Assert>";
var target = new SsisAssert(testSuite, ssisTest, assertXml);
Assert.AreEqual("Test", target.Name);
Assert.AreEqual("1", target.ExpectedResult.ToString());
Assert.AreEqual(false, target.TestBefore);
Assert.AreEqual("SqlCommand", target.Command.CommandName);
}
public void SsisAssertConstructorTest()
{
var testSuite = new SsisTestSuite();
const string Name = "Test";
object expectedResult = 100;
var ssisTest = new Test(testSuite, "Test", "C:\\Projects\\SSISUnit\\SSIS2005\\SSIS2005\\UT Basic Scenario.dtsx", null, "SELECT COUNT");
testSuite.Tests.Add("Test", ssisTest);
var target = new SsisAssert(testSuite, ssisTest, Name, expectedResult, false);
ssisTest.Asserts.Add("Test", target);
Assert.AreEqual(1, ssisTest.Asserts.Count);
Assert.AreEqual("Test", ssisTest.Asserts["Test"].Name);
Assert.AreEqual(100, ssisTest.Asserts["Test"].ExpectedResult);
Assert.AreEqual(false, ssisTest.Asserts["Test"].TestBefore);
}
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 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 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 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 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));
}
private TreeNode CreateAssertNode(SsisAssert assert)
{
TreeNode assertNode = new TreeNode(assert.Name)
{
Name = assert.Name, Tag = assert
};
if (!assert.Validate())
{
assertNode.ForeColor = Color.Red;
assertNode.ToolTipText = assert.ValidationMessages;
}
if (assert.Command != null)
{
TreeNode commandNode = new TreeNode(assert.Command.CommandName)
{
Tag = assert.Command
};
assertNode.Nodes.Add(commandNode);
}
return(assertNode);
}
public void DeleteItem()
{
if (treeTest.SelectedNode.Name == "Connection List" ||
treeTest.SelectedNode.Name == "Package List" ||
treeTest.SelectedNode.Name == "Dataset List" ||
treeTest.SelectedNode.Name == "Test Suite Setup" ||
treeTest.SelectedNode.Name == "Setup" ||
treeTest.SelectedNode.Name == "Test" ||
treeTest.SelectedNode.Name == "Teardown" ||
treeTest.SelectedNode.Name == "Test Suite Teardown")
{
return;
}
CommandSet commandSet = treeTest.SelectedNode.Parent == null ? null : treeTest.SelectedNode.Parent.Tag as CommandSet;
if (commandSet != null)
{
commandSet.Commands.Remove((CommandBase)treeTest.SelectedNode.Tag);
treeTest.SelectedNode.Remove();
return;
}
ConnectionRef connectionRef = treeTest.SelectedNode.Tag as ConnectionRef;
if (connectionRef != null)
{
ConnectionRef cref = connectionRef;
_testSuite.ConnectionList.Remove(cref.ReferenceName);
treeTest.SelectedNode.Remove();
return;
}
PackageRef packageRef = treeTest.SelectedNode.Tag as PackageRef;
if (packageRef != null)
{
_testSuite.PackageList.Remove(packageRef.Name);
treeTest.SelectedNode.Remove();
return;
}
Dataset dataset = treeTest.SelectedNode.Tag as Dataset;
if (dataset != null)
{
_testSuite.Datasets.Remove(dataset.Name);
treeTest.SelectedNode.Remove();
return;
}
Test unitTest = treeTest.SelectedNode.Tag as Test;
if (unitTest != null)
{
_testSuite.Tests.Remove(unitTest.Name);
treeTest.SelectedNode.Remove();
return;
}
SsisAssert ssisAssert = treeTest.SelectedNode.Tag as SsisAssert;
if (ssisAssert != null)
{
if (treeTest.SelectedNode.Parent == null)
{
return;
}
Test cs = (Test)treeTest.SelectedNode.Parent.Tag;
cs.Asserts.Remove(ssisAssert.Name);
treeTest.SelectedNode.Remove();
return;
}
if (treeTest.SelectedNode.Parent == null || !(treeTest.SelectedNode.Parent.Tag is SsisAssert))
{
return;
}
SsisAssert assert = (SsisAssert)treeTest.SelectedNode.Parent.Tag;
assert.Command = null;
treeTest.SelectedNode.Remove();
}
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));
}
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 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));
}
