public ComponentInputCommand(SsisTestSuite testSuite)
: base(testSuite)
{
InitializeProperties();
}
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 ComponentOutputCommand(SsisTestSuite testSuite, object parent, string commandXml)
: base(testSuite, parent, commandXml)
{
InitializeProperties();
}
public ComponentOutputCommand(SsisTestSuite testSuite, XmlNode commandXml)
: base(testSuite, commandXml)
{
InitializeProperties();
}
internal DynamicValues(SsisTestSuite testSuite)
{
TestSuite = testSuite;
}
public void SsisTestCaseConstructorTest()
{
var target = new SsisTestSuite(Helper.CreateUnitTestStream(TestXmlFilename));
Assert.IsNotNull(target);
}
public static void Init(TestContext tc)
{
// ssisUnitLearningMSTest.dll is in subfolder bin\Debug and Tests folder is parallel, that's why ..\..\..
testSuite = new SsisTestSuite(@"..\..\..\Tests\20_DataFlow.ssisUnit");
}
public static void Init(TestContext tc)
{
// ssisUnitLearningMSTest.dll is in subfolder bin\Debug and Tests folder is parallel, that's why ..\..\..
//System.Console.WriteLine(System.Environment.CurrentDirectory);
testSuite = new SsisTestSuite(@"..\..\..\Tests\05_SQLTask_LocalCM.ssisUnit");
}
public void AddTestSuite(string fileName)
{
_testSuite = new SsisTestSuite(fileName);
LoadTest(_testSuite, fileName);
}
public new void Setup()
{
base.Setup();
_testSuite = new SsisTestSuite();
}
public void CreateTest()
{
var newTs = new SsisTestSuite();
LoadTest(newTs, GetUnusedName());
}
internal void AddTestSuite(SsisTestSuite ts)
{
LoadTest(ts, GetUnusedName());
}
public void PersistDataSetWithResultsIsResultsStoredFalseTest()
{
var ts = new SsisTestSuite();
var connRef = new ConnectionRef("TestConn", "Data Source=localhost;Initial Catalog=AdventureWorks2012;Integrated Security=SSPI", ConnectionRef.ConnectionTypeEnum.AdoNet, "System.Data.SqlClient");
ts.ConnectionList.Add(connRef.ReferenceName, connRef);
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");
dataset.Results = dataset.RetrieveDataTable();
string datasetXml = @"<Dataset name=""Test"" connection=""TestConn"" isResultsStored=""false""><query><![CDATA[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]]></query><results><![CDATA[<NewDataSet>
<xs:schema id=""NewDataSet"" xmlns="""" xmlns:xs=""http://www.w3.org/2001/XMLSchema"" xmlns:msdata=""urn:schemas-microsoft-com:xml-msdata"">
<xs:element name=""NewDataSet"" msdata:IsDataSet=""true"" msdata:MainDataTable=""Results"" msdata:UseCurrentLocale=""true"">
<xs:complexType>
<xs:choice minOccurs=""0"" maxOccurs=""unbounded"">
<xs:element name=""Results"">
<xs:complexType>
<xs:sequence>
<xs:element name=""ColInt"" msdata:ReadOnly=""true"" type=""xs:int"" minOccurs=""0"" />
<xs:element name=""ColVarChar"" msdata:ReadOnly=""true"" minOccurs=""0"">
<xs:simpleType>
<xs:restriction base=""xs:string"">
<xs:maxLength value=""50"" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name=""ColNVarChar"" msdata:ReadOnly=""true"" minOccurs=""0"">
<xs:simpleType>
<xs:restriction base=""xs:string"">
<xs:maxLength value=""50"" />
</xs:restriction>
</xs:simpleType>
</xs:element>
<xs:element name=""ColDateTime"" msdata:ReadOnly=""true"" type=""xs:dateTime"" minOccurs=""0"" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
</xs:element>
</xs:schema>
<Results>
<ColInt>1</ColInt>
<ColVarChar>Test</ColVarChar>
<ColNVarChar>Test</ColNVarChar>
<ColDateTime>1900-01-01T00:00:00+01:00</ColDateTime>
</Results>
</NewDataSet>]]></results></Dataset>";
string result = dataset.PersistToXml();
Assert.AreEqual(datasetXml, result);
}
public ComponentInputCommand(SsisTestSuite testSuite, string commandXml)
: base(testSuite, commandXml)
{
InitializeProperties();
}
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 ComponentInputCommand(SsisTestSuite testSuite, object parent, XmlNode commandXml)
: base(testSuite, parent, commandXml)
{
InitializeProperties();
}
// class has to be like in the error message
// Message: Method ssisUnitLearningMSTest.TestUnit_01_OnlyParametersAndVariables.Init has wrong signature.
// The method must be static, public, does not return a value and should take a single parameter of type TestContext.
// Additionally, if you are using async-await in method then return-type must be Task.
public static void Init(TestContext tc)
{
// ssisUnitLearningMSTest.dll is in subfolder bin\Debug and Tests folder is parallel, that's why ..\..\..
testSuite = new SsisTestSuite(@"..\..\..\Tests\01_OnlyParametersAndVariables.ssisUnit");
}
