private void WriteValue(object value)
{
if (value == null || value == DBNull.Value)
{
_writer.Write("");
return;
}
//todo: create CsvStringConverter and use it or allow only strings
var str = EtlValueConverter.ToString(value);
var containsQuote = str.Contains(_quote);
var containsEscape = str.Contains(_escape);
var containsDelimiters =
containsQuote ||
containsEscape ||
str.Contains(_fieldDelimiter) ||
str.Contains(_lineDelimiter);
if (containsQuote || containsEscape)
{
str = EscapeString(str);
}
if (containsDelimiters)
{
_writer.Write(_quote);
_writer.Write(str);
_writer.Write(_quote);
}
else
{
_writer.Write(str);
}
}
public override object Evaluate(object value, IDataRecord sourceRecord)
{
if (sourceRecord == null)
{
throw new ArgumentNullException("sourceRecord");
}
if (this.Length <= 0)
{
return(null);
}
if (value == null)
{
return(null);
}
var str = EtlValueConverter.ToString(value);
var skip = Math.Max(this.Skip, 0);
var length = this.Length;
if (skip >= str.Length)
{
return(null);
}
if (skip + length > str.Length)
{
length = str.Length - skip;
}
var result = str.Substring(skip, length);
return(result);
}
public string GetString(int i)
{
var mapping = GetMapping(i);
var value = MapValue(mapping);
return(EtlValueConverter.ToString(value));
}
public override object Evaluate(object value, IDataRecord sourceRecord)
{
if (sourceRecord == null)
{
throw new ArgumentNullException("sourceRecord");
}
var str0 = EtlValueConverter.ToString(value);
var str1 = EtlValueTranslation.Evaluate(this.SourceFieldName, this.SourceFieldTranslation, sourceRecord, this.DefaultValue);
if (str0 == null || str1 == null)
{
if (this.IgnoreNulls)
{
if (str0 == null)
{
return(str1);
}
else
{
return(str0);
}
}
else
{
return(null);
}
}
else
{
return(string.Concat(str0, str1));
}
}
private List <EtlMessage> SelectEtlMessages(string etlPackageId, string etlSessionId)
{
const string sql =
@"select
m.[SequentialId],
m.[EtlPackageId],
m.[EtlSessionId],
m.[EtlStepName],
m.[LogDateTime],
m.[LogUtcDateTime],
m.[MessageType],
m.[Text],
m.[Flags],
m.[StackTrace]
from
{0}[EtlMessages] m with (nolock)
where
m.[EtlPackageId] = @EtlPackageId and
m.[EtlSessionId] = @EtlSessionId
order by
m.[SequentialId]
";
var messages = new List <EtlMessage>();
using (var conn = CreateConnection())
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(sql, _schemaToken);
cmd.Parameters.AddWithValue("@EtlPackageId", etlPackageId);
cmd.Parameters.AddWithValue("@EtlSessionId", etlSessionId);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var msg = new EtlMessage
{
SequentialId = EtlValueConverter.ParseInt64(reader["SequentialId"]),
EtlPackageId = EtlValueConverter.ToString(reader["EtlPackageId"]),
EtlSessionId = EtlValueConverter.ToString(reader["EtlSessionId"]),
EtlStepName = EtlValueConverter.ToString(reader["EtlStepName"]),
LogDateTime = EtlValueConverter.ParseDateTime(reader["LogDateTime"]),
LogUtcDateTime = EtlValueConverter.ParseDateTime(reader["LogUtcDateTime"]),
MessageType = ConvertToEtlMessageType(EtlValueConverter.ParseInt32(reader["MessageType"])),
Text = EtlValueConverter.ToString(reader["Text"]),
Flags = EtlValueConverter.ParseInt64OrNull(reader["Flags"]),
StackTrace = EtlValueConverter.ToString(reader["StackTrace"]),
};
messages.Add(msg);
}
}
}
return(messages);
}
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);
}
private List <EtlVariable> SelectEtlVariables(string etlPackageId, string etlSessionId)
{
const string sql =
@"select
sv.[Name],
sv.[Modifier],
sv.[Value],
sv.[LogDateTime],
sv.[LogUtcDateTime],
sv.[IsSecure]
from
{0}[EtlVariables] sv with (nolock)
where
sv.[EtlPackageId] = @EtlPackageId and
sv.[EtlSessionId] = @EtlSessionId
";
var variables = new List <EtlVariable>();
using (var conn = CreateConnection())
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(sql, _schemaToken);
cmd.Parameters.AddWithValue("@EtlPackageId", etlPackageId);
cmd.Parameters.AddWithValue("@EtlSessionId", etlSessionId);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var es = new EtlVariable
{
EtlPackageId = etlPackageId,
EtlSessionId = etlSessionId,
Name = EtlValueConverter.ToString(reader["Name"]),
Modifier = (EtlVariableModifier)EtlValueConverter.ParseInt32(reader["Modifier"]),
Value = EtlValueConverter.ToString(reader["Value"]),
IsSecure = EtlValueConverter.ParseBoolean(reader["IsSecure"]),
DateTime = EtlValueConverter.ParseDateTime(reader["LogDateTime"]),
UtcDateTime = EtlValueConverter.ParseDateTime(reader["LogUtcDateTime"]),
};
variables.Add(es);
}
}
}
return(variables);
}
private List <EtlCounter> SelectEtlCounters(string etlPackageId, string etlSessionId)
{
const string sql =
@"select
sv.[EntityName],
sv.[CounterName],
sv.[CounterValue],
sv.[LogDateTime],
sv.[LogUtcDateTime]
from
{0}[EtlCounters] sv with (nolock)
where
sv.[EtlPackageId] = @EtlPackageId and
sv.[EtlSessionId] = @EtlSessionId
";
var counters = new List <EtlCounter>();
using (var conn = CreateConnection())
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(sql, _schemaToken);
cmd.Parameters.AddWithValue("@EtlPackageId", etlPackageId);
cmd.Parameters.AddWithValue("@EtlSessionId", etlSessionId);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var es = new EtlCounter
{
EtlPackageId = etlPackageId,
EtlSessionId = etlSessionId,
EntityName = EtlValueConverter.ToString(reader["EntityName"]),
CounterName = EtlValueConverter.ToString(reader["CounterName"]),
CounterValue = EtlValueConverter.ParseInt64(reader["CounterValue"]),
DateTime = EtlValueConverter.ParseDateTime(reader["LogDateTime"]),
UtcDateTime = EtlValueConverter.ParseDateTime(reader["LogUtcDateTime"]),
};
counters.Add(es);
}
}
}
return(counters);
}
public override object Evaluate(object value, IDataRecord sourceRecord)
{
if (sourceRecord == null)
{
throw new ArgumentNullException("sourceRecord");
}
if (value == null)
{
return(null);
}
var result = Trim(EtlValueConverter.ToString(value));
return(result);
}
public Guid GetGuid(int i)
{
var mapping = GetMapping(i);
var value = MapValue(mapping);
Guid result;
if (EtlValueConverter.TryParseGuid(value, out result))
{
return(result);
}
else
{
throw GetConvertException(value, mapping, typeof(Guid));
}
}
public Decimal GetDecimal(int i)
{
var mapping = GetMapping(i);
var value = MapValue(mapping);
Decimal result;
if (EtlValueConverter.TryParseDecimal(value, out result))
{
return(result);
}
else
{
throw GetConvertException(value, mapping, typeof(DateTime));
}
}
public Byte GetByte(int i)
{
var mapping = GetMapping(i);
var value = MapValue(mapping);
Byte result;
if (EtlValueConverter.TryParseByte(value, out result))
{
return(result);
}
else
{
throw GetConvertException(value, mapping, typeof(Byte));
}
}
public Boolean GetBoolean(int i)
{
var mapping = GetMapping(i);
var value = MapValue(mapping);
bool result;
if (EtlValueConverter.TryParseBoolean(value, out result))
{
return(result);
}
else
{
throw GetConvertException(value, mapping, typeof(Boolean));
}
}
public long GetInt64(int i)
{
var mapping = GetMapping(i);
var value = MapValue(mapping);
Int64 result;
if (EtlValueConverter.TryParseInt64(value, out result))
{
return(result);
}
else
{
throw GetConvertException(value, mapping, typeof(Int64));
}
}
public Char GetChar(int i)
{
var mapping = GetMapping(i);
var value = MapValue(mapping);
Char result;
if (EtlValueConverter.TryParseChar(value, out result))
{
return(result);
}
else
{
throw GetConvertException(value, mapping, typeof(Char));
}
}
public Single GetFloat(int i)
{
var mapping = GetMapping(i);
var value = MapValue(mapping);
Single result;
if (EtlValueConverter.TryParseSingle(value, out result))
{
return(result);
}
else
{
throw GetConvertException(value, mapping, typeof(Single));
}
}
private EtlSession ReadEtlSession(SqlDataReader reader)
{
var session = new EtlSession
{
EtlPackageId = EtlValueConverter.ToString(reader["EtlPackageId"]),
EtlPackageName = EtlValueConverter.ToString(reader["EtlPackageName"]),
EtlSessionId = EtlValueConverter.ToString(reader["EtlSessionId"]),
StartDateTime = EtlValueConverter.ParseDateTime(reader["StartDateTime"]),
StartUtcDateTime = EtlValueConverter.ParseDateTime(reader["StartUtcDateTime"]),
EndDateTime = EtlValueConverter.ParseDateTimeOrNull(reader["EndDateTime"]),
EndUtcDateTime = EtlValueConverter.ParseDateTimeOrNull(reader["EndUtcDateTime"]),
Status = (EtlStatus)EtlValueConverter.ParseInt32(reader["Status"]),
UserName = EtlValueConverter.ToString(reader["UserName"]),
};
return(session);
}
public override object Evaluate(object value, IDataRecord sourceRecord)
{
if (sourceRecord == null)
{
throw new ArgumentNullException("sourceRecord");
}
if (value == null)
{
return(null);
}
if (string.IsNullOrEmpty(this.Regex) || string.IsNullOrEmpty(this.GroupName))
{
return(null);
}
var str = EtlValueConverter.ToString(value);
var options = GetRegexOptions();
var regex = new Regex(this.Regex, options);
var matches = regex.Match(str);
if (matches == null)
{
return(null);
}
var group = matches.Groups[this.GroupName];
if (group == null)
{
return(null);
}
else
{
if (string.IsNullOrEmpty(group.Value))
{
return(null);
}
else
{
return(group.Value);
}
}
}
private List <EtlPackage> SelectEtlPackages()
{
var sql = @"
select
p.[Id],
p.[Name],
p.[RunIntervalSeconds],
p.[Enabled]
from
{0}[EtlPackages] p with (nolock)
order by
p.[Name]
";
var packages = new List <EtlPackage>();
using (var conn = CreateConnection())
{
conn.Open();
var cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = string.Format(sql, _schemaToken);
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
packages.Add
(
new EtlPackage
{
Id = EtlValueConverter.ToString(reader["Id"]),
Name = EtlValueConverter.ToString(reader["Name"]),
Enabled = EtlValueConverter.ParseBoolean(reader["Enabled"]),
RunIntervalSeconds = Convert.ToInt32(reader["RunIntervalSeconds"]),
}
);
}
}
}
return(packages);
}
private static object[] GetParameterValues(MethodInfo method, IEnumerable <EtlMethodParameter> parameters, EtlContext context, IEtlLogger logger)
{
var parametersInfo = method.GetParameters();
var values = new object[parametersInfo.Length];
for (var i = 0; i < parametersInfo.Length; i++)
{
var parameterInfo = parametersInfo[i];
var parameter = FindParameter(parameters, parameterInfo.Name);
if (parameter == null)
{
values[i] = GetSpecialValue(parameterInfo, context, logger);
}
else
{
values[i] = EtlValueConverter.ParseType(parameter.Value, Type.GetTypeCode(parameterInfo.ParameterType));
}
}
return(values);
}
public override object Evaluate(object value, IDataRecord sourceRecord)
{
if (sourceRecord == null)
{
throw new ArgumentNullException("sourceRecord");
}
if (_rules.Count == 0)
{
return(null);
}
var str = EtlValueConverter.ToString(value);
foreach (var rule in _rules)
{
if (!string.IsNullOrEmpty(rule.Regex))
{
var options = GetRegexOptions(rule);
var regex = new Regex(rule.Regex, options);
if (regex.IsMatch(str))
{
if (rule.Result == null)
{
return(null);
}
else
{
var result = EtlValueTranslation.Evaluate(rule.Result.SourceFieldName, rule.Result.SourceFieldTranslation, sourceRecord, rule.Result.DefaultValue);
return(result);
}
}
}
}
return(null);
}
public override object Evaluate(object value, IDataRecord sourceRecord)
{
if (sourceRecord == null)
{
throw new ArgumentNullException("sourceRecord");
}
if (value == null)
{
return(null);
}
if (string.IsNullOrEmpty(this.Regex) || string.IsNullOrEmpty(this.Replacement))
{
return(null);
}
var str = EtlValueConverter.ToString(value);
var options = GetRegexOptions();
var regex = new Regex(this.Regex, options);
var result = regex.Replace(str, this.Replacement ?? "");
return(result);
}
public override object Evaluate(object value, IDataRecord sourceRecord)
{
if (sourceRecord == null)
{
throw new ArgumentNullException("sourceRecord");
}
if (_rules.Count == 0)
{
return(null);
}
var str = EtlValueConverter.ToString(value);
foreach (var rule in _rules)
{
if (string.Equals(rule.Value, str, GetStringComparison(rule)))
{
return(rule.Result);
}
}
return(null);
}
public override EtlStepResult Invoke(EtlContext context, IEtlLogger logger)
{
if (context == null)
{
throw new ArgumentNullException("context");
}
if (logger == null)
{
throw new ArgumentNullException("logger");
}
if (this.Source == null)
{
throw new InvalidOperationException("Source cannot be null");
}
if (string.IsNullOrEmpty(this.Source.ConnectionString))
{
throw new InvalidOperationException("ConnectionString cannot be empty");
}
if (string.IsNullOrEmpty(this.Source.ProviderName))
{
throw new InvalidOperationException("ProviderName cannot be empty");
}
if (string.IsNullOrEmpty(this.Source.ProcedureName))
{
throw new InvalidOperationException("ProcedureName cannot be empty");
}
var result = new EtlStepResult(EtlStatus.Succeeded, null);
var hasOutputVariables = this.OutputVariables != null && this.OutputVariables.FirstRow.Count > 0;
using (var dbAccessor = new DBAccessor(this.Source.ConnectionString, this.Source.ProviderName))
{
if (hasOutputVariables)
{
using (var dbReader = dbAccessor.ExecuteProcedureReader(this.Source.ProcedureName, EtlProcedureParameter.ToDictionary(this.Source.Parameters), this.TimeoutMilliseconds))
{
if (dbReader.Read())
{
foreach (var firstRowResult in this.OutputVariables.FirstRow)
{
var firstRowResultValue = EtlValueTranslation.Evaluate(firstRowResult.SourceFieldName, firstRowResult.SourceFieldTranslation, dbReader, firstRowResult.DefaultValue);
result.VariableAssignments.Add(new EtlVariableAssignment(firstRowResult.VariableName, EtlValueConverter.ToString(firstRowResultValue)));
}
}
}
}
else
{
dbAccessor.ExecuteProcedure(this.Source.ProcedureName, EtlProcedureParameter.ToDictionary(this.Source.Parameters), this.TimeoutMilliseconds);
}
}
return(result);
}
public override EtlStepResult Invoke(EtlContext context, IEtlLogger logger)
{
if (context == null)
{
throw new ArgumentNullException("context");
}
if (logger == null)
{
throw new ArgumentNullException("logger");
}
if (this.Source == null)
{
throw new InvalidOperationException("Source cannot be null");
}
if (string.IsNullOrEmpty(this.Source.ConnectionString))
{
throw new InvalidOperationException("Source.ConnectionString cannot be empty");
}
if (string.IsNullOrEmpty(this.Source.ProviderName))
{
throw new InvalidOperationException("Source.ProviderName cannot be empty");
}
if (string.IsNullOrEmpty(this.Source.Text))
{
throw new InvalidOperationException("Source.Text cannot be empty");
}
var result = new EtlStepResult(EtlStatus.Succeeded, null);
var rowCounter = (this.Counters != null && this.Counters.RowCount != null && !string.IsNullOrEmpty(this.Counters.RowCount.CounterName))
? new EtlCounter
{
EtlPackageId = context.EtlPackageId,
EtlSessionId = context.EtlSessionId,
EntityName = this.Counters.RowCount.EntityName,
CounterName = this.Counters.RowCount.CounterName,
}
: null;
var hasOutputVariables = this.OutputVariables != null && this.OutputVariables.FirstRow.Count > 0;
var hasRowCounter = rowCounter != null;
using (var dbAccessor = new DBAccessor(this.Source.ConnectionString, this.Source.ProviderName))
{
if (hasOutputVariables || hasRowCounter)
{
using (var dbReader = dbAccessor.ExecuteQuery(this.Source.Text, EtlQueryParameter.ToDictionary(this.Source.Parameters), this.TimeoutMilliseconds))
{
if (dbReader.Read())
{
if (hasRowCounter)
{
rowCounter.CounterValue++;
}
if (hasOutputVariables)
{
foreach (var firstRowResult in this.OutputVariables.FirstRow)
{
var firstRowResultValue = EtlValueTranslation.Evaluate(firstRowResult.SourceFieldName, firstRowResult.SourceFieldTranslation, dbReader, firstRowResult.DefaultValue);
result.VariableAssignments.Add(new EtlVariableAssignment(firstRowResult.VariableName, EtlValueConverter.ToString(firstRowResultValue)));
}
}
}
if (hasRowCounter)
{
while (dbReader.Read())
{
rowCounter.CounterValue++;
}
rowCounter.DateTime = DateTime.Now;
rowCounter.UtcDateTime = rowCounter.DateTime.ToUniversalTime();
logger.LogEtlCounter(rowCounter);
}
}
}
else
{
dbAccessor.ExecuteNonQuery(this.Source.Text, EtlQueryParameter.ToDictionary(this.Source.Parameters), this.TimeoutMilliseconds);
}
}
return(result);
}
