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);
}
public void LogEtlMessage(EtlMessage message)
{
if (message == null)
{
throw new ArgumentNullException("message");
}
var cmdText = string.Format(@"
insert into {0}[EtlMessages]
(
[EtlPackageId],
[EtlSessionId],
[EtlStepName],
[LogDateTime],
[LogUtcDateTime],
[MessageType],
[Text],
[Flags],
[StackTrace]
)
values
(
@pid,
@sid,
@stn,
@dt,
@udt,
@mt,
@t,
@f,
@tr
)
", _schemaToken);
using (var conn = CreateConnection())
{
conn.Open();
var cmd = new SqlCommand(cmdText, conn);
AddParameter(cmd, "@pid", message.EtlPackageId);
AddParameter(cmd, "@sid", message.EtlSessionId);
AddParameter(cmd, "@stn", message.EtlStepName);
AddParameter(cmd, "@dt", message.LogDateTime);
AddParameter(cmd, "@udt", message.LogUtcDateTime);
AddParameter(cmd, "@mt", (int)message.MessageType);
AddParameter(cmd, "@t", GetSubstring(message.Text, EtlMessageTextMaxLength));
AddParameter(cmd, "@f", message.Flags);
AddParameter(cmd, "@tr", GetSubstring(message.StackTrace, EtlMessageErrorTraceMaxLength));
cmd.ExecuteNonQuery();
}
}
public void LogEtlMessage(EtlMessage message)
{
if (message == null)
{
throw new ArgumentNullException("message");
}
if (message.MessageType == EtlMessageType.StepStart)
{
_currentPackageStepIndex++;
_currentProgress = ((_currentPackageStepIndex - 1) * 100) / _packageStepCount;
}
//_worker.ReportProgress(_currentProgress, message);
}
public void BatchLogEtlMessage(EtlMessage[] messages)
{
if (messages == null )//|| messages.Any(i => i == null))
{
throw new ArgumentNullException("messages");
}
var count = 0;
var sb = GetEtlMessageBuilder();
using (var conn = CreateConnection())
{
conn.Open();
using (var tr = conn.BeginTransaction())
{
SqlCommand cmd = new SqlCommand { Connection = conn, Transaction = tr, CommandType = CommandType.Text };
foreach (var message in messages)
{
if(message == null)
continue;
if (count != 0)
{
sb.Append(",");
}
sb.AppendFormat("(@pid{0}, @sid{0}, @stn{0}, @dt{0}, @udt{0}, @mt{0}, @t{0}, @f{0}, @tr{0})", count);
AddParameter(cmd, string.Format("@pid{0}", count), message.EtlPackageId);
AddParameter(cmd, string.Format("@sid{0}", count), message.EtlSessionId);
AddParameter(cmd, string.Format("@stn{0}", count), message.EtlStepName);
AddParameter(cmd, string.Format("@dt{0}", count), message.LogDateTime);
AddParameter(cmd, string.Format("@udt{0}", count), message.LogUtcDateTime);
AddParameter(cmd, string.Format("@mt{0}", count), (int)message.MessageType);
AddParameter(cmd, string.Format("@t{0}", count), GetSubstring(message.Text, EtlMessageTextMaxLength));
AddParameter(cmd, string.Format("@f{0}", count), message.Flags);
AddParameter(cmd, string.Format("@tr{0}", count), GetSubstring(message.StackTrace, EtlMessageErrorTraceMaxLength));
if (count >= DB_INSERT_COUNT - 1)
{
// Save items
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
// Create new query
sb = GetEtlMessageBuilder();
cmd = new SqlCommand { Connection = conn, Transaction = tr, CommandType = CommandType.Text };
cmd.CommandType = CommandType.Text;
count = 0;
}
else
{
count++;
}
}
if (count > 0)
{
cmd.CommandText = sb.ToString();
cmd.ExecuteNonQuery();
}
tr.Commit();
}
}
}
public void LogEtlMessage(EtlMessage message)
{
if (message == null)
{
throw new ArgumentNullException("message");
}
var cmdText = string.Format(@"
insert into {0}[EtlMessages]
(
[EtlPackageId],
[EtlSessionId],
[EtlStepName],
[LogDateTime],
[LogUtcDateTime],
[MessageType],
[Text],
[Flags],
[StackTrace]
)
values
(
@pid,
@sid,
@stn,
@dt,
@udt,
@mt,
@t,
@f,
@tr
)
", _schemaToken);
using (var conn = CreateConnection())
{
conn.Open();
var cmd = new SqlCommand(cmdText, conn);
AddParameter(cmd, "@pid", message.EtlPackageId);
AddParameter(cmd, "@sid", message.EtlSessionId);
AddParameter(cmd, "@stn", message.EtlStepName);
AddParameter(cmd, "@dt", message.LogDateTime);
AddParameter(cmd, "@udt", message.LogUtcDateTime);
AddParameter(cmd, "@mt", (int)message.MessageType);
AddParameter(cmd, "@t", GetSubstring(message.Text, EtlMessageTextMaxLength));
AddParameter(cmd, "@f", message.Flags);
AddParameter(cmd, "@tr", GetSubstring(message.StackTrace, EtlMessageErrorTraceMaxLength));
cmd.ExecuteNonQuery();
}
}
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;
}
public void LogEtlMessage(EtlMessage message)
{
_writer.WriteLine(string.Format("[{0}] {1}", GetNowString(), message.Text));
}
