Esempio n. 1
0
        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);
        }
Esempio n. 3
0
        public string GetString(int i)
        {
            var mapping = GetMapping(i);
            var value   = MapValue(mapping);

            return(EtlValueConverter.ToString(value));
        }
Esempio n. 4
0
        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));
            }
        }
Esempio n. 5
0
        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);
        }
Esempio n. 6
0
        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);
        }
Esempio n. 7
0
        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);
        }
Esempio n. 8
0
        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);
        }
Esempio n. 9
0
        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);
        }
Esempio n. 10
0
        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));
            }
        }
Esempio n. 11
0
        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));
            }
        }
Esempio n. 12
0
        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));
            }
        }
Esempio n. 13
0
        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));
            }
        }
Esempio n. 14
0
        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));
            }
        }
Esempio n. 15
0
        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));
            }
        }
Esempio n. 16
0
        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));
            }
        }
Esempio n. 17
0
        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);
        }
Esempio n. 18
0
        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);
                }
            }
        }
Esempio n. 19
0
        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);
        }
Esempio n. 20
0
        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);
        }
Esempio n. 21
0
        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);
        }
Esempio n. 22
0
        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);
        }
Esempio n. 23
0
        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);
        }
Esempio n. 25
0
        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);
        }