public void TestLoggingWithOdbc()
{
ControlFlow.CurrentDbConnection = new OdbcConnectionManager(new OdbcConnectionString(OdbcConnectionStringParameter));
CreateLogTablesTask.CreateLog();
LogTask.Info("Info");
Assert.AreEqual(1, SqlTask.ExecuteScalar <int>("Check if default log works", "select count(*) from etl.Log where Message in ('Error','Warn','Info')"));
}
public void Start()
{
//Recreate database
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;"));
DropDatabaseTask.Drop("DemoDB");
CreateDatabaseTask.Create("DemoDB");
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString("Data Source=.;Integrated Security=SSPI;Initial Catalog=DemoDB;"));
//Logging (only works with existing configuration nlog config in App.config)
CreateLogTablesTask.CreateLog();
StartLoadProcessTask.Start("Process 1");
ControlFlow.STAGE = "Staging";
SqlTask.ExecuteNonQuery("some sql", "Select 1 as test");
TransferCompletedForLoadProcessTask.Complete();
ControlFlow.STAGE = "DataVault";
Sequence.Execute("some custom code", () => { });
LogTask.Warn("Some warning!");
EndLoadProcessTask.End("Everything successful");
string jsonLP = GetLoadProcessAsJSONTask.GetJSON();
string jsonLog = GetLogAsJSONTask.GetJSON(1);
}
public static void TestInit(TestContext testContext)
{
ControlFlow.ClearSettings();
TestHelper.RecreateDatabase(testContext);
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString(testContext.Properties["connectionString"].ToString()));
CreateLogTablesTask.CreateLog();
}
public void TestRemoveLogTablesTask()
{
RemoveLogTablesTask.Remove();
CreateLogTablesTask.CreateLog();
RemoveLogTablesTask.Remove();
Assert.IsTrue(SqlTask.ExecuteScalarAsBool("Check if tables are deleted", "select case when object_id('etl.LoadProcess') is null then 1 else 0 end"));
Assert.IsTrue(SqlTask.ExecuteScalarAsBool("Check if tables are deleted", "select case when object_id('etl.Log') is null then 1 else 0 end"));
}
public async Task Handle(string request)
{
//string sC = "data source=QHB-CRMDB001.centralservices.local;initial catalog=eInsightCRM_OceanProperties_QA;uid=eInsightCRM_eContact_OceanProperties;pwd=Tv3CxdZwA%9;MultipleActiveResultSets=True";
//string dC = "data source=localhost;initial catalog=eInsightCRM_AMResorts_QA;uid=sa;pwd=123456;MultipleActiveResultSets=True";
//string dT = "dbo.D_Customer";
//string sql = "SELECT TOP 20 CustomerID, FirstName, LastName, Email, PropertyCode, InsertDate, SourceID, AddressStatus, DedupeCheck, AllowEMail, Report_Flag, UNIFOCUS_SCORE FROM dbo.D_Customer with(Nolock);";
//string sql1 = "SELECT TOP 20 CustomerID, FirstName, LastName, Email, PropertyCode, InsertDate, SourceID, AddressStatus, DedupeCheck, AllowEMail, Report_Flag, UNIFOCUS_SCORE FROM dbo.D_Customer_03092017 with(Nolock);";
//List<Dictionary<string,string>> SQL_GetDataFromPMS_SpecialRequestsList = SQLHelper.GetDbValues(sC, "SQL_GetDataFromPMS_SpecialRequests", sql, null);
//new DataFlowTask<D_Customer>().runTask(sC, dC, dT, sql, true, true, new List<string>() { "FirstName", "LastName" }, new List<string>() { "CustomerID", "FirstName", "LastName", "Email", "PropertyCode", "InsertDate", "SourceID", "AddressStatus", "DedupeCheck", "AllowEMail", "Report_Flag", "UNIFOCUS_SCORE" });
// new DataFlowTask<D_Customer>().runTask1<D_Customer, D_Customer, D_Customer>(sC, dC, dT, sql, sql1, true, true, new List<string>() { "FirstName"}, new List<string>() { "CustomerID", "FirstName", "LastName", "Email", "PropertyCode", "InsertDate", "SourceID", "AddressStatus", "DedupeCheck", "AllowEMail", "Report_Flag", "UNIFOCUS_SCORE" });
try
{
string eContactLogConnectionString = System.Configuration.ConfigurationManager.AppSettings["econtact-log-db-sql"];
ControlFlow.CurrentDbConnection = new SqlConnectionManager(new ConnectionString(eContactLogConnectionString));
CreateLogTablesTask.CreateLog();
StartLoadProcessTask.Start("Process 1", "Start Message 1", "ETL");
var settings = JsonConvert.DeserializeObject <Dictionary <string, object> >(request);
ControlFlow.STAGE = "0";
eContactDBManager.GetCompanySetting(settings);
ControlFlow.SetLoggingDatabase(new SqlConnectionManager(eContactLogConnectionString));
//Customer
CustomerTask CT = new CustomerTask();
CT.Start();
}
catch (Exception ex)
{
ControlFlow.STAGE = "0";
new LogTask()
{
Message = $"Exception({ex.Message}) Occurred. Task Stopped!", ActionType = "Stop"
}.Info();
NLog.LogManager.GetCurrentClassLogger().Fatal(ex);
}
//Console.WriteLine("Starting ControlFlow example");
//ControlFlowTasks cft = new ControlFlowTasks();
//cft.Start();
//Console.WriteLine("ControlFlow finished...");
//Console.WriteLine("Start Logging example");
//Logging log = new Logging();
//log.Start();
//Console.WriteLine("Logging finished...");
//Console.WriteLine("Starting DataFlow example");
//DataFlowTasks dft = new DataFlowTasks();
//dft.Preparation();
//dft.Start();
//Console.WriteLine("Dafaflow finished...");
//Console.WriteLine("Press any key to continue...");
//Console.ReadLine();
}
public void TestLogging()
{
CreateLogTablesTask.CreateLog();
TruncateTableTask.Truncate("dbo.TRC");
Assert.IsTrue(new SqlTask("Find log entry", "select count(*) from etl.Log where TaskAction = 'START' and TaskType='TRUNCATE'")
{
DisableLogging = true
}.ExecuteScalarAsBool());
}
public void TestLogging()
{
CreateLogTablesTask.CreateLog();
CRUDProcedureTask.CreateOrAlter("test.Test4", "select 1 as Test");
Assert.AreEqual(4, new SqlTask("Find log entry", "select count(*) from etl.Log where TaskType='CRUDPROC' group by TaskHash")
{
DisableLogging = true
}.ExecuteScalar <int>());
}
public void TestLogging()
{
CreateLogTablesTask.CreateLog();
CreateSchemaTask.Create("s" + TestHelper.RandomString(9));
Assert.AreEqual(2, new SqlTask("Find log entry", "select count(*) from etl.Log where TaskType='CREATESCHEMA' group by TaskHash")
{
DisableLogging = true
}.ExecuteScalar <int>());
}
public void TestLogging()
{
CreateLogTablesTask.CreateLog();
Sequence.Execute("Test sequence 3", Action1);
Assert.AreEqual(2, new SqlTask("Find log entry", "select count(*) from etl.Log where TaskType='SEQUENCE' group by TaskHash")
{
DisableLogging = true
}.ExecuteScalar <int>());
}
public void TestLogging()
{
CreateLogTablesTask.CreateLog();
CustomTask.Execute("Test custom task 4", Action1);
Assert.AreEqual(2, new SqlTask("Find log entry", "select count(*) from etl.Log where TaskType='CUSTOM' group by TaskHash")
{
DisableLogging = true
}.ExecuteScalar <int>());
}
public void TestLogging()
{
TestHelper.RecreateDatabase(TestContext);
CreateLogTablesTask.CreateLog();
ProcessCubeTask.Process("Process cube test");
Assert.AreEqual(2, new SqlTask("Find log entry", "select count(*) from etl.Log where TaskType='PROCESSCUBE' group by TaskHash")
{
DisableLogging = true
}.ExecuteScalar <int>());
}
public void TestLogging()
{
RemoveLogTablesTask.Remove();
CreateLogTablesTask.CreateLog();
SqlTask.ExecuteNonQuery("Test select", $"select 1 as test");
Assert.AreEqual(2, new SqlTask("Find log entry", "select count(*) from etl.Log where TaskType='SQL' group by TaskHash")
{
DisableLogging = true
}.ExecuteScalar <int>());
}
public void TestTransferAndLogging()
{
//CurrentDbConnection is always use if ConnectionManager is not specified otherwise!
ControlFlow.CurrentDbConnection = new SqlConnectionManager(ConnectionStringSource);
SqlTask.ExecuteNonQuery("Create source table", @"CREATE TABLE test.Source
(Col1 nvarchar(100) null, Col2 int null)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test1',1)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test2',2)");
SqlTask.ExecuteNonQuery("Insert demo data", "insert into test.Source values('Test3',3)");
ControlFlow.CurrentDbConnection = new SqlConnectionManager(ConnectionStringLog);
CreateLogTablesTask.CreateLog();
new CreateTableTask("test.Destination", new List <ITableColumn>()
{
new TableColumn("Col1", "nvarchar(100)", allowNulls: false),
new TableColumn("Col2", "int", allowNulls: true)
})
{
ConnectionManager = new SqlConnectionManager(ConnectionStringDest)
}.Execute();
var sourceConnection = new SqlConnectionManager(new ConnectionString(ConnectionStringSource));
var destConnection = new SqlConnectionManager(new ConnectionString(ConnectionStringDest));
DBSource source = new DBSource(sourceConnection, "test.Source");
RowTransformation trans = new RowTransformation(row =>
{
LogTask.Info($"Test message: {row[0]}, {row[1]}"); //Log DB is used as this is the ControlFlow.CurrentDBConnection!
return(row);
});
DBDestination destination = new DBDestination(destConnection, "test.Destination");
source.LinkTo(trans);
trans.LinkTo(destination);
source.Execute();
destination.Wait();
Assert.AreEqual(1, new RowCountTask("test.Destination", "Col1 = 'Test1' AND Col2=1")
{
ConnectionManager = new SqlConnectionManager(ConnectionStringDest)
}.Count().Rows);
Assert.AreEqual(1, new RowCountTask("test.Destination", "Col1 = 'Test2' AND Col2=2")
{
ConnectionManager = new SqlConnectionManager(ConnectionStringDest)
}.Count().Rows);
Assert.AreEqual(1, new RowCountTask("test.Destination", "Col1 = 'Test3' AND Col2=3")
{
ConnectionManager = new SqlConnectionManager(ConnectionStringDest)
}.Count().Rows);
}
public void TestLogging()
{
CreateLogTablesTask.CreateLog();
CreateIndexTask.Create("ix_" + TestHelper.RandomString(5), "test.Table1", new List <string>()
{
"key1", "key2"
});
Assert.AreEqual(2, new SqlTask("Find log entry", "select count(*) from etl.Log where TaskType='CREATEINDEX' group by TaskHash")
{
DisableLogging = true
}.ExecuteScalar <int>());
}
public void TestLogging()
{
CreateLogTablesTask.CreateLog();
CreateTableTask.Create("test.Table8", new List <TableColumn>()
{
new TableColumn("value", "int")
});
Assert.AreEqual(2, new SqlTask("Find log entry", "select count(*) from etl.Log where TaskType='CREATETABLE' group by TaskHash")
{
DisableLogging = true
}.ExecuteScalar <int>());
}
public void CreateLogTables()
{
//Arrange
//Act
CreateLogTablesTask.CreateLog(Connection);
//Assert
Assert.Equal(1, RowCountTask.Count(Connection, "sys.procedures",
"type = 'P' and name = 'StartLoadProcess' and schema_id = schema_id('etl')"));
Assert.Equal(1, RowCountTask.Count(Connection, "sys.procedures",
"type = 'P' and name = 'EndLoadProcess' and schema_id = schema_id('etl')"));
Assert.Equal(1, RowCountTask.Count(Connection, "sys.procedures",
"type = 'P' and name = 'AbortLoadProcess' and schema_id = schema_id('etl')"));
}
public void TestLogging_CSV_RowTrans_DB()
{
CreateLogTablesTask.CreateLog();
CSV_RowTrans_DB();
Assert.AreEqual(2, new SqlTask("Find log entry", "select count(*) from etl.Log where TaskType='DF_CSVSOURCE' group by TaskHash")
{
DisableLogging = true
}.ExecuteScalar <int>());
Assert.AreEqual(2, new SqlTask("Find log entry", "select count(*) from etl.Log where TaskType='DF_DBDEST' group by TaskHash")
{
DisableLogging = true
}.ExecuteScalar <int>());
}
public void RemoveLogTables()
{
//Arrange
RemoveLogTablesTask.Remove(Connection);
CreateLogTablesTask.CreateLog(Connection);
//Act
RemoveLogTablesTask.Remove(Connection);
//Assert
Assert.True(SqlTask.ExecuteScalarAsBool(Connection, "Check if tables are deleted",
"SELECT CASE WHEN object_id('etl.LoadProcess') IS NULL THEN 1 ELSE 0 END"));
Assert.True(SqlTask.ExecuteScalarAsBool(Connection, "Check if tables are deleted",
"SELECT CASE WHEN object_id('etl.Log') IS NULL THEN 1 ELSE 0 END"));
}
public void TestLogging()
{
CreateLogTablesTask.CreateLog();
RowCountTask.Count("test.RC");
Assert.AreEqual(2, new SqlTask("Find log entry", "select count(*) from etl.Log where TaskType='ROWCOUNT' group by TaskHash")
{
DisableLogging = true
}.ExecuteScalar <int>());
RowCountTask.Count("test.RC", "value = 2");
Assert.AreEqual(2, new SqlTask("Find log entry", "select count(*) from etl.Log where TaskType='ROWCOUNT' and Message like '%with condition%' group by TaskHash")
{
DisableLogging = true
}.ExecuteScalar <int>());
}
public void TestDuplicateCheckInRowTrans()
{
CreateLogTablesTask.CreateLog();
DataFlow.LoggingThresholdRows = 2;
CSVSource <Poco> source = new CSVSource <Poco>("src/DataFlowExamples/Duplicate.csv");
source.Configuration.Delimiter = ";";
source.Configuration.TrimOptions = CsvHelper.Configuration.TrimOptions.Trim;
source.Configuration.MissingFieldFound = null;
List <int> IDs = new List <int>(); //at the end of the flow, this list will contain all IDs of your source
RowTransformation <Poco, Poco> rowTrans = new RowTransformation <Poco, Poco>(input =>
{
if (IDs.Contains(input.ID))
{
input.IsDuplicate = true;
}
else
{
IDs.Add(input.ID);
}
return(input);
});
var multicast = new Multicast <Poco>();
var dest = new DBDestination <Poco>("dbo.Staging");
TableDefinition stagingTable = new TableDefinition("dbo.Staging", new List <TableColumn>()
{
new TableColumn("Key", "INT", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("ID", "INT", allowNulls: false),
new TableColumn("Value", "NVARCHAR(100)", allowNulls: false),
new TableColumn("Name", "NVARCHAR(100)", allowNulls: false)
});
stagingTable.CreateTable();
var trash = new VoidDestination <Poco>();
source.LinkTo(rowTrans);
rowTrans.LinkTo(multicast);
multicast.LinkTo(dest, input => input.IsDuplicate == false);
multicast.LinkTo(trash, input => input.IsDuplicate == true);
source.Execute();
dest.Wait();
trash.Wait();
}
public void BeforeTests()
{
TestHelper.RecreateDatabase(TestContext);
CreateLogTablesTask.CreateLog();
Assert.IsTrue(new SqlTask("Check proc StartLoadProcess", $"select count(*) from sys.procedures where type = 'P' and name = 'StartLoadProcess' and schema_id = schema_id('etl')")
{
DisableLogging = true
}.ExecuteScalarAsBool());
Assert.IsTrue(new SqlTask("Check proc EndLoadProcess", $"select count(*) from sys.procedures where type = 'P' and name = 'EndLoadProcess' and schema_id = schema_id('etl')")
{
DisableLogging = true
}.ExecuteScalarAsBool());
Assert.IsTrue(new SqlTask("Check proc AbortLoadProcess", $"select count(*) from sys.procedures where type = 'P' and name = 'AbortLoadProcess' and schema_id = schema_id('etl')")
{
DisableLogging = true
}.ExecuteScalarAsBool());
}
public void BeforeTests()
{
TestHelper.RecreateDatabase(TestContext);
CreateLogTablesTask.CreateLog();
Assert.IsFalse(new SqlTask("Check if logging was disabled for table creation", "select count(*) from etl.Log")
{
DisableLogging = true
}.ExecuteScalarAsBool());
Assert.IsTrue(new SqlTask("Check etl.Log table", $"select count(*) from sys.tables where type = 'U' and name = 'Log' and schema_id('etl') = schema_id")
{
DisableLogging = true
}.ExecuteScalarAsBool());
Assert.IsTrue(new SqlTask("Check etl.LoadProcess table", $"select count(*) from sys.tables where type = 'U' and name = 'LoadProcess' and schema_id('etl') = schema_id")
{
DisableLogging = true
}.ExecuteScalarAsBool());
}
public void LoggingDisabled()
{
CreateLogTablesTask.CreateLog();
DataFlow.LoggingThresholdRows = 0;
DBSource source;
DBDestination dest;
CreateSourceAndDestination(out source, out dest);
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(0, RowCountTask.Count("etl.Log", "TaskType = 'DF_DBSOURCE' AND TaskAction = 'LOG' AND LoadProcessKey IS NOT NULL"));
Assert.AreEqual(0, RowCountTask.Count("etl.Log", "TaskType = 'DF_DBDEST' AND TaskAction = 'LOG' AND LoadProcessKey IS NOT NULL"));
}
public void CleanupETLLogTablesWhileLogging()
{
//Arrange
CreateLogTablesTask.CreateLog(Connection);
Assert.Equal(1, RowCountTask.Count(Connection, "sys.tables",
$"type = 'U' AND name = 'Log' AND schema_id('etl') = schema_id"));
Assert.Equal(1, RowCountTask.Count(Connection, "sys.tables",
$"type = 'U' AND name = 'LoadProcess' AND schema_id('etl') = schema_id"));
//Act
CleanUpSchemaTask.CleanUp(Connection, "etl");
//Assert
Assert.Equal(0, RowCountTask.Count(Connection, "sys.tables",
$"type = 'U' AND name = 'Log' AND schema_id('etl') = schema_id"));
Assert.Equal(0, RowCountTask.Count(Connection, "sys.tables",
$"type = 'U' AND name = 'LoadProcess' AND schema_id('etl') = schema_id"));
}
public void LoggingInRowTransformation()
{
CreateLogTablesTask.CreateLog();
DataFlow.LoggingThresholdRows = 3;
DBSource source;
DBDestination dest;
CreateSourceAndDestination(out source, out dest);
RowTransformation rowTrans = new RowTransformation(row => row);
source.LinkTo(rowTrans);
rowTrans.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.AreEqual(3, RowCountTask.Count("etl.Log", "TaskType = 'DF_ROWTRANSFORMATION' AND TaskAction = 'LOG'"));
}
public void TestCleanupETLLogTables()
{
CreateLogTablesTask.CreateLog();
Assert.IsTrue(new SqlTask("Check etl.Log table", $"select count(*) from sys.tables where type = 'U' and name = 'Log' and schema_id('etl') = schema_id")
{
DisableLogging = true
}.ExecuteScalarAsBool());
Assert.IsTrue(new SqlTask("Check etl.LoadProcess table", $"select count(*) from sys.tables where type = 'U' and name = 'LoadProcess' and schema_id('etl') = schema_id")
{
DisableLogging = true
}.ExecuteScalarAsBool());
CleanUpSchemaTask.CleanUp("etl");
Assert.IsFalse(new SqlTask("Check etl.Log table", $"select count(*) from sys.tables where type = 'U' and name = 'Log' and schema_id('etl') = schema_id")
{
DisableLogging = true
}.ExecuteScalarAsBool());
Assert.IsFalse(new SqlTask("Check etl.LoadProcess table", $"select count(*) from sys.tables where type = 'U' and name = 'LoadProcess' and schema_id('etl') = schema_id")
{
DisableLogging = true
}.ExecuteScalarAsBool());
}
public void Table2JsonFile()
{
CreateLogTablesTask.CreateLog();
DataFlow.LoggingThresholdRows = 2;
TableDefinition sourceTableDefinition = CreateSourceTable("test.Source");
DBSource <MySimpleRow> source = new DBSource <MySimpleRow>(sourceTableDefinition);
List <MySimpleRow> rows = new List <MySimpleRow>();
CustomDestination <MySimpleRow> dest = new CustomDestination <MySimpleRow>(
row => {
rows.Add(row);
}
);
source.LinkTo(dest);
source.Execute();
dest.Wait();
string json = JsonConvert.SerializeObject(rows, Formatting.Indented);
Assert.AreEqual(json, File.ReadAllText("src/DataFlow/json_tobe.json"));
}
public DataFlowLoggingTests(LoggingDatabaseFixture dbFixture)
{
CreateLogTablesTask.CreateLog(Connection);
}
public GetLoadProcessAsJSONTaskTests(LoggingDatabaseFixture dbFixture)
{
CreateLogTablesTask.CreateLog(Connection);
}
public LoadProcessTasksTests(LoggingDatabaseFixture dbFixture)
{
CreateLogTablesTask.CreateLog(Connection);
ControlFlow.CurrentDbConnection = Connection;
}
