public void CanInvokeMethod()
{
var fileName = "Temp.txt";
var a = "Hello, world";
var b = "123";
var c = new DateTime(2013, 12, 15);
var package = new EtlPackage
{
Id = Guid.NewGuid().ToString(),
Variables =
{
new EtlVariableInfo("a", EtlVariableModifier.Input, a),
new EtlVariableInfo("b", EtlVariableModifier.Input, b),
new EtlVariableInfo("c", EtlVariableModifier.Input, c.ToString("o")),
},
Steps =
{
new EtlInvokeMethodStep
{
Source = new EtlMethodSourceInfo
{
AssemblyName = "RapidSoft.Etl.Runtime.Tests",
TypeName = "RapidSoft.Etl.Runtime.Tests.Steps.TestPlugin",
MethodName = "DoSomething",
Parameters =
{
new EtlMethodParameter("c", "$(c)"),
new EtlMethodParameter("b", "$(b)"),
new EtlMethodParameter("a", "$(a)"),
new EtlMethodParameter("fileName", fileName),
}
},
},
}
};
var logger = new MemoryEtlLogger();
var session = package.Invoke(logger, null, null);
Assert.IsNotNull(session);
Assert.AreEqual(1, logger.EtlSessions.Count);
Assert.AreEqual(package.Id, logger.EtlSessions[0].EtlPackageId);
Assert.AreEqual(session.EtlSessionId, logger.EtlSessions[0].EtlSessionId);
Assert.AreEqual(EtlStatus.Succeeded, logger.EtlSessions[0].Status);
var fileData = File.ReadAllText(fileName);
Assert.AreEqual(fileData, string.Format("{0}, {1}, {2:o}", a, b, c));
}
private void backgroundWorker_DoWork(object sender, DoWorkEventArgs e)
{
var package = _currentPackage;
var backgroundLogger = new BackgroundEtlLogger(backgroundWorker, package.Steps.Count);
var memoryLogger = new MemoryEtlLogger();
_agent.AttachLogger(backgroundLogger);
_agent.AttachLogger(memoryLogger);
_agent.InvokeEtlPackage(_currentPackage.Id, null, null);
e.Result = memoryLogger;
}
public void CanExecuteProcedure()
{
var package = new EtlPackage
{
Id = Guid.NewGuid().ToString(),
Variables =
{
new EtlVariableInfo("connstr", EtlVariableModifier.Input, _connectionString),
new EtlVariableInfo("ipn", EtlVariableModifier.Input, _providerName),
new EtlVariableInfo("pid", EtlVariableModifier.Bound, EtlVariableBinding.EtlPackageId),
new EtlVariableInfo("sid", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionId),
new EtlVariableInfo("dt", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionDateTime),
new EtlVariableInfo("udt", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionUtcDateTime),
new EtlVariableInfo("a", EtlVariableModifier.Output),
},
Steps =
{
new EtlExecuteProcedureStep
{
Source = new EtlProcedureSourceInfo
{
ProviderName = "$(ipn)",
ConnectionString = "$(connstr)",
ProcedureName = "dbo.CopyAllDataTypesTable",
Parameters =
{
new EtlProcedureParameter("etlPackageId", "$(pid)"),
new EtlProcedureParameter("etlSessionId", "$(sid)"),
},
},
}
}
};
var logger = new MemoryEtlLogger();
var session = package.Invoke(logger, null, null);
Assert.IsNotNull(session);
Assert.AreEqual(1, logger.EtlSessions.Count);
Assert.AreEqual(package.Id, logger.EtlSessions[0].EtlPackageId);
Assert.AreEqual(session.EtlSessionId, logger.EtlSessions[0].EtlSessionId);
Assert.AreEqual(EtlStatus.Succeeded, logger.EtlSessions[0].Status);
//todo: assert that stored procedure made some work
}
public void CanDownloadFileFromHttps()
{
var sourceUrl = string.Concat(_baseHttpsUrl, "/", "Sample.xml");
var destinationFileName = @"Sample.xml";
var package = new EtlPackage
{
Id = Guid.NewGuid().ToString(),
Steps =
{
new EtlDownloadFileStep
{
Source = new EtlResourceInfo
{
AllowInvalidCertificates = true, //"[email protected], CN=etltestbadcert, OU=Test, O=Test, L=Moscow, S=Moscow, C=RU",
Uri = sourceUrl,
Method = "GET",
//UserName = "",
//Password = "",
},
Destination = new EtlFileInfo
{
FilePath = destinationFileName
}
}
}
};
var logger = new MemoryEtlLogger();
var session = package.Invoke(logger);
Assert.AreEqual(1, logger.EtlSessions.Count);
Assert.AreEqual(package.Id, logger.EtlSessions[0].EtlPackageId);
Assert.AreEqual(session.EtlSessionId, logger.EtlSessions[0].EtlSessionId);
Assert.AreEqual(EtlStatus.Succeeded, logger.EtlSessions[0].Status);
Assert.AreEqual(EtlStatus.Succeeded, session.Status);
Assert.IsTrue(File.Exists(destinationFileName));
var destinationData = File.ReadAllLines(destinationFileName);
Assert.IsNotNull(destinationData);
Assert.AreNotEqual("", destinationData);
}
public void CanDownloadFilesFromFtp()
{
var sourceUrl = _ftpPath;
var destinationFileName = @"Test";
var package = new EtlPackage
{
Id = Guid.NewGuid().ToString(),
Steps =
{
new EtlDownloadFolderFilesStep()
{
Source = new EtlResourceInfo
{
Uri = sourceUrl,
//Method = "RETR",
},
Destination = new EtlFileInfo
{
FilePath = destinationFileName
}
}
}
};
var logger = new MemoryEtlLogger();
var session = package.Invoke(logger);
Assert.AreEqual(1, logger.EtlSessions.Count);
Assert.AreEqual(package.Id, logger.EtlSessions[0].EtlPackageId);
Assert.AreEqual(session.EtlSessionId, logger.EtlSessions[0].EtlSessionId);
Assert.AreEqual(EtlStatus.Succeeded, logger.EtlSessions[0].Status);
Assert.AreEqual(EtlStatus.Succeeded, session.Status);
Assert.IsTrue(File.Exists(destinationFileName));
var destinationData = File.ReadAllLines(destinationFileName);
Assert.IsNotNull(destinationData);
Assert.AreNotEqual("", destinationData);
}
public void CanDownloadFileFromHttp()
{
var sourceUrl = string.Concat(_baseHttpUrl, "/", "Sample.xml");
var destinationFileName = @"Sample.xml";
var package = new EtlPackage
{
Id = Guid.NewGuid().ToString(),
Steps =
{
new EtlDownloadFileStep
{
Source = new EtlResourceInfo
{
Uri = sourceUrl,
Method = "GET",
},
Destination = new EtlFileInfo
{
FilePath = destinationFileName
}
}
}
};
var logger = new MemoryEtlLogger();
var session = package.Invoke(logger);
Assert.AreEqual(1, logger.EtlSessions.Count);
Assert.AreEqual(package.Id, logger.EtlSessions[0].EtlPackageId);
Assert.AreEqual(session.EtlSessionId, logger.EtlSessions[0].EtlSessionId);
Assert.AreEqual(EtlStatus.Succeeded, logger.EtlSessions[0].Status);
Assert.AreEqual(EtlStatus.Succeeded, session.Status);
Assert.IsTrue(File.Exists(destinationFileName));
var destinationData = File.ReadAllLines(destinationFileName);
Assert.IsNotNull(destinationData);
Assert.AreNotEqual("", destinationData);
}
public void CanExportAllDataTypesCsv()
{
var connectionString = ConfigurationManager.ConnectionStrings[_connectionStringName].ConnectionString;
var importedFileName = "AllDataTypes.csv";
var exportedFileName = "AllDataTypes_Out.csv";
var package = new EtlPackage
{
Id = Guid.NewGuid().ToString(),
Variables =
{
new EtlVariableInfo("connstr", EtlVariableModifier.Input, connectionString),
new EtlVariableInfo("db_prov", EtlVariableModifier.Input, "System.Data.SqlClient"),
new EtlVariableInfo("pid", EtlVariableModifier.Bound, EtlVariableBinding.EtlPackageId),
new EtlVariableInfo("sid", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionId),
new EtlVariableInfo("dt", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionDateTime),
new EtlVariableInfo("udt", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionUtcDateTime),
},
Steps =
{
new EtlImportCsvFileStep
{
Source = new EtlCsvFileInfo
{
FilePath = importedFileName,
CodePage = 1251,
FieldDelimiter = ";",
HasHeaders = true,
},
Destination = new EtlTableInfo
{
ConnectionString = "$(connstr)",
ProviderName = "$(db_prov)",
TableName = "dbo.AllDataTypesTable",
},
Mappings =
{
new EtlFieldMapping {
DestinationFieldName = "Id", SourceFieldName = "Id"
},
new EtlFieldMapping {
DestinationFieldName = "Null", SourceFieldName = "Null"
},
new EtlFieldMapping {
DestinationFieldName = "Boolean", SourceFieldName = "Boolean"
},
new EtlFieldMapping {
DestinationFieldName = "Byte", SourceFieldName = "Byte"
},
new EtlFieldMapping {
DestinationFieldName = "DateTime", SourceFieldName = "DateTime"
},
new EtlFieldMapping {
DestinationFieldName = "Decimal", SourceFieldName = "Decimal"
},
new EtlFieldMapping {
DestinationFieldName = "Double", SourceFieldName = "Double"
},
new EtlFieldMapping {
DestinationFieldName = "Guid", SourceFieldName = "Guid"
},
new EtlFieldMapping {
DestinationFieldName = "Int16", SourceFieldName = "Int16"
},
new EtlFieldMapping {
DestinationFieldName = "Int32", SourceFieldName = "Int32"
},
new EtlFieldMapping {
DestinationFieldName = "Int64", SourceFieldName = "Int64"
},
new EtlFieldMapping {
DestinationFieldName = "Single", SourceFieldName = "Single"
},
new EtlFieldMapping {
DestinationFieldName = "String", SourceFieldName = "String"
},
new EtlFieldMapping {
DestinationFieldName = "EtlPackageId", DefaultValue = "$(pid)"
},
new EtlFieldMapping {
DestinationFieldName = "EtlSessionId", DefaultValue = "$(sid)"
},
new EtlFieldMapping {
DestinationFieldName = "EtlInsertedDateTime", DefaultValue = "$(dt)"
},
new EtlFieldMapping {
DestinationFieldName = "EtlInsertedUtcDateTime", DefaultValue = "$(udt)"
},
},
},
new EtlExportCsvFileStep
{
Source = new EtlQuerySourceInfo
{
ConnectionString = "$(connstr)",
ProviderName = "$(db_prov)",
Text = "select * from dbo.AllDataTypesTable",
},
Destination = new EtlCsvFileInfo
{
FilePath = exportedFileName,
CodePage = 1251,
FieldDelimiter = ";",
HasHeaders = true,
},
Mappings =
{
new EtlFieldMapping {
DestinationFieldName = "Id", SourceFieldName = "Id"
},
new EtlFieldMapping {
DestinationFieldName = "Null", SourceFieldName = "Null"
},
new EtlFieldMapping {
DestinationFieldName = "Boolean", SourceFieldName = "Boolean"
},
new EtlFieldMapping {
DestinationFieldName = "Byte", SourceFieldName = "Byte"
},
new EtlFieldMapping {
DestinationFieldName = "DateTime", SourceFieldName = "DateTime"
},
new EtlFieldMapping {
DestinationFieldName = "Decimal", SourceFieldName = "Decimal"
},
new EtlFieldMapping {
DestinationFieldName = "Double", SourceFieldName = "Double"
},
new EtlFieldMapping {
DestinationFieldName = "Guid", SourceFieldName = "Guid"
},
new EtlFieldMapping {
DestinationFieldName = "Int16", SourceFieldName = "Int16"
},
new EtlFieldMapping {
DestinationFieldName = "Int32", SourceFieldName = "Int32"
},
new EtlFieldMapping {
DestinationFieldName = "Int64", SourceFieldName = "Int64"
},
new EtlFieldMapping {
DestinationFieldName = "Single", SourceFieldName = "Single"
},
new EtlFieldMapping {
DestinationFieldName = "String", SourceFieldName = "String"
},
},
}
}
};
var logger = new MemoryEtlLogger();
var session = package.Invoke(logger);
Assert.IsNotNull(session);
Assert.AreEqual(1, logger.EtlSessions.Count);
Assert.AreEqual(EtlStatus.Succeeded, logger.EtlSessions[0].Status);
Assert.AreEqual(package.Id, logger.EtlSessions[0].EtlPackageId);
Assert.AreEqual(session.EtlSessionId, logger.EtlSessions[0].EtlSessionId);
//todo: decide to test that imported and exported files are identical
var importedFileData = File.ReadAllText(importedFileName);
var exportedFileData = File.ReadAllText(exportedFileName);
//Assert.AreEqual(importedFileData, exportedFileData, "Exported and imported data are not identical");
}
public void CanImportAllDataTypesCsv()
{
var connectionString = ConfigurationManager.ConnectionStrings[_connectionStringName].ConnectionString;
var package = new EtlPackage
{
Id = Guid.NewGuid().ToString(),
Variables =
{
new EtlVariableInfo("connstr", EtlVariableModifier.Input, connectionString),
new EtlVariableInfo("db_prov", EtlVariableModifier.Input, "System.Data.SqlClient"),
new EtlVariableInfo("pid", EtlVariableModifier.Bound, EtlVariableBinding.EtlPackageId),
new EtlVariableInfo("sid", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionId),
new EtlVariableInfo("dt", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionDateTime),
new EtlVariableInfo("udt", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionUtcDateTime),
},
Steps =
{
new EtlImportCsvFileStep
{
Source = new EtlCsvFileInfo
{
FilePath = "AllDataTypes.csv",
CodePage = 1251,
FieldDelimiter = ";",
HasHeaders = true,
},
Destination = new EtlTableInfo
{
ConnectionString = "$(connstr)",
ProviderName = "$(db_prov)",
TableName = "dbo.AllDataTypesTable",
},
Mappings =
{
new EtlFieldMapping {
DestinationFieldName = "Id", SourceFieldName = "Id"
},
new EtlFieldMapping {
DestinationFieldName = "Null", SourceFieldName = "Null"
},
new EtlFieldMapping {
DestinationFieldName = "Boolean", SourceFieldName = "Boolean"
},
new EtlFieldMapping {
DestinationFieldName = "Byte", SourceFieldName = "Byte"
},
new EtlFieldMapping {
DestinationFieldName = "DateTime", SourceFieldName = "DateTime"
},
new EtlFieldMapping {
DestinationFieldName = "Decimal", SourceFieldName = "Decimal"
},
new EtlFieldMapping {
DestinationFieldName = "Double", SourceFieldName = "Double"
},
new EtlFieldMapping {
DestinationFieldName = "Guid", SourceFieldName = "Guid"
},
new EtlFieldMapping {
DestinationFieldName = "Int16", SourceFieldName = "Int16"
},
new EtlFieldMapping {
DestinationFieldName = "Int32", SourceFieldName = "Int32"
},
new EtlFieldMapping {
DestinationFieldName = "Int64", SourceFieldName = "Int64"
},
new EtlFieldMapping {
DestinationFieldName = "Single", SourceFieldName = "Single"
},
new EtlFieldMapping {
DestinationFieldName = "String", SourceFieldName = "String"
},
new EtlFieldMapping {
DestinationFieldName = "EtlPackageId", DefaultValue = "$(pid)"
},
new EtlFieldMapping {
DestinationFieldName = "EtlSessionId", DefaultValue = "$(sid)"
},
new EtlFieldMapping {
DestinationFieldName = "EtlInsertedDateTime", DefaultValue = "$(dt)"
},
new EtlFieldMapping {
DestinationFieldName = "EtlInsertedUtcDateTime", DefaultValue = "$(udt)"
},
},
}
}
};
var logger = new MemoryEtlLogger();
var session = package.Invoke(logger);
Assert.IsNotNull(session);
Assert.AreEqual(1, logger.EtlSessions.Count);
Assert.AreEqual(EtlStatus.Succeeded, logger.EtlSessions[0].Status);
Assert.AreEqual(package.Id, logger.EtlSessions[0].EtlPackageId);
Assert.AreEqual(session.EtlSessionId, logger.EtlSessions[0].EtlSessionId);
//Assert.AreEqual(3, logger.EtlEntityCounters.Count);
//Assert.AreEqual(package.Id, logger.EtlEntityCounters[0].EtlPackageId);
//Assert.AreEqual(session.EtlSessionId, logger.EtlEntityCounters[0].EtlSessionId);
//Assert.AreEqual("Source", logger.EtlEntityCounters[0].CounterName);
//Assert.AreEqual(10, logger.EtlEntityCounters[0].CounterValue);
//Assert.AreEqual(package.Id, logger.EtlEntityCounters[1].EtlPackageId);
//Assert.AreEqual(session.EtlSessionId, logger.EtlEntityCounters[1].EtlSessionId);
//Assert.AreEqual("Errors", logger.EtlEntityCounters[1].CounterName);
//Assert.AreEqual(0, logger.EtlEntityCounters[1].CounterValue);
//Assert.AreEqual(package.Id, logger.EtlEntityCounters[2].EtlPackageId);
//Assert.AreEqual(session.EtlSessionId, logger.EtlEntityCounters[2].EtlSessionId);
//Assert.AreEqual("Inserted", logger.EtlEntityCounters[2].CounterName);
//Assert.AreEqual(10, logger.EtlEntityCounters[2].CounterValue);
}
public void CanImportAllDataTypesXmlWithErrors()
{
var connectionString = ConfigurationManager.ConnectionStrings[_connectionStringName].ConnectionString;
var package = new EtlPackage
{
Id = Guid.NewGuid().ToString(),
Variables =
{
new EtlVariableInfo("connstr", EtlVariableModifier.Input, connectionString),
new EtlVariableInfo("ipn", EtlVariableModifier.Input, "System.Data.SqlClient"),
new EtlVariableInfo("pid", EtlVariableModifier.Bound, EtlVariableBinding.EtlPackageId),
new EtlVariableInfo("sid", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionId),
new EtlVariableInfo("dt", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionDateTime),
new EtlVariableInfo("udt", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionUtcDateTime),
},
Steps =
{
new EtlImportFlatXmlFileStep
{
DataLossBehavior = EtlImportDataLossBehavior.Skip, //should be ignored by this step
Source = new EtlXmlFileInfo
{
FilePath = "BadAllDataTypes.xml",
DataElementPath = @"items/item",
TreatEmptyStringAsNull = true,
},
Destination = new EtlTableInfo
{
ProviderName = "$(ipn)",
ConnectionString = "$(connstr)",
TableName = "dbo.AllDataTypesTable",
},
Mappings =
{
new EtlFieldMapping {
DestinationFieldName = "Id", SourceFieldName = "id"
},
new EtlFieldMapping {
DestinationFieldName = "Null", SourceFieldName = "null"
},
new EtlFieldMapping {
DestinationFieldName = "Boolean", SourceFieldName = "boolean"
},
new EtlFieldMapping {
DestinationFieldName = "Byte", SourceFieldName = "byte"
},
new EtlFieldMapping {
DestinationFieldName = "DateTime", SourceFieldName = "datetime"
},
new EtlFieldMapping {
DestinationFieldName = "Decimal", SourceFieldName = "decimal"
},
new EtlFieldMapping {
DestinationFieldName = "Double", SourceFieldName = "double"
},
new EtlFieldMapping {
DestinationFieldName = "Guid", SourceFieldName = "guid"
},
new EtlFieldMapping {
DestinationFieldName = "Int16", SourceFieldName = "int16"
},
new EtlFieldMapping {
DestinationFieldName = "Int32", SourceFieldName = "int32"
},
new EtlFieldMapping {
DestinationFieldName = "Int64", SourceFieldName = "int64"
},
new EtlFieldMapping {
DestinationFieldName = "Single", SourceFieldName = "single"
},
new EtlFieldMapping {
DestinationFieldName = "String", SourceFieldName = "string"
},
new EtlFieldMapping {
DestinationFieldName = "EtlPackageId", DefaultValue = "$(pid)"
},
new EtlFieldMapping {
DestinationFieldName = "EtlSessionId", DefaultValue = "$(sid)"
},
new EtlFieldMapping {
DestinationFieldName = "EtlInsertedDateTime", DefaultValue = "$(dt)"
},
new EtlFieldMapping {
DestinationFieldName = "EtlInsertedUtcDateTime", DefaultValue = "$(udt)"
},
},
}
}
};
var logger = new MemoryEtlLogger();
var session = package.Invoke(logger);
Assert.IsNotNull(session);
Assert.AreEqual(1, logger.EtlSessions.Count);
Assert.AreEqual(package.Id, logger.EtlSessions[0].EtlPackageId);
Assert.AreEqual(session.EtlSessionId, logger.EtlSessions[0].EtlSessionId);
Assert.AreEqual(EtlStatus.Failed, logger.EtlSessions[0].Status);
//Assert.AreEqual(3, logger.EtlEntityCounters.Count);
//Assert.AreEqual(package.Id, logger.EtlEntityCounters[0].EtlPackageId);
//Assert.AreEqual(session.EtlSessionId, logger.EtlEntityCounters[0].EtlSessionId);
//Assert.AreEqual("Source", logger.EtlEntityCounters[0].CounterName);
//Assert.AreEqual(7, logger.EtlEntityCounters[0].CounterValue);
//Assert.AreEqual(package.Id, logger.EtlEntityCounters[1].EtlPackageId);
//Assert.AreEqual(session.EtlSessionId, logger.EtlEntityCounters[1].EtlSessionId);
//Assert.AreEqual("Errors", logger.EtlEntityCounters[1].CounterName);
//Assert.AreEqual(1, logger.EtlEntityCounters[1].CounterValue);
//Assert.AreEqual(package.Id, logger.EtlEntityCounters[2].EtlPackageId);
//Assert.AreEqual(session.EtlSessionId, logger.EtlEntityCounters[2].EtlSessionId);
//Assert.AreEqual("Inserted", logger.EtlEntityCounters[2].CounterName);
//Assert.AreEqual(6, logger.EtlEntityCounters[2].CounterValue);
}
public void CanImportSqlQuery()
{
var package = new EtlPackage
{
Id = Guid.NewGuid().ToString(),
Variables =
{
new EtlVariableInfo("connstr", EtlVariableModifier.Input, _connectionString),
new EtlVariableInfo("ipn", EtlVariableModifier.Input, _providerName),
new EtlVariableInfo("pid", EtlVariableModifier.Bound, EtlVariableBinding.EtlPackageId),
new EtlVariableInfo("sid", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionId),
new EtlVariableInfo("dt", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionDateTime),
new EtlVariableInfo("udt", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionUtcDateTime),
},
Steps =
{
new EtlImportQueryStep
{
Source = new EtlQuerySourceInfo
{
ProviderName = "$(ipn)",
ConnectionString = "$(connstr)",
Text = "select * from dbo.AllDataTypesTable",
},
Destination = new EtlTableInfo
{
ProviderName = "$(ipn)",
ConnectionString = "$(connstr)",
TableName = "dbo.AllDataTypesTableCopy",
},
Mappings =
{
new EtlFieldMapping {
DestinationFieldName = "Id", SourceFieldName = "Id"
},
new EtlFieldMapping {
DestinationFieldName = "Null", SourceFieldName = "Null"
},
new EtlFieldMapping {
DestinationFieldName = "Boolean", SourceFieldName = "Boolean"
},
new EtlFieldMapping {
DestinationFieldName = "Byte", SourceFieldName = "Byte"
},
new EtlFieldMapping {
DestinationFieldName = "DateTime", SourceFieldName = "DateTime"
},
new EtlFieldMapping {
DestinationFieldName = "Decimal", SourceFieldName = "Decimal"
},
new EtlFieldMapping {
DestinationFieldName = "Double", SourceFieldName = "Double"
},
new EtlFieldMapping {
DestinationFieldName = "Guid", SourceFieldName = "Guid"
},
new EtlFieldMapping {
DestinationFieldName = "Int16", SourceFieldName = "Int16"
},
new EtlFieldMapping {
DestinationFieldName = "Int32", SourceFieldName = "Int32"
},
new EtlFieldMapping {
DestinationFieldName = "Int64", SourceFieldName = "Int64"
},
new EtlFieldMapping {
DestinationFieldName = "Single", SourceFieldName = "Single"
},
new EtlFieldMapping {
DestinationFieldName = "String", SourceFieldName = "String"
},
new EtlFieldMapping {
DestinationFieldName = "EtlPackageId", DefaultValue = "$(pid)"
},
new EtlFieldMapping {
DestinationFieldName = "EtlSessionId", DefaultValue = "$(sid)"
},
new EtlFieldMapping {
DestinationFieldName = "EtlInsertedDateTime", DefaultValue = "$(dt)"
},
new EtlFieldMapping {
DestinationFieldName = "EtlInsertedUtcDateTime", DefaultValue = "$(udt)"
},
},
},
}
};
var logger = new MemoryEtlLogger();
var session = package.Invoke(logger, null, null);
Assert.IsNotNull(session);
Assert.AreEqual(1, logger.EtlSessions.Count);
Assert.AreEqual(package.Id, logger.EtlSessions[0].EtlPackageId);
Assert.AreEqual(session.EtlSessionId, logger.EtlSessions[0].EtlSessionId);
Assert.AreEqual(EtlStatus.Succeeded, logger.EtlSessions[0].Status);
//Assert.AreEqual(3, logger.EtlEntityCounters.Count);
//Assert.AreEqual(package.Id, logger.EtlEntityCounters[0].EtlPackageId);
//Assert.AreEqual(session.EtlSessionId, logger.EtlEntityCounters[0].EtlSessionId);
//Assert.AreEqual("Source", logger.EtlEntityCounters[0].CounterName);
//Assert.AreEqual(10, logger.EtlEntityCounters[0].CounterValue);
//Assert.AreEqual(package.Id, logger.EtlEntityCounters[1].EtlPackageId);
//Assert.AreEqual(session.EtlSessionId, logger.EtlEntityCounters[1].EtlSessionId);
//Assert.AreEqual("Errors", logger.EtlEntityCounters[1].CounterName);
//Assert.AreEqual(0, logger.EtlEntityCounters[1].CounterValue);
//Assert.AreEqual(package.Id, logger.EtlEntityCounters[2].EtlPackageId);
//Assert.AreEqual(session.EtlSessionId, logger.EtlEntityCounters[2].EtlSessionId);
//Assert.AreEqual("Inserted", logger.EtlEntityCounters[2].CounterName);
//Assert.AreEqual(10, logger.EtlEntityCounters[2].CounterValue);
}
public void CanChangeVariableAfterExecuteQuery()
{
var fileName = "Temp.csv";
var varDefaultValue = "123";
var varNewValue = "321";
var package = new EtlPackage
{
Id = Guid.NewGuid().ToString(),
Variables =
{
new EtlVariableInfo("connstr", EtlVariableModifier.Input, _connectionString),
new EtlVariableInfo("ipn", EtlVariableModifier.Input, _providerName),
new EtlVariableInfo("pid", EtlVariableModifier.Bound, EtlVariableBinding.EtlPackageId),
new EtlVariableInfo("sid", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionId),
new EtlVariableInfo("dt", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionDateTime),
new EtlVariableInfo("udt", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionUtcDateTime),
new EtlVariableInfo("mutable_var", EtlVariableModifier.Output, varDefaultValue),
},
Steps =
{
new EtlExecuteQueryStep
{
Source = new EtlQuerySourceInfo
{
ProviderName = "$(ipn)",
ConnectionString = "$(connstr)",
Text = "select @number as colNumber",
Parameters =
{
new EtlQueryParameter("number", varNewValue),
},
},
OutputVariables = new EtlExecuteQueryOutputVariableSet
{
FirstRow =
{
new EtlFieldToVariableAssignment
{
SourceFieldName = "colNumber",
VariableName = "mutable_var",
}
}
},
},
new EtlExportCsvFileStep
{
Source = new EtlQuerySourceInfo
{
ProviderName = "$(ipn)",
ConnectionString = "$(connstr)",
Text = "select @number as colNumber",
Parameters =
{
new EtlQueryParameter("number", "$(mutable_var)"),
},
},
Destination = new EtlCsvFileInfo
{
FilePath = fileName,
CodePage = 1251,
FieldDelimiter = ";",
HasHeaders = false,
},
Mappings =
{
new EtlFieldMapping {
DestinationFieldName = "colNumber", SourceFieldName = "colNumber"
},
},
},
}
};
var logger = new MemoryEtlLogger();
var session = package.Invoke(logger, null, null);
Assert.IsNotNull(session);
Assert.AreEqual(1, logger.EtlSessions.Count);
Assert.AreEqual(package.Id, logger.EtlSessions[0].EtlPackageId);
Assert.AreEqual(session.EtlSessionId, logger.EtlSessions[0].EtlSessionId);
Assert.AreEqual(EtlStatus.Succeeded, logger.EtlSessions[0].Status);
var fileData = File.ReadAllText(fileName);
Assert.AreEqual(fileData, varNewValue);
}
public void CanExecuteQuery()
{
var packageStepId = Guid.NewGuid().ToString();
var package = new EtlPackage
{
Id = Guid.NewGuid().ToString(),
Variables =
{
new EtlVariableInfo("connstr", EtlVariableModifier.Input, _connectionString),
new EtlVariableInfo("ipn", EtlVariableModifier.Input, _providerName),
new EtlVariableInfo("pid", EtlVariableModifier.Bound, EtlVariableBinding.EtlPackageId),
new EtlVariableInfo("sid", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionId),
new EtlVariableInfo("dt", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionDateTime),
new EtlVariableInfo("udt", EtlVariableModifier.Bound, EtlVariableBinding.EtlSessionUtcDateTime),
},
Steps =
{
new EtlExecuteQueryStep
{
Source = new EtlQuerySourceInfo
{
ProviderName = "$(ipn)",
ConnectionString = "$(connstr)",
Text = "select @etlPackageId as colPackageId, @etlSessionId as colSessionId, @number as colNumber",
Parameters =
{
new EtlQueryParameter("etlPackageId", "$(pid)"),
new EtlQueryParameter("etlSessionId", "$(sid)"),
new EtlQueryParameter("number", "123"),
},
},
Counters = new EtlExecuteQueryCounterSet
{
RowCount = new EtlCounterBinding
{
EntityName = "TestEntity",
CounterName = "RowCount",
}
}
}
}
};
var logger = new MemoryEtlLogger();
var session = package.Invoke(logger, null, null);
Assert.IsNotNull(session);
Assert.AreEqual(1, logger.EtlSessions.Count);
Assert.AreEqual(package.Id, logger.EtlSessions[0].EtlPackageId);
Assert.AreEqual(session.EtlSessionId, logger.EtlSessions[0].EtlSessionId);
Assert.AreEqual(EtlStatus.Succeeded, logger.EtlSessions[0].Status);
Assert.AreEqual(1, logger.EtlCounters.Count);
Assert.AreEqual("TestEntity", logger.EtlCounters[0].EntityName);
Assert.AreEqual("RowCount", logger.EtlCounters[0].CounterName);
Assert.AreEqual(package.Id, logger.EtlCounters[0].EtlPackageId);
Assert.AreEqual(session.EtlSessionId, logger.EtlCounters[0].EtlSessionId);
Assert.AreEqual(1, logger.EtlCounters[0].CounterValue);
}
public void CanValidateAllDataTypesCsv()
{
var stepName = "Validate";
var package = new EtlPackage
{
Id = Guid.NewGuid().ToString(),
Steps =
{
new EtlValidateCsvFileStep
{
Name = stepName,
Source = new EtlCsvFileInfo
{
FilePath = "BadAllDataTypes.csv",
CodePage = 1251,
FieldDelimiter = ";",
HasHeaders = true,
},
ErrorBehavior = EtlValidationErrorBehavior.ValidateAll,
BadFormatMessage = "Файл не является корректным CSV-файлом",
FieldValidationRules =
{
new EtlFieldValidationRule
{
SourceName = "Byte",
MinLength = 1,
MaxLength = 3,
Regex = @"^\d+$",
ErrorMessage = "Столбец Byte должен содержать 3-хзначное число",
},
new EtlFieldValidationRule
{
SourceName = "Double",
MinLength = 1,
MaxLength = 15,
Regex = @"^\d+(\.\d+)?$",
ErrorMessage = "Столбец Double должен содержать число",
},
new EtlFieldValidationRule
{
SourceName = "Null",
CanBeNull = true,
CanBeEmpty = true,
MinLength = 4, //this must be ignored
MaxLength = 4, //this must be ignored
ErrorMessage = "Столбец Null должен быть пустым",
},
}
}
}
};
var logger = new MemoryEtlLogger();
var session = package.Invoke(logger);
Assert.IsNotNull(session);
Assert.AreEqual(1, logger.EtlSessions.Count);
Assert.AreEqual(EtlStatus.Failed, logger.EtlSessions[0].Status);
Assert.AreEqual(package.Id, logger.EtlSessions[0].EtlPackageId);
Assert.AreEqual(session.EtlSessionId, logger.EtlSessions[0].EtlSessionId);
var validationMessages = GetErrorMessages(logger.EtlMessages, stepName);
Assert.AreEqual(3, validationMessages.Length);
Assert.AreEqual(package.Id, validationMessages[0].EtlPackageId);
Assert.AreEqual(session.EtlSessionId, validationMessages[0].EtlSessionId);
Assert.AreEqual(stepName, validationMessages[0].EtlStepName);
Assert.AreEqual(EtlMessageType.Error, validationMessages[0].MessageType);
Assert.AreEqual("Столбец Double должен содержать число", validationMessages[0].Text);
Assert.AreEqual(package.Id, validationMessages[1].EtlPackageId);
Assert.AreEqual(session.EtlSessionId, validationMessages[1].EtlSessionId);
Assert.AreEqual(stepName, validationMessages[1].EtlStepName);
Assert.AreEqual(EtlMessageType.Error, validationMessages[1].MessageType);
Assert.AreEqual("Столбец Byte должен содержать 3-хзначное число", validationMessages[1].Text);
Assert.AreEqual(package.Id, validationMessages[2].EtlPackageId);
Assert.AreEqual(session.EtlSessionId, validationMessages[2].EtlSessionId);
Assert.AreEqual(stepName, validationMessages[2].EtlStepName);
Assert.AreEqual(EtlMessageType.Error, validationMessages[2].MessageType);
Assert.AreEqual("Файл не является корректным CSV-файлом", validationMessages[2].Text);
}
