public void DataIsInList()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("MemoryDestinationNonGenericSource");
source2Columns.InsertTestData();
DbSource <string[]> source = new DbSource <string[]>(SqlConnection, "MemoryDestinationNonGenericSource");
MemoryDestination <string[]> dest = new MemoryDestination <string[]>();
//Act
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Collection(dest.Data,
d => Assert.True(d[0] == "1" && d[1] == "Test1"),
d => Assert.True(d[0] == "2" && d[1] == "Test2"),
d => Assert.True(d[0] == "3" && d[1] == "Test3")
);
}
public void SqlWithSelectStarAndDynamicObject()
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(SqlConnection, "SourceSelectStarDynamic");
s2c.InsertTestData();
//Act
DbSource source = new DbSource()
{
Sql = $@"SELECT * FROM {s2c.QB}SourceSelectStarDynamic{s2c.QE}",
ConnectionManager = SqlConnection
};
MemoryDestination dest = new MemoryDestination();
source.LinkTo(dest);
Assert.Throws <ETLBoxException>(() =>
{
source.Execute();
dest.Wait();
});
}
public void IgnoreWithObject()
{
//Arrange
MemorySource <MySimpleRow> source = new MemorySource <MySimpleRow>();
source.DataAsList = new List <MySimpleRow>()
{
null,
new MySimpleRow()
{
Col1 = 1, Col2 = "Test1"
},
null,
new MySimpleRow()
{
Col1 = 2, Col2 = "Test2"
},
new MySimpleRow()
{
Col1 = 3, Col2 = "Test3"
},
null
};
//Act
MemoryDestination <MySimpleRow> dest = new MemoryDestination <MySimpleRow>();
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Collection(dest.Data,
d => Assert.True(d.Col1 == 1 && d.Col2 == "Test1"),
d => Assert.True(d.Col1 == 2 && d.Col2 == "Test2"),
d => Assert.True(d.Col1 == 3 && d.Col2 == "Test3")
);
}
public void ValidateSchemaForEachRow()
{
//Arrange
var source = new MemorySource <MyXmlRow>();
source.DataAsList.Add(new MyXmlRow()
{
Xml = _validXml
});
source.DataAsList.Add(new MyXmlRow()
{
Xml = _invalidXml
});
source.DataAsList.Add(new MyXmlRow()
{
Xml = _validXml
});
MemoryDestination <MyXmlRow> dest = new MemoryDestination <MyXmlRow>();
MemoryDestination <ETLBoxError> error = new MemoryDestination <ETLBoxError>();
//Act
XmlSchemaValidation <MyXmlRow> schemaValidation = new XmlSchemaValidation <MyXmlRow>();
schemaValidation.XmlSelector = r => r.Xml;
schemaValidation.XmlSchema = xsdMarkup;
source.LinkTo(schemaValidation);
schemaValidation.LinkTo(dest);
schemaValidation.LinkErrorTo(error);
source.Execute();
dest.Wait();
error.Wait();
//Assert
Assert.True(dest.Data.Count == 2);
Assert.True(error.Data.Count == 1);
}
public void WithObjectErrorLinking()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("XmlSourceErrorLinking");
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>(SqlConnection, "XmlSourceErrorLinking");
MemoryDestination <ETLBoxError> errorDest = new MemoryDestination <ETLBoxError>();
//Act
XmlSource <MySimpleRow> source = new XmlSource <MySimpleRow>("res/XmlSource/TwoColumnsErrorLinking.xml",
ResourceType.File);
source.LinkTo(dest);
source.LinkErrorTo(errorDest);
source.Execute();
dest.Wait();
errorDest.Wait();
//Assert
dest2Columns.AssertTestData();
Assert.Collection <ETLBoxError>(errorDest.Data,
d => Assert.True(!string.IsNullOrEmpty(d.RecordAsJson) && !string.IsNullOrEmpty(d.ErrorText))
);
}
public void Exceding20Columns()
{
//Arrange
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture("ExcelDestination");
//Act
ExcelSource <Excel21Cols> source = new ExcelSource <Excel21Cols>("res/Excel/MoreThan20Cols.xlsx")
{
Range = new ExcelRange(1, 2),
};
MemoryDestination <Excel21Cols> dest = new MemoryDestination <Excel21Cols>();
source.LinkTo(dest);
source.Execute();
dest.Wait();
Assert.Collection <Excel21Cols>(dest.Data,
r => Assert.True(r.Col1 == 1 && r.Col2 == "Test1" && r.N == "N" && r.V == "V"),
r => Assert.True(r.Col1 == 2 && r.Col2 == "Test2" && r.N == "N" && r.V == "V"),
r => Assert.True(r.Col1 == 3 && r.Col2 == "Test3" && r.N == "N" && r.V == "V")
);
}
public void UnknownTableViaTableDefinition()
{
//Arrange
TableDefinition def = new TableDefinition("UnknownTable",
new List <TableColumn>()
{
new TableColumn("id", "INT")
});
DbSource <string[]> source = new DbSource <string[]>()
{
ConnectionManager = SqlConnection,
SourceTableDefinition = def
};
MemoryDestination <string[]> dest = new MemoryDestination <string[]>();
//Act & Assert
Assert.Throws <Microsoft.Data.SqlClient.SqlException>(() =>
{
source.LinkTo(dest);
source.Execute();
dest.Wait();
});
}
public void WriteIntoMultipleDestinations()
{
//Arrange
var source = new MemorySource <string[]>();
source.DataAsList.Add(new string[] { "Test" });
var trans = new RowTransformation <string[]>();
trans.TransformationFunc = r => throw new Exception();
var dest = new MemoryDestination <string[]>();
CreateErrorTableTask.Create(SqlConnection, "error_log");
var mc = new Multicast <ETLBoxError>();
var errorMem = new MemoryDestination <ETLBoxError>();
var errorDb = new DbDestination <ETLBoxError>(SqlConnection, "error_log");
var errorCsv = new CsvDestination <ETLBoxError>("error_csv.csv");
source.LinkTo(trans);
trans.LinkTo(dest);
//Act
trans.LinkErrorTo(mc);
mc.LinkTo(errorMem);
mc.LinkTo(errorDb);
mc.LinkTo(errorCsv);
source.Execute();
dest.Wait();
errorMem.Wait();
errorDb.Wait();
errorCsv.Wait();
//Assert
Assert.True(errorMem.Data.Count > 0);
Assert.True(RowCountTask.Count(SqlConnection, "error_log") > 0);
Assert.True(File.ReadAllText("error_csv.csv").Length > 0);
}
public void JsonAPIRequestWithMetaData()
{
//Arrange
MemoryDestination dest = new MemoryDestination();
bool firstRequest = true;
//Act
JsonSource source = new JsonSource();
source.GetNextUri = meta =>
{
return(firstRequest ? $"res/JsonSource/JsonAPI.json" : $"res/JsonSource/JsonAPINext.json");
};
source.HasNextUri = meta =>
{
Assert.Equal(3, meta.ProgressCount);
if (firstRequest)
{
Assert.Equal(firstRequestUnparsedToBe, meta.UnparsedData, ignoreCase: true, ignoreLineEndingDifferences: true, ignoreWhiteSpaceDifferences: true);
}
else
{
Assert.Equal(secondRequestUnparsedToBe, meta.UnparsedData, ignoreCase: true, ignoreLineEndingDifferences: true, ignoreWhiteSpaceDifferences: true);
}
bool result = firstRequest;
firstRequest = false;
return(result);
};
source.ResourceType = ResourceType.File;
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, dest.Data.Count);
}
public void WithMultipleTokens()
{
//Arrange
MemoryDestination dest = new MemoryDestination();
//Act
JsonSource <ExpandoObject> source = new JsonSource <ExpandoObject>("res/JsonSource/NestedData.json", ResourceType.File);
List <JsonProperty2JsonPath> pathLookups = new List <JsonProperty2JsonPath>()
{
new JsonProperty2JsonPath()
{
JsonPropertyName = "Column2",
JsonPath = "$.Value",
NewPropertyName = "Value"
},
new JsonProperty2JsonPath()
{
JsonPropertyName = "Column2",
JsonPath = "$['Id']",
NewPropertyName = "Id"
}
};
source.JsonSerializer.Converters.Add(new ExpandoJsonPathConverter(pathLookups));
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Collection <ExpandoObject>(dest.Data,
row => { dynamic r = row as ExpandoObject; Assert.True(r.Column1 == 1 && r.Id == "A" && r.Value == "Test1"); },
row => { dynamic r = row as ExpandoObject; Assert.True(r.Column1 == 2 && r.Id == "B" && r.Value == "Test2"); },
row => { dynamic r = row as ExpandoObject; Assert.True(r.Column1 == 3 && r.Id == "C" && r.Value == "Test3"); }
);
}
public void DataIsInList()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("MemoryDestinationSource");
source2Columns.InsertTestData();
DbSource <ExpandoObject> source = new DbSource <ExpandoObject>(SqlConnection, "MemoryDestinationSource");
MemoryDestination <ExpandoObject> dest = new MemoryDestination <ExpandoObject>();
//Act
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
int index = 1;
foreach (dynamic d in dest.Data)
{
Assert.True(d.Col1 == index && d.Col2 == "Test" + index);
index++;
}
}
public void AggregateSimple()
{
//Arrange
MemorySource <MyRow> source = new MemorySource <MyRow>();
source.DataAsList = new List <MyRow>()
{
new MyRow {
Id = 1, DetailValue = 3.5
},
new MyRow {
Id = 2, DetailValue = 4.5
},
new MyRow {
Id = 3, DetailValue = 2.0
},
};
Aggregation <MyRow, MyAggRow> agg = new Aggregation <MyRow, MyAggRow>()
{
AggregationAction = (row, aggRow) => aggRow.AggValue += row.DetailValue
};
MemoryDestination <MyAggRow> dest = new MemoryDestination <MyAggRow>();
//Act
source.LinkTo(agg);
agg.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Collection <MyAggRow>(dest.Data,
ar => Assert.True(ar.AggValue == 10)
);
}
public void NoParameter()
{
//Arrange
MemorySource <MyEnumRow> source = new MemorySource <MyEnumRow>();
source.DataAsList.Add(new MyEnumRow()
{
EnumCol = EnumType.Value2
});
RowDuplication <MyEnumRow> duplication = new RowDuplication <MyEnumRow>();
MemoryDestination <MyEnumRow> dest = new MemoryDestination <MyEnumRow>();
//Act
source.LinkTo(duplication);
duplication.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Collection <MyEnumRow>(dest.Data,
d => Assert.True(d.EnumCol == EnumType.Value2),
d => Assert.True(d.EnumCol == EnumType.Value2)
);
}
public void ThrowExceptionInFlow()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("RowMultiplicationSource");
source2Columns.InsertTestData();
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>(SqlConnection, "RowMultiplicationSource");
RowMultiplication <MySimpleRow> multiplication = new RowMultiplication <MySimpleRow>(
row =>
{
List <MySimpleRow> result = new List <MySimpleRow>();
result.Add(row);
if (row.Col1 == 2)
{
throw new Exception("Error in Flow!");
}
return(result);
});
MemoryDestination <MySimpleRow> dest = new MemoryDestination <MySimpleRow>();
MemoryDestination <ETLBoxError> errorDest = new MemoryDestination <ETLBoxError>();
//Act
source.LinkTo(multiplication);
multiplication.LinkTo(dest);
multiplication.LinkErrorTo(errorDest);
source.Execute();
dest.Wait();
errorDest.Wait();
//Assert
Assert.Collection <ETLBoxError>(errorDest.Data,
d => Assert.True(!string.IsNullOrEmpty(d.RecordAsJson) && !string.IsNullOrEmpty(d.ErrorText))
);
}
public void WithObject()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(SqlConnection, "LookupErrorLinkingDest");
CreateSourceTable(SqlConnection, "LookupErrorLinkingSource");
DbSource <MyLookupRow> lookupSource = new DbSource <MyLookupRow>(SqlConnection, "LookupErrorLinkingSource");
MemorySource <MyInputDataRow> source = new MemorySource <MyInputDataRow>();
source.DataAsList = new List <MyInputDataRow>()
{
new MyInputDataRow()
{
Col1 = 1
},
new MyInputDataRow()
{
Col1 = 2
},
new MyInputDataRow()
{
Col1 = 3
},
new MyInputDataRow()
{
Col1 = 4
}
};
MemoryDestination <ETLBoxError> errorDest = new MemoryDestination <ETLBoxError>();
//Act
List <MyLookupRow> LookupTableData = new List <MyLookupRow>();
LookupTransformation <MyInputDataRow, MyLookupRow> lookup = new LookupTransformation <MyInputDataRow, MyLookupRow>(
lookupSource,
row =>
{
row.Col2 = LookupTableData.Where(ld => ld.Key == row.Col1).Select(ld => ld.LookupValue).FirstOrDefault();
if (row.Col1 == 4)
{
throw new Exception("Error record");
}
return(row);
}
, LookupTableData
);
DbDestination <MyInputDataRow> dest = new DbDestination <MyInputDataRow>(SqlConnection, "LookupErrorLinkingDest");
source.LinkTo(lookup);
lookup.LinkTo(dest);
lookup.LinkLookupSourceErrorTo(errorDest);
lookup.LinkLookupTransformationErrorTo(errorDest);
source.Execute();
dest.Wait();
errorDest.Wait();
//Assert
dest2Columns.AssertTestData();
Assert.Collection <ETLBoxError>(errorDest.Data,
d => Assert.True(!string.IsNullOrEmpty(d.RecordAsJson) && !string.IsNullOrEmpty(d.ErrorText)),
d => Assert.True(!string.IsNullOrEmpty(d.RecordAsJson) && !string.IsNullOrEmpty(d.ErrorText))
);
}
private void LoadLookupData()
{
NLogStartOnce();
Source.Execute();
LookupBuffer.Wait();
}
public void GroupingUsingDynamicObject()
{
//Arrange
MemorySource <ExpandoObject> source = new MemorySource <ExpandoObject>();
dynamic row1 = new ExpandoObject();
row1.ClassName = "Class1";
row1.DetailValue = 3.5;
dynamic row2 = new ExpandoObject();
row2.ClassName = "Class1";
row2.DetailValue = 6.5;
dynamic row3 = new ExpandoObject();
row3.ClassName = "Class2";
row3.DetailValue = 10;
source.Data.Add(row1);
source.Data.Add(row2);
source.Data.Add(row3);
Aggregation <ExpandoObject, ExpandoObject> agg = new Aggregation <ExpandoObject, ExpandoObject>(
(row, aggValue) =>
{
dynamic r = row as ExpandoObject;
dynamic a = aggValue as ExpandoObject;
if (!((IDictionary <String, object>)a).ContainsKey("AggValue"))
{
a.AggValue = r.DetailValue;
}
else
{
a.AggValue += r.DetailValue;
}
},
row =>
{
dynamic r = row as ExpandoObject;
return(r.ClassName);
},
(key, agg) =>
{
dynamic a = agg as ExpandoObject;
a.GroupName = (string)key;
});
MemoryDestination <ExpandoObject> dest = new MemoryDestination <ExpandoObject>();
//Act
source.LinkTo(agg);
agg.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Collection <ExpandoObject>(dest.Data,
ar => {
dynamic a = ar as ExpandoObject;
Assert.True(a.AggValue == 10 && a.GroupName == "Class1");
},
ar => {
dynamic a = ar as ExpandoObject;
Assert.True(a.AggValue == 10 && a.GroupName == "Class2");
}
);
}
public void ReadDifferentTypes(IConnectionManager conn)
{
//Arrange
CreateTableTask.Create(conn, "different_type_table",
new List <TableColumn>()
{
new TableColumn("int_col", "INT", allowNulls: true),
new TableColumn("long_col", "BIGINT", allowNulls: true),
new TableColumn("decimal_col", "FLOAT", allowNulls: true),
new TableColumn("double_col", "FLOAT", allowNulls: true),
new TableColumn("datetime_col", "DATETIME", allowNulls: true),
new TableColumn("date_col", "DATE", allowNulls: true),
new TableColumn("string_col", "VARCHAR(200)", allowNulls: true),
new TableColumn("char_col", "CHAR(1)", allowNulls: true),
new TableColumn("decimal_string_col", "DECIMAL(12,10)", allowNulls: true),
new TableColumn("null_col", "CHAR(1)", allowNulls: true),
new TableColumn("enum_col", "INT", allowNulls: true),
});
string datetime = "'2020-01-01 10:00:00.000'";
string date = "'2020-01-01'";
if (conn.GetType() == typeof(OracleConnectionManager))
{
datetime = "TO_TIMESTAMP('01-JAN-2020 10:00:00')";
date = "'01 JAN 2020'";
}
SqlTask.ExecuteNonQuery(conn, "Insert test data",
$@"
INSERT
INTO {conn.QB}different_type_table{conn.QE}
(
{conn.QB}int_col{conn.QE}
, {conn.QB}long_col{conn.QE}
, {conn.QB}decimal_col{conn.QE}
, {conn.QB}double_col{conn.QE}
, {conn.QB}datetime_col{conn.QE}
, {conn.QB}date_col{conn.QE}
, {conn.QB}string_col{conn.QE}
, {conn.QB}char_col{conn.QE}
, {conn.QB}decimal_string_col{conn.QE}
, {conn.QB}null_col{conn.QE}
, {conn.QB}enum_col{conn.QE}
)
VALUES
( 1, -1, 2.3, 5.4, {datetime}, {date}, 'Test', 'T', '13.4566', NULL, 2 )
");
//Act
DbSource <MyDataTypeRow> source = new DbSource <MyDataTypeRow>(conn, "different_type_table");
MemoryDestination <MyDataTypeRow> dest = new MemoryDestination <MyDataTypeRow>();
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(1, dest.Data.First().IntCol);
Assert.Equal(-1, dest.Data.First().LongCol);
Assert.Equal(2.3M, dest.Data.First().DecimalCol);
Assert.True(dest.Data.First().DoubleCol >= 5.4 && dest.Data.First().DoubleCol < 5.5);
Assert.Equal("2020-01-01 10:00:00.000", dest.Data.First().DateTimeCol.ToString("yyyy-MM-dd hh:mm:ss.fff"));
Assert.Equal("2020-01-01", dest.Data.First().DateCol.ToString("yyyy-MM-dd"));
Assert.Equal("Test", dest.Data.First().StringCol);
Assert.Equal('T', dest.Data.First().CharCol);
Assert.StartsWith("13.4566", dest.Data.First().DecimalStringCol);
Assert.Null(dest.Data.First().NullCol);
Assert.Equal(EnumType.Value2, dest.Data.First().EnumCol);
}