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 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);
}
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);
}