public void CreateTablesStep()
{
var dbDestination = new DbDestination(Settings.Current.Building.DestinationConnectionString,
Settings.Current.Building.CdmSchema);
dbDestination.ExecuteQuery(Settings.Current.CreateCdmTablesScript);
}
public void SimpleDataNoHeader()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("ExcelDestinationDynamic");
//Act
ExcelSource source = new ExcelSource("res/Excel/TwoColumnShiftedData.xlsx")
{
Range = new ExcelRange(3, 4),
HasNoHeader = true
};
RowTransformation trans = new RowTransformation(row =>
{
dynamic r = row as dynamic;
r.Col1 = r.Column1;
r.Col2 = r.Column2;
return(r);
});
DbDestination dest = new DbDestination(Connection, "ExcelDestinationDynamic");
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void ConvertIntoObject()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("DestinationRowTransformationDynamic");
CsvSource <ExpandoObject> source = new CsvSource <ExpandoObject>("res/RowTransformation/TwoColumns.csv");
//Act
RowTransformation <ExpandoObject> trans = new RowTransformation <ExpandoObject>(
csvdata =>
{
dynamic c = csvdata as ExpandoObject;
c.Col1 = c.Header1;
c.Col2 = c.Header2;
return(c);
});
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>(Connection, "DestinationRowTransformationDynamic");
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void UsingDifferentObjectTypes()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("SourceMultipleLinks");
source2Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("DestinationMultipleLinks");
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>(SqlConnection, "SourceMultipleLinks");
DbDestination <MyOtherRow> dest = new DbDestination <MyOtherRow>(SqlConnection, "DestinationMultipleLinks");
RowTransformation <MySimpleRow, MyOtherRow> trans1 = new RowTransformation <MySimpleRow, MyOtherRow>
(row =>
{
return(new MyOtherRow()
{
ColA = row.Col1,
ColB = row.Col2
});
}
);
//Act
source.LinkTo <MyOtherRow>(trans1).LinkTo(dest);
//Assert
source.Execute();
dest.Wait();
dest2Columns.AssertTestData();
}
public void SplitInto2Tables()
{
//Arrange
TwoColumnsTableFixture sourceTable = new TwoColumnsTableFixture("Source");
sourceTable.InsertTestData();
TwoColumnsTableFixture dest1Table = new TwoColumnsTableFixture("Destination1");
TwoColumnsTableFixture dest2Table = new TwoColumnsTableFixture("Destination2");
DbSource <string[]> source = new DbSource <string[]>("Source", Connection);
DbDestination <string[]> dest1 = new DbDestination <string[]>("Destination1", Connection);
DbDestination <string[]> dest2 = new DbDestination <string[]>("Destination2", Connection);
//Act
Multicast <string[]> multicast = new Multicast <string[]>();
source.LinkTo(multicast);
multicast.LinkTo(dest1);
multicast.LinkTo(dest2);
source.Execute();
dest1.Wait();
dest2.Wait();
//Assert
dest1Table.AssertTestData();
dest2Table.AssertTestData();
}
public void UsingTableDefinitions(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture(connection, "SourceTableDef");
source2Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(connection, "DestinationTableDef");
//Act
DbSource <string[]> source = new DbSource <string[]>()
{
SourceTableDefinition = source2Columns.TableDefinition,
ConnectionManager = connection
};
DbDestination <string[]> dest = new DbDestination <string[]>()
{
DestinationTableDefinition = dest2Columns.TableDefinition,
ConnectionManager = connection
};
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void RedirectErrorWithObject(IConnectionManager connection)
{
if (connection.GetType() == typeof(SQLiteConnectionManager))
{
Task.Delay(100).Wait(); //Database was locked and needs to recover after exception
}
//Arrange
CreateSourceTable(connection, "DbSourceErrorLinking");
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture(connection, "DbDestinationErrorLinking");
//Act
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>(connection, "DbSourceErrorLinking");
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>(connection, "DbDestinationErrorLinking");
MemoryDestination <ETLBoxError> errorDest = new MemoryDestination <ETLBoxError>();
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)),
d => Assert.True(!string.IsNullOrEmpty(d.RecordAsJson) && !string.IsNullOrEmpty(d.ErrorText)),
d => Assert.True(!string.IsNullOrEmpty(d.RecordAsJson) && !string.IsNullOrEmpty(d.ErrorText))
);
}
public void IgnoreWithStringArray()
{
//Arrange
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(SqlConnection, "DestIgnoreNullValuesStringArray");
MemorySource <string[]> source = new MemorySource <string[]>();
source.DataAsList = new List <string[]>()
{
null,
new string[] { "1", "Test1" },
null,
new string[] { "2", "Test2" },
new string[] { "3", "Test3" },
null
};
DbDestination <string[]> dest = new DbDestination <string[]>(SqlConnection, "DestIgnoreNullValuesStringArray");
//Act
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
d2c.AssertTestData();
}
public void DuplicateDataInto3Destinations()
{
//Arrange
TwoColumnsTableFixture sourceTable = new TwoColumnsTableFixture("Source");
sourceTable.InsertTestData();
TwoColumnsTableFixture dest1Table = new TwoColumnsTableFixture("Destination1");
TwoColumnsTableFixture dest2Table = new TwoColumnsTableFixture("Destination2");
TwoColumnsTableFixture dest3Table = new TwoColumnsTableFixture("Destination3");
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>(Connection, "Source");
DbDestination <MySimpleRow> dest1 = new DbDestination <MySimpleRow>(Connection, "Destination1");
DbDestination <MySimpleRow> dest2 = new DbDestination <MySimpleRow>(Connection, "Destination2");
DbDestination <MySimpleRow> dest3 = new DbDestination <MySimpleRow>(Connection, "Destination3");
//Act
Multicast <MySimpleRow> multicast = new Multicast <MySimpleRow>();
source.LinkTo(multicast);
multicast.LinkTo(dest1);
multicast.LinkTo(dest2);
multicast.LinkTo(dest3);
source.Execute();
dest1.Wait();
dest2.Wait();
dest3.Wait();
//Assert
dest1Table.AssertTestData();
dest2Table.AssertTestData();
dest3Table.AssertTestData();
}
public void ThrowExceptionWithoutHandling()
{
//Arrange
TwoColumnsTableFixture source2Columns = new TwoColumnsTableFixture("BlockTransSource");
source2Columns.InsertTestData();
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("BlockTransDest");
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>(Connection, "BlockTransSource");
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>(Connection, "BlockTransDest");
//Act
BlockTransformation <MySimpleRow> block = new BlockTransformation <MySimpleRow>(
inputData =>
{
throw new Exception("Test");
});
source.LinkTo(block);
block.LinkTo(dest);
//Assert
Assert.Throws <AggregateException>(() =>
{
source.Execute();
dest.Wait();
});
}
public void IgnoreWithObject()
{
//Arrange
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(SqlConnection, "DestIgnoreNullValues");
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
};
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>(SqlConnection, "DestIgnoreNullValues");
//Act
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
d2c.AssertTestData();
}
public void SimpleFlowWithDefaultConnection()
{
//Arrange
CreateTableTask.Create("TestSourceTable",
new List <TableColumn>()
{
new TableColumn("Col1", "VARCHAR(100)")
});
SqlTask.ExecuteNonQuery("Insert test data", "INSERT INTO TestSourceTable VALUES ('T');");
CreateTableTask.Create("TestDestinationTable",
new List <TableColumn>()
{
new TableColumn("Col1", "VARCHAR(100)")
});
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>("TestSourceTable");
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>("TestDestinationTable");
//Act
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.True(RowCountTask.Count("TestDestinationTable").Value == 1);
}
public void TruncateLookup()
{
var dbDestination = new DbDestination(Settings.Current.Building.DestinationConnectionString,
Settings.Current.Building.CdmSchema);
dbDestination.ExecuteQuery(Settings.Current.TruncateLookupScript);
}
public void DropDestination()
{
var dbDestination = new DbDestination(Settings.Current.Building.DestinationConnectionString,
Settings.Current.Building.CdmSchema);
dbDestination.ExecuteQuery(Settings.Current.DropTablesScript);
}
public void ConvertIntoObject()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("DestinationRowTransformation");
CsvSource <string[]> source = new CsvSource <string[]>("res/RowTransformation/TwoColumns.csv");
//Act
RowTransformation <string[], MySimpleRow> trans = new RowTransformation <string[], MySimpleRow>(
csvdata =>
{
return(new MySimpleRow()
{
Col1 = int.Parse(csvdata[0]),
Col2 = csvdata[1]
});
});
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>(Connection, "DestinationRowTransformation");
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void WithBatchChanges(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(connection, "DbDestinationBatchChanges");
DbDestination <string[]> dest = new DbDestination <string[]>(connection, "DbDestinationBatchChanges", batchSize: 2)
{
BeforeBatchWrite = rowArray =>
{
rowArray[0][1] = "NewValue";
return(rowArray);
}
};
//Act
CsvSource <string[]> source = new CsvSource <string[]>("res/CsvSource/TwoColumns.csv");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(connection, "DbDestinationBatchChanges"));
Assert.Equal(2, RowCountTask.Count(connection, "DbDestinationBatchChanges", $"{d2c.QB}Col2{d2c.QE}='NewValue'"));
Assert.Equal(1, RowCountTask.Count(connection, "DbDestinationBatchChanges", $"{d2c.QB}Col1{d2c.QE} = 2 AND {d2c.QB}Col2{d2c.QE}='Test2'"));
}
public void InitAction()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("DestinationRowTransformation");
CsvSource <MySimpleRow> source = new CsvSource <MySimpleRow>("res/RowTransformation/TwoColumnsIdMinus1.csv");
//Act
int IdOffset = 0;
RowTransformation <MySimpleRow, MySimpleRow> trans = new RowTransformation <MySimpleRow, MySimpleRow>()
{
TransformationFunc = row =>
{
row.Col1 += IdOffset;
return(row);
},
InitAction = () => IdOffset += 1
};
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>(Connection, "DestinationRowTransformation");
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void AfterBatchWrite(IConnectionManager connection)
{
//Arrange
bool wasExecuted = false;
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(connection, "DbDestinationBatchChanges");
DbDestination <string[]> dest = new DbDestination <string[]>(connection, "DbDestinationBatchChanges", batchSize: 1)
{
AfterBatchWrite = rowArray =>
{
Assert.True(rowArray.Length == 1);
wasExecuted = true;
}
};
//Act
CsvSource <string[]> source = new CsvSource <string[]>("res/CsvSource/TwoColumns.csv");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(connection, "DbDestinationBatchChanges"));
Assert.True(wasExecuted);
}
public void ColumnMapping(IConnectionManager connection)
{
//Arrange
FourColumnsTableFixture source4Columns = new FourColumnsTableFixture(connection, "Source");
source4Columns.InsertTestData();
FourColumnsTableFixture dest4Columns = new FourColumnsTableFixture(connection, "Destination", identityColumnIndex: 2);
DbSource <string[]> source = new DbSource <string[]>(connection, "Source");
RowTransformation <string[], MyExtendedRow> trans = new RowTransformation <string[], MyExtendedRow>(
row => new MyExtendedRow()
{
Id = int.Parse(row[0]),
Text = row[1],
Value = row[2] != null ? (long?)long.Parse(row[2]) : null,
Percentage = decimal.Parse(row[3])
});
//Act
DbDestination <MyExtendedRow> dest = new DbDestination <MyExtendedRow>(connection, "Destination");
source.LinkTo(trans);
trans.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest4Columns.AssertTestData();
}
public void LeaveOpen(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(connection, "TransactionSource");
s2c.InsertTestData();
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(connection, "TransactionDest");
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>(connection, "TransactionSource");
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>(connection, "TransactionDest", batchSize: 2);
//Act
connection.LeaveOpen = true;
connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
source.LinkTo(dest);
source.Execute();
dest.Wait();
connection.CommitTransaction();
//Assert
Assert.Equal(connection, dest.BulkInsertConnectionManager);
//Assert Connections are closed
Assert.True(dest.BulkInsertConnectionManager.State == System.Data.ConnectionState.Open);
Assert.True(connection.State == System.Data.ConnectionState.Open);
Assert.Equal(3, RowCountTask.Count(connection, "TransactionDest"));
}
public void SimpleFlow()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("Destination4CustomSourceDynamic");
List <string> Data = new List <string>()
{
"Test1", "Test2", "Test3"
};
int _readIndex = 0;
Func <ExpandoObject> ReadData = () =>
{
dynamic result = new ExpandoObject();
result.Col1 = (_readIndex + 1).ToString();
result.Col2 = Data[_readIndex];
_readIndex++;
return(result);
};
Func <bool> EndOfData = () => _readIndex >= Data.Count;
//Act
CustomSource <ExpandoObject> source = new CustomSource <ExpandoObject>(ReadData, EndOfData);
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>("Destination4CustomSourceDynamic", Connection);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void TwoTransactionsAndParallelWriting(IConnectionManager connection)
{
if (connection.ConnectionManagerType == ConnectionManagerType.SQLite)
{
return;
}
//Arrange
var concopy = connection.Clone();
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(connection, "TransactionSourceParallelWrite");
s2c.InsertTestData();
TwoColumnsTableFixture d2c1 = new TwoColumnsTableFixture(connection, "TransactionDest1");
TwoColumnsTableFixture d2c2 = new TwoColumnsTableFixture(connection, "TransactionDest2");
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>(connection, "TransactionSourceParallelWrite");
DbDestination <MySimpleRow> dest1 = new DbDestination <MySimpleRow>(connection, "TransactionDest1", batchSize: 2);
DbDestination <MySimpleRow> dest2 = new DbDestination <MySimpleRow>(concopy, "TransactionDest2", batchSize: 2);
Multicast <MySimpleRow> multicast = new Multicast <MySimpleRow>();
//Act & Assert
connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
concopy.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
source.LinkTo(multicast);
multicast.LinkTo(dest1);
multicast.LinkTo(dest2);
source.Execute();
dest1.Wait();
dest2.Wait();
connection.CommitTransaction();
concopy.CommitTransaction();
Assert.Equal(3, RowCountTask.Count(connection, "TransactionDest1"));
Assert.Equal(3, RowCountTask.Count(connection, "TransactionDest2"));
}
public void JsonPathInEpandoObject()
{
//Arrange
TwoColumnsTableFixture dest2Columns = new TwoColumnsTableFixture("JsonSourceNestedDynamic");
RowTransformation <ExpandoObject> trans = new RowTransformation <ExpandoObject>(
row =>
{
dynamic r = row as ExpandoObject;
r.Col1 = r.Column1;
return(r);
});
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>(SqlConnection, "JsonSourceNestedDynamic");
//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 = "Col2"
}
};
source.JsonSerializer.Converters.Add(new ExpandoJsonPathConverter(pathLookups));
source.LinkTo(trans).LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
dest2Columns.AssertTestData();
}
public void OneTransactionAndParallelWritingWithMARS()
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(SqlConnection, "TransactionSourceParallelWrite");
s2c.InsertTestData();
TwoColumnsTableFixture d2c1 = new TwoColumnsTableFixture(SqlConnection, "TransactionDest1");
TwoColumnsTableFixture d2c2 = new TwoColumnsTableFixture(SqlConnection, "TransactionDest2");
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>(SqlConnection, "TransactionSourceParallelWrite");
string constring = $"{Config.SqlConnection.RawConnectionString("DataFlow")};MultipleActiveResultSets=True;";
var marscon = new SqlConnectionManager(constring);
DbDestination <MySimpleRow> dest1 = new DbDestination <MySimpleRow>(marscon, "TransactionDest1", batchSize: 2);
DbDestination <MySimpleRow> dest2 = new DbDestination <MySimpleRow>(marscon, "TransactionDest2", batchSize: 2);
Multicast <MySimpleRow> multicast = new Multicast <MySimpleRow>();
//Act & Assert
marscon.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
source.LinkTo(multicast);
multicast.LinkTo(dest1);
multicast.LinkTo(dest2);
source.Execute();
dest1.Wait();
dest2.Wait();
marscon.CommitTransaction();
d2c1.AssertTestData();
d2c1.AssertTestData();
}
public void CompareFlowWithBulkInsert(IConnectionManager connection, int numberOfRows, int batchSize, double deviationGeneric, double deviationBulk)
{
//Arrange
BigDataCsvSource.CreateCSVFileIfNeeded(numberOfRows);
ReCreateDestinationTable(connection, "CsvDestinationNonGenericETLBox");
ReCreateDestinationTable(connection, "CsvDestinationBulkInsert");
ReCreateDestinationTable(connection, "CsvDestinationGenericETLBox");
var sourceNonGeneric = new CsvSource(BigDataCsvSource.GetCompleteFilePath(numberOfRows));
var destNonGeneric = new DbDestination(connection, "CsvDestinationNonGenericETLBox", batchSize);
var sourceGeneric = new CsvSource <CSVData>(BigDataCsvSource.GetCompleteFilePath(numberOfRows));
var destGeneric = new DbDestination <CSVData>(connection, "CsvDestinationGenericETLBox", batchSize);
//Act
var timeElapsedBulkInsert = GetBulkInsertTime(connection, numberOfRows);
var timeElapsedETLBoxNonGeneric = GetETLBoxTime(numberOfRows, sourceNonGeneric, destNonGeneric);
var timeElapsedETLBoxGeneric = GetETLBoxTime(numberOfRows, sourceGeneric, destGeneric);
//Assert
Assert.Equal(numberOfRows, RowCountTask.Count(connection, "CsvDestinationNonGenericETLBox"));
Assert.Equal(numberOfRows, RowCountTask.Count(connection, "CsvDestinationGenericETLBox"));
Assert.True(Math.Abs(timeElapsedETLBoxGeneric.TotalMilliseconds - timeElapsedETLBoxNonGeneric.TotalMilliseconds) <
Math.Min(timeElapsedETLBoxGeneric.TotalMilliseconds, timeElapsedETLBoxNonGeneric.TotalMilliseconds) * deviationGeneric);
if (timeElapsedBulkInsert.TotalMilliseconds > 0)
{
Assert.True(timeElapsedBulkInsert < timeElapsedETLBoxNonGeneric);
Assert.True(timeElapsedBulkInsert.TotalMilliseconds * (deviationBulk + 1) > timeElapsedETLBoxNonGeneric.TotalMilliseconds);
}
}
public void CommitTransaction(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(connection, "TransactionSource");
s2c.InsertTestData();
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(connection, "TransactionDest");
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>(connection, "TransactionSource");
DbDestination <MySimpleRow> dest = new DbDestination <MySimpleRow>(connection, "TransactionDest", batchSize: 2);
//Act & Assert
connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
if (connection.GetType() == typeof(SqlConnectionManager))
{
Assert.Equal(3, RowCountTask.Count(connection.Clone(), "TransactionDest", RowCountOptions.NoLock));
}
connection.CommitTransaction();
Assert.Equal(3, RowCountTask.Count(connection, "TransactionDest"));
Assert.Equal(3, RowCountTask.Count(connection.Clone(), "TransactionDest"));
//Assert Connections are closed
Assert.True(dest.BulkInsertConnectionManager.State == null);
Assert.True(connection.State == null);
}
public void MoreColumnsInDestination()
{
//Arrange
CreateTableTask.Create(SqlConnection, "CsvSourceDynamicColsInDest",
new List <TableColumn>()
{
new TableColumn("Col2", "VARCHAR(100)", allowNulls: true),
new TableColumn("Id", "INT", allowNulls: false, isPrimaryKey: true, isIdentity: true),
new TableColumn("Col1", "INT", allowNulls: true),
new TableColumn("ColX", "INT", allowNulls: true),
});
DbDestination <ExpandoObject> dest = new DbDestination <ExpandoObject>(SqlConnection, "CsvSourceDynamicColsInDest");
//Act
CsvSource <ExpandoObject> source = new CsvSource <ExpandoObject>("res/CsvSource/TwoColumnsForDynamic.csv");
source.LinkTo(dest);
source.Execute();
dest.Wait();
//Assert
Assert.Equal(3, RowCountTask.Count(SqlConnection, "CsvSourceDynamicColsInDest"));
Assert.Equal(1, RowCountTask.Count(SqlConnection, "CsvSourceDynamicColsInDest", $"Col1 = 1 AND Col2='Test1' AND Id > 0 AND ColX IS NULL"));
Assert.Equal(1, RowCountTask.Count(SqlConnection, "CsvSourceDynamicColsInDest", $"Col1 = 2 AND Col2='Test2' AND Id > 0 AND ColX IS NULL"));
Assert.Equal(1, RowCountTask.Count(SqlConnection, "CsvSourceDynamicColsInDest", $"Col1 = 3 AND Col2='Test3' AND Id > 0 AND ColX IS NULL"));
}
public void CheckMemoryUsage(IConnectionManager connection, int numberOfRows, int batchSize, double deviation)
{
//Arrange
BigDataCsvSource.CreateCSVFileIfNeeded(numberOfRows);
ReCreateDestinationTable(connection, "CsvDestinationWithTransformation");
var sourceExpando = new CsvSource(BigDataCsvSource.GetCompleteFilePath(numberOfRows));
var trans = new RowTransformation <ExpandoObject, CSVData>(
row =>
{
dynamic r = row as ExpandoObject;
return(new CSVData()
{
Col1 = r.Col1,
Col2 = r.Col2,
Col3 = r.Col3,
Col4 = r.Col4
});
});
var destGeneric = new DbDestination <CSVData>(connection, "CsvDestinationWithTransformation", batchSize);
sourceExpando.LinkTo(trans);
trans.LinkTo(destGeneric);
//Act
long memAfter = 0;
long memBefore = 0;
bool startCheck = true;
int count = 1;
destGeneric.AfterBatchWrite = data =>
{
if (count++ % 50 == 0)
{
using Process proc = Process.GetCurrentProcess();
memAfter = proc.WorkingSet64;
if (startCheck)
{
memBefore = memAfter;
startCheck = false;
}
Assert.True(memAfter < (memBefore + (memBefore * deviation)));
}
};
var timeElapsedETLBox = BigDataHelper.LogExecutionTime($"Copying Csv into DB (non generic) with {numberOfRows} rows of data using ETLBox",
() =>
{
sourceExpando.Execute();
destGeneric.Wait();
}
);
output.WriteLine("Elapsed " + timeElapsedETLBox.TotalSeconds + " seconds for ETLBox (Expando to object transformation).");
//Assert
Assert.Equal(numberOfRows, RowCountTask.Count(connection, "CsvDestinationWithTransformation"));
//10.000.000 rows, batch size 10.000: ~8 min
//10.000.000 rows, batch size 1.000: ~10 min 10 sec
}
public void WithDeltaDestination(IConnectionManager connection)
{
//Arrange
TwoColumnsTableFixture s2c = new TwoColumnsTableFixture(connection, "DBMergeSource");
s2c.InsertTestData();
s2c.InsertTestDataSet2();
TwoColumnsTableFixture d2c = new TwoColumnsTableFixture(connection, "DBMergeDestination");
d2c.InsertTestDataSet3();
TwoColumnsDeltaTableFixture delta2Columns = new TwoColumnsDeltaTableFixture(connection, "DBMergeDelta");
DbSource <MySimpleRow> source = new DbSource <MySimpleRow>("DBMergeSource", connection);
//Act
DbMerge <MySimpleRow> merge = new DbMerge <MySimpleRow>(MergeMode.Full, "DBMergeDestination", connection);
DbDestination <MySimpleRow> delta = new DbDestination <MySimpleRow>("DBMergeDelta", connection);
source.LinkTo(merge);
merge.LinkTo(delta);
source.Execute();
merge.Wait();
delta.Wait();
//Assert
Assert.True(merge.UseTruncateMethod == true);
Assert.Equal(6, RowCountTask.Count(connection, "DBMergeDestination", $"{d2c.QB}Col1{d2c.QE} BETWEEN 1 AND 7 AND {d2c.QB}Col2{d2c.QE} LIKE 'Test%'"));
Assert.Equal(7, RowCountTask.Count(connection, "DBMergeDelta", $"{d2c.QB}Col1{d2c.QE} BETWEEN 1 AND 10 AND {d2c.QB}Col2{d2c.QE} LIKE 'Test%'"));
Assert.Equal(1, RowCountTask.Count(connection, "DBMergeDelta", $"{d2c.QB}ChangeAction{d2c.QE} = 'D' AND {d2c.QB}Col1{d2c.QE} = 10"));
Assert.Equal(3, RowCountTask.Count(connection, "DBMergeDelta", $"{d2c.QB}ChangeAction{d2c.QE} = 'U' AND {d2c.QB}Col1{d2c.QE} IN (1,2,4)"));
Assert.Equal(3, RowCountTask.Count(connection, "DBMergeDelta", $"{d2c.QB}ChangeAction{d2c.QE} = 'I' AND {d2c.QB}Col1{d2c.QE} IN (3,5,6)"));
}
public void ResetVocabularyStep()
{
var dbDestination = new DbDestination(Settings.Current.Building.DestinationConnectionString,
Settings.Current.Building.CdmSchema);
dbDestination.ExecuteQuery(Settings.Current.DropVocabularyTablesScript);
}
