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 InsertOrUpdateEtlPackage(EtlPackage package)
{
var sql = @"
if exists(select 1 from {0}[EtlPackages] p with (nolock) where p.[Id] = @id)
update {0}[EtlPackages] set [Name] = @name, [Enabled] = @enabled, [RunIntervalSeconds] = @ris, [Text] = @text
where [Id] = @id
else
insert into {0}[EtlPackages]([Id], [Name], [Enabled], [RunIntervalSeconds], [Text])
values (@id, @name, @enabled, @ris, @text)
";
var serializer = new EtlPackageXmlSerializer();
var packageText = serializer.Serialize(package);
using (var conn = CreateConnection())
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(sql, _schemaToken);
cmd.Parameters.AddWithValue("id", package.Id);
cmd.Parameters.AddWithValue("name", package.Name);
cmd.Parameters.AddWithValue("enabled", package.Enabled);
cmd.Parameters.AddWithValue("ris", package.RunIntervalSeconds);
cmd.Parameters.AddWithValue("text", packageText);
cmd.ExecuteNonQuery();
}
}
private void UpdateEtlPackage(EtlPackage package)
{
var filePath = Path.Combine(_directoryPath, package.Id.ToString() + this.PackageFileExtension);
var serializer = new EtlPackageXmlSerializer();
var xml = serializer.Serialize(package);
File.WriteAllText(filePath, xml);
}
private EtlPackage SelectEtlPackage(string etlPackageId)
{
var sql = @"
select top 1
p.[Id],
p.[Name],
p.[RunIntervalSeconds],
p.[Text],
p.[Enabled]
from
{0}[EtlPackages] p with (nolock)
where
p.[Id] = @id
";
EtlPackage package = null;
using (var conn = CreateConnection())
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(sql, _schemaToken);
cmd.Parameters.AddWithValue("id", etlPackageId);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
var id = EtlValueConverter.ToString(reader["Id"]);
var name = EtlValueConverter.ToString(reader["Name"]);
var enabled = EtlValueConverter.ParseBoolean(reader["Enabled"]);
var runIntervalSeconds = Convert.ToInt32(reader["RunIntervalSeconds"]);
var text = EtlValueConverter.ToString(reader["Text"]);
if (!string.IsNullOrEmpty(text))
{
var serializer = new EtlPackageXmlSerializer();
package = serializer.Deserialize(text);
}
else
{
package = new EtlPackage();
}
package.Id = id;
package.Name = name;
package.Enabled = enabled;
package.RunIntervalSeconds = runIntervalSeconds;
}
}
}
return(package);
}
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 Exception TryGetPackage(string packageId, out EtlPackage package)
{
try
{
package = _agent.GetEtlPackage(packageId);
return(null);
}
catch (Exception exc)
{
package = null;
return(exc);
}
}
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));
}
public EtlPackage GetEtlPackage(string etlPackageId)
{
EtlPackage result = null;
foreach (var agent in _etlAgents)
{
result = agent.GetEtlPackage(etlPackageId);
if (result != null)
{
break;
}
}
return(result);
}
private void AddEtlPackage(EtlPackage package)
{
var filePath = Path.Combine(_directoryPath, package.Id.ToString() + this.PackageFileExtension);
if (File.Exists(filePath))
{
throw new EtlPackageAlreadyExistsException(package.Id);
}
var serializer = new EtlPackageXmlSerializer();
var xml = serializer.Serialize(package);
File.WriteAllText(filePath, xml);
}
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 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);
}
private Exception TryCreatePackage(string packageId, string packageName)
{
try
{
var package = new EtlPackage
{
Id = packageId,
Name = packageName,
Enabled = true,
};
_agent.DeployEtlPackage(package, null);
return(null);
}
catch (Exception exc)
{
return(exc);
}
}
private EtlPackage SelectEtlPackage(string etlPackageId)
{
var packages = SelectEtlPackages();
EtlPackage foundPackage = null;
foreach (var package in packages)
{
if (string.Equals(package.Id, etlPackageId, StringComparison.InvariantCultureIgnoreCase))
{
if (foundPackage != null)
{
throw new InvalidOperationException(string.Format("There are more than one ETL packages with identifier \"{0}\" in directory \"{1}\"", etlPackageId, _directoryPath));
}
foundPackage = package;
}
}
return(foundPackage);
}
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 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 InsertPackage(EtlPackage package)
{
var sql = @"
insert into {0}[EtlPackages]([Id], [Name], [Enabled], [RunIntervalSeconds], [Text])
values (@id, @name, @enabled, @ris, @text)
";
var serializer = new EtlPackageXmlSerializer();
var packageText = serializer.Serialize(package);
using (var conn = CreateConnection())
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(sql, _schemaToken);
cmd.Parameters.AddWithValue("id", package.Id);
cmd.Parameters.AddWithValue("name", package.Name);
cmd.Parameters.AddWithValue("enabled", package.Enabled);
cmd.Parameters.AddWithValue("ris", package.RunIntervalSeconds);
cmd.Parameters.AddWithValue("text", packageText);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException exc)
{
if (exc.Number == PRIMARY_KEY_VIOLATION_NUMBER)
{
throw new EtlPackageAlreadyExistsException(package.Id, null, exc);
}
else
{
throw;
}
}
}
}
public void DeployEtlPackage(EtlPackage package, EtlPackageDeploymentOptions options)
{
if (package == null)
{
throw new ArgumentNullException("package");
}
if (options == null)
{
options = new EtlPackageDeploymentOptions();
}
if (options.Overwrite)
{
InsertOrUpdateEtlPackage(package);
}
else
{
InsertPackage(package);
}
}
public void DeployEtlPackage(EtlPackage package, EtlPackageDeploymentOptions options)
{
if (package == null)
{
throw new ArgumentNullException("package");
}
if (options == null)
{
options = new EtlPackageDeploymentOptions();
}
if (options.Overwrite)
{
InsertOrUpdateEtlPackage(package);
}
else
{
InsertPackage(package);
}
}
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 CanUploadFileToFtp()
{
var sourceFileName = @"Sample.xml";
var destinationUrl = string.Concat(_ftpPath, "/", "Sample.xml");
var package = new EtlPackage
{
Id = Guid.NewGuid().ToString(),
Steps =
{
new EtlUploadFileStep
{
Source = new EtlFileInfo
{
FilePath = sourceFileName
},
Destination = new EtlResourceInfo
{
Uri = destinationUrl,
Method = "STOR",
}
}
}
};
Assert.IsTrue(File.Exists(sourceFileName));
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);
//var destinationData = File.ReadAllLines(destinationFileName);
//Assert.IsNotNull(destinationData);
//Assert.AreNotEqual("", destinationData);
}
public void Show(ILocalEtlAgent agent, EtlAgentInfo agentInfo, string packageId)
{
if (agent == null)
{
throw new ArgumentNullException("agent");
}
if (agentInfo == null)
{
throw new ArgumentNullException("agentInfo");
}
if (packageId == null)
{
throw new ArgumentNullException("packageId");
}
_agent = agent;
_agentInfo = agentInfo;
_packageId = packageId;
EtlPackage package;
var exc = TryGetPackage(_packageId, out package);
if (exc != null)
{
MessageForm.ShowMessage(MessageFormType.Error, exc.Message, "Cannot open package", new ExceptionInfo(exc), MessageBoxButtons.OK);
}
else
{
_currentPackage = package;
UpdateButtons();
ResetLogView();
ShowCurrentPackage();
this.Show();
}
}
public void DeployEtlPackage(EtlPackage package, EtlPackageDeploymentOptions options)
{
if (package == null)
{
throw new ArgumentNullException("package");
}
if (options == null)
{
options = new EtlPackageDeploymentOptions();
}
PrepareDirectory();
if (options.Overwrite)
{
UpdateEtlPackage(package);
}
else
{
AddEtlPackage(package);
}
}
private void UpdateEtlPackage(EtlPackage package)
{
var filePath = Path.Combine(_directoryPath, package.Id.ToString() + this.PackageFileExtension);
var serializer = new EtlPackageXmlSerializer();
var xml = serializer.Serialize(package);
File.WriteAllText(filePath, xml);
}
private void AddEtlPackage(EtlPackage package)
{
var filePath = Path.Combine(_directoryPath, package.Id.ToString() + this.PackageFileExtension);
if (File.Exists(filePath))
{
throw new EtlPackageAlreadyExistsException(package.Id);
}
var serializer = new EtlPackageXmlSerializer();
var xml = serializer.Serialize(package);
File.WriteAllText(filePath, xml);
}
public void DeployEtlPackage(EtlPackage package, EtlPackageDeploymentOptions options)
{
if (package == null)
{
throw new ArgumentNullException("package");
}
if (options == null)
{
options = new EtlPackageDeploymentOptions();
}
PrepareDirectory();
if (options.Overwrite)
{
UpdateEtlPackage(package);
}
else
{
AddEtlPackage(package);
}
}
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 InsertOrUpdateEtlPackage(EtlPackage package)
{
var sql = @"
if exists(select 1 from {0}[EtlPackages] p with (nolock) where p.[Id] = @id)
update {0}[EtlPackages] set [Name] = @name, [Enabled] = @enabled, [RunIntervalSeconds] = @ris, [Text] = @text
where [Id] = @id
else
insert into {0}[EtlPackages]([Id], [Name], [Enabled], [RunIntervalSeconds], [Text])
values (@id, @name, @enabled, @ris, @text)
";
var serializer = new EtlPackageXmlSerializer();
var packageText = serializer.Serialize(package);
using (var conn = CreateConnection())
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(sql, _schemaToken);
cmd.Parameters.AddWithValue("id", package.Id);
cmd.Parameters.AddWithValue("name", package.Name);
cmd.Parameters.AddWithValue("enabled", package.Enabled);
cmd.Parameters.AddWithValue("ris", package.RunIntervalSeconds);
cmd.Parameters.AddWithValue("text", packageText);
cmd.ExecuteNonQuery();
}
}
public void InsertPackage(EtlPackage package)
{
var sql = @"
insert into {0}[EtlPackages]([Id], [Name], [Enabled], [RunIntervalSeconds], [Text])
values (@id, @name, @enabled, @ris, @text)
";
var serializer = new EtlPackageXmlSerializer();
var packageText = serializer.Serialize(package);
using (var conn = CreateConnection())
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(sql, _schemaToken);
cmd.Parameters.AddWithValue("id", package.Id);
cmd.Parameters.AddWithValue("name", package.Name);
cmd.Parameters.AddWithValue("enabled", package.Enabled);
cmd.Parameters.AddWithValue("ris", package.RunIntervalSeconds);
cmd.Parameters.AddWithValue("text", packageText);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException exc)
{
if (exc.Number == PRIMARY_KEY_VIOLATION_NUMBER)
{
throw new EtlPackageAlreadyExistsException(package.Id, null, exc);
}
else
{
throw;
}
}
}
}
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 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 CanImportSqlTable()
{
var package = new EtlPackage
{
Id = Guid.NewGuid().ToString(),
Variables =
{
new EtlVariableInfo("connstr", EtlVariableModifier.Input, _connectionString),
new EtlVariableInfo("db_prov", 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 EtlImportTableStep
{
BatchSize = 5,
Source = new EtlTableInfo
{
ConnectionString = "$(connstr)",
ProviderName = "$(db_prov)",
TableName = "dbo.AllDataTypesTable",
},
Destination = new EtlTableInfo
{
ConnectionString = "$(connstr)",
ProviderName = "$(db_prov)",
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 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);
}
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 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);
}
private EtlPackage SelectEtlPackage(string etlPackageId)
{
var sql = @"
select top 1
p.[Id],
p.[Name],
p.[RunIntervalSeconds],
p.[Text],
p.[Enabled]
from
{0}[EtlPackages] p with (nolock)
where
p.[Id] = @id
";
EtlPackage package = null;
using (var conn = CreateConnection())
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(sql, _schemaToken);
cmd.Parameters.AddWithValue("id", etlPackageId);
using (var reader = cmd.ExecuteReader())
{
if (reader.Read())
{
var id = EtlValueConverter.ToString(reader["Id"]);
var name = EtlValueConverter.ToString(reader["Name"]);
var enabled = EtlValueConverter.ParseBoolean(reader["Enabled"]);
var runIntervalSeconds = Convert.ToInt32(reader["RunIntervalSeconds"]);
var text = EtlValueConverter.ToString(reader["Text"]);
if (!string.IsNullOrEmpty(text))
{
var serializer = new EtlPackageXmlSerializer();
package = serializer.Deserialize(text);
}
else
{
package = new EtlPackage();
}
package.Id = id;
package.Name = name;
package.Enabled = enabled;
package.RunIntervalSeconds = runIntervalSeconds;
}
}
}
return package;
}
public void DeployEtlPackage(EtlPackage package, EtlPackageDeploymentOptions options)
{
throw new NotImplementedException();
}
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 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 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 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 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 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);
}
private EtlPackageMonitorModel RefreshModel(EtlPackageMonitorModel model)
{
// обновляем модель или создаем заново
var refresh = true;
if (model == null)
{
model = new EtlPackageMonitorModel();
model.ItemsByColumns = new List <EtlPackageMonitorItem> [SiteConfiguration.MaxCoulmnsToShow];
for (var columnIndex = 0; columnIndex < SiteConfiguration.MaxCoulmnsToShow; columnIndex++)
{
model.ItemsByColumns[columnIndex] = new List <EtlPackageMonitorItem>();
}
refresh = false;
}
model.ColumnCount = SiteConfiguration.MaxCoulmnsToShow;
IEnumerable <EtlPackage> packages = new EtlPackage[0];
if (model.Items.Count() > 0)
{
// идентификаторы пакетов из модели которые необходимо обновить
var packageIds = model.Items.Where(i => i.ForceRefresh || i.EtlPackageStatus != EtlPackageMonitorItemStatus.Succeeded).Select(i => i.EtlPackageId);
// сами пакеты
packages = GetEtlPackages().Where(p => packageIds.Contains(p.Id, StringComparer.OrdinalIgnoreCase));
}
else
{
packages = GetEtlPackages();
}
var agent = SiteConfiguration.GetEtlAgent();
var logParser = agent.GetEtlLogParser();
var sessions = logParser.GetLatestEtlSessions(packages.Select(p => p.Id).ToArray());
model.HasErrors = false;
var packageIndex = 0;
foreach (var package in packages)
{
var item = new EtlPackageMonitorItem();
item.EtlPackageId = package.Id;
item.RunIntervalSeconds = package.RunIntervalSeconds;
item.EtlPackageName = package.Name;
item.CanInvoke = true;
var session = sessions.Where(s => s.EtlPackageId == package.Id).FirstOrDefault();
if (session != null)
{
item.EtlSessionId = session.EtlSessionId;
item.EtlSessionDateTime = session.EndDateTime;
item.EtlPackageStatus = EtlPackageMonitorItemStatuses.GetMonitorItemStatus(session, package.RunIntervalSeconds);
item.Counters = logParser.GetEtlCounters(package.Id, session.EtlSessionId);
var messages = logParser.GetEtlMessages(package.Id, session.EtlSessionId);
if (item.EtlPackageStatus == EtlPackageMonitorItemStatus.Failed)
{
var errorMessage = messages.OrderByDescending(m => m.SequentialId).FirstOrDefault(m => m.MessageType == EtlMessageType.Error);
item.StatusMessage = errorMessage == null ? DEFAULT_ERROR_MSG : errorMessage.Text;
}
if (item.EtlPackageStatus == EtlPackageMonitorItemStatus.TooFar && session.Status == EtlStatus.Started)
{
item.StatusMessage = PACKAGE_CURRENTLY_RUNNING;
}
}
else
{
item.EtlPackageStatus = EtlPackageMonitorItemStatus.Never;
}
if (item.EtlPackageStatus != EtlPackageMonitorItemStatus.Succeeded)
{
model.HasErrors = true;
}
if (refresh)
{
var pack = model.Items.FirstOrDefault(i => String.Equals(i.EtlPackageId, package.Id, StringComparison.OrdinalIgnoreCase));
if (pack != null)
{
pack.UpdateFromItem(item);
}
}
else
{
var columnIndex = packageIndex % SiteConfiguration.MaxCoulmnsToShow;
model.ItemsByColumns[columnIndex].Add(item);
}
packageIndex++;
}
for (var columnIndex = 0; columnIndex < model.ItemsByColumns.Length; columnIndex++)
{
if (model.MaxItemCountInColumn < model.ItemsByColumns[columnIndex].Count)
{
model.MaxItemCountInColumn = model.ItemsByColumns[columnIndex].Count;
}
}
model.LastUpdated = DateTime.Now;
return(model);
}
