Exemple #1
0
        public override ExitCodeType Attach(IDataLoadJob job, GracefulCancellationToken cancellationToken)
        {
            if (job == null)
                throw new Exception("Job is Null, we require to know the job to build a DataFlowPipeline");
      
            ThrowIfInvalidRemoteTableName();

            var syntax = _remoteDatabase.Server.GetQuerySyntaxHelper();

            string sql;

            if (!string.IsNullOrWhiteSpace(RemoteSelectSQL))
                sql = RemoteSelectSQL;
            else
                sql = "Select * from " + syntax.EnsureWrapped(RemoteTableName);
            
            bool scheduleMismatch = false;

            //if there is a load progress 
            if (Progress != null)
                try
                {
                    //get appropriate date declaration SQL if any
                    sql = GetScheduleParameterDeclarations(job, out scheduleMismatch) + sql;
                }
                catch (Exception e)
                {
                    //if the date range is in the future then GetScheduleParameterDeclarations will throw Exception about future dates
                    if(e.Message.StartsWith(FutureLoadMessage))
                        return ExitCodeType.OperationNotRequired;//if this is the case then don't bother with the data load

                    throw;
                }
            if (scheduleMismatch)
            {
                job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Warning, "Skipping LoadProgress '" + Progress + "' because it is not the correct Schedule for this component"));
                return ExitCodeType.Success;
            }

            job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to execute SQL:" + Environment.NewLine + sql));

            var source = new DbDataCommandDataFlowSource(sql, "Fetch data from " + _remoteDatabase.Server + " to populate RAW table " + RemoteTableName, _remoteDatabase.Server.Builder, Timeout == 0 ? 50000 : Timeout);

            //For Oracle / Postgres we have to add the parameters to the DbCommand directly
            if (_minDateParam.HasValue && _maxDateParam.HasValue && !syntax.SupportsEmbeddedParameters())
            {
                source.CommandAdjuster = (cmd) =>
                {
                    var pmin = cmd.CreateParameter();
                    pmin.Value = _minDateParam.Value;
                    pmin.ParameterName = StartDateParameter;
                    cmd.Parameters.Add(pmin);

                    var pmax = cmd.CreateParameter();
                    pmax.Value = _maxDateParam.Value;
                    pmax.ParameterName = EndDateParameter;
                    cmd.Parameters.Add(pmax);
                };
            }
                
            var destination = new SqlBulkInsertDestination(_dbInfo, RAWTableName, Enumerable.Empty<string>());

            var contextFactory = new DataFlowPipelineContextFactory<DataTable>();
            var context = contextFactory.Create(PipelineUsage.LogsToTableLoadInfo | PipelineUsage.FixedDestination);

            var engine = new DataFlowPipelineEngine<DataTable>(context, source, destination, job);

            ITableLoadInfo loadInfo = job.DataLoadInfo.CreateTableLoadInfo("Truncate RAW table " + RAWTableName,
                _dbInfo.Server.Name + "." + _dbInfo.GetRuntimeName(),
                new []
                {
                    new DataSource(
                        "Remote SqlServer Servername=" + _remoteDatabase.Server + "Database=" + _dbInfo.GetRuntimeName() +
                        
                        //Either list the table or the query depending on what is populated
                        (RemoteTableName != null?" Table=" + RemoteTableName
                            :" Query = " + sql), DateTime.Now)
                }, -1);

            engine.Initialize(loadInfo);
            engine.ExecutePipeline(new GracefulCancellationToken());

            if (source.TotalRowsRead == 0 && LoadNotRequiredIfNoRowsRead)
            {
                job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "No rows were read from the remote table and LoadNotRequiredIfNoRowsRead is true so returning ExitCodeType.LoadNotRequired"));
                return ExitCodeType.OperationNotRequired;
            }

            job.OnNotify(this, new NotifyEventArgs(source.TotalRowsRead  > 0 ? ProgressEventType.Information:ProgressEventType.Warning, "Finished after reading " + source.TotalRowsRead + " rows"));


            if (Progress != null)
            {
                if(ProgressUpdateStrategy == null)
                    throw new Exception("ProgressUpdateStrategy is null but there is a Progress");

                ProgressUpdateStrategy.AddAppropriateDisposeStep((ScheduledDataLoadJob) job,_dbInfo);

            }
                
            
            return ExitCodeType.Success;
        }
Exemple #2
0
        public override void Check(ICheckNotifier notifier)
        {

            if (RemoteServerReference != null)
            {
                if (!string.IsNullOrWhiteSpace(RemoteServer))
                    notifier.OnCheckPerformed(new CheckEventArgs("RemoteServer must be blank when you have specified a RemoteServerReference", CheckResult.Fail));
                
                if (!string.IsNullOrWhiteSpace(RemoteDatabaseName))
                    notifier.OnCheckPerformed(new CheckEventArgs("RemoteDatabaseName must be blank when you have specified a RemoteServerReference", CheckResult.Fail));

                if(RemoteTableAccessCredentials != null)
                    notifier.OnCheckPerformed(new CheckEventArgs("RemoteTableAccessCredentials must be blank when you have specified a RemoteServerReference", CheckResult.Fail));

                if (string.IsNullOrWhiteSpace(RemoteServerReference.Database))
                    notifier.OnCheckPerformed(new CheckEventArgs($"RemoteServerReference {RemoteServerReference} had no listed database to connect to", CheckResult.Fail));
            }
            else
            {
                if (string.IsNullOrWhiteSpace(RemoteServer))
                    notifier.OnCheckPerformed(new CheckEventArgs("RemoteServer is a Required field (unless you specify a RemoteServerReference)", CheckResult.Fail));
                
                if (string.IsNullOrWhiteSpace(RemoteDatabaseName))
                    notifier.OnCheckPerformed(new CheckEventArgs("RemoteDatabaseName is a Required field (unless you specify a RemoteServerReference)", CheckResult.Fail));
            }

            //if we have been initialized
            if (LoadDirectory != null)
            {
                try
                {
                    ThrowIfInvalidRemoteTableName();
                }
                catch (Exception e)
                {
                    notifier.OnCheckPerformed(new CheckEventArgs("Failed to find username and password for RemoteTableAttacher",
                        CheckResult.Fail, e));
                }

                try
                {
                    
                    try
                    {
                        Setup();
                    }
                    catch (Exception e)
                    {
                        notifier.OnCheckPerformed(new CheckEventArgs("Failed to setup username/password - proceeding with Integrated Security", CheckResult.Warning, e));
                    }
                    
                    CheckTablesExist(notifier);
                }
                catch (Exception e)
                {
                    notifier.OnCheckPerformed(new CheckEventArgs("Program crashed while trying to inspect remote server " + (RemoteServerReference ?? (object)RemoteServer ) + "  for presence of tables/databases specified in the load configuration.",
                        CheckResult.Fail, e));
                }

            }
            else
                notifier.OnCheckPerformed(new CheckEventArgs(
                    "LoadDirectory was null in Check() for class RemoteTableAttacher",
                    CheckResult.Warning, null));


            if (Progress != null)
            {
                if (!Progress.DataLoadProgress.HasValue)
                {

                    if (Progress.OriginDate.HasValue)
                    {
                        var fixDate = Progress.OriginDate.Value.AddDays(-1);

                        bool setDataLoadProgressDateToOriginDate = notifier.OnCheckPerformed(new CheckEventArgs("LoadProgress '" + Progress + "' does not have a DataLoadProgress value, you must set this to something to start loading data from that date", CheckResult.Fail, null, "Set the data load progress date to the OriginDate minus one Day? " + Environment.NewLine + "Set DataLoadProgress = " + Progress.OriginDate + " -1 day = " + fixDate)); 
                        if(setDataLoadProgressDateToOriginDate)
                        {
                            Progress.DataLoadProgress = fixDate;
                            Progress.SaveToDatabase();
                        }
                        else
                           notifier.OnCheckPerformed(new CheckEventArgs("User decided not to apply suggested fix so stopping checking",CheckResult.Fail, null));
                    }
                    else
                        notifier.OnCheckPerformed(new CheckEventArgs("LoadProgress '" + Progress + "' does not have a DataLoadProgress value, you must set this to something to start loading data from that date",CheckResult.Fail, null));
                    
                }

                if (ProgressUpdateStrategy == null)
                    notifier.OnCheckPerformed(
                        new CheckEventArgs(
                            "Progress is specified '" + Progress +
                            "' but there is no ProgressUpdateStrategy specified (if you have one you must have both)",
                            CheckResult.Fail));
                else
                    ProgressUpdateStrategy.Check(notifier);

                if (!LoadNotRequiredIfNoRowsRead)
                    notifier.OnCheckPerformed(new CheckEventArgs("LoadNotRequiredIfNoRowsRead is false but you have a Progress '" + Progress +"', this means that when the data being loaded is fully exhausted for a given range of days you will probably get an error instead of a clean shutdown",CheckResult.Warning));

                if(string.IsNullOrWhiteSpace(RemoteSelectSQL))
                    notifier.OnCheckPerformed(new CheckEventArgs("A LoadProgress has been configured but the RemoteSelectSQL is empty, how are you respecting the schedule without tailoring your query?", CheckResult.Fail, null));
                else
                {
                    foreach (string expectedParameter in new[] {StartDateParameter, EndDateParameter})
                        if (RemoteSelectSQL.Contains(expectedParameter))
                            notifier.OnCheckPerformed(new CheckEventArgs("Found " + expectedParameter + " in the RemoteSelectSQL",
                                CheckResult.Success, null));
                        else
                            notifier.OnCheckPerformed(new CheckEventArgs(
                                "Could not find any reference to parameter " + expectedParameter +
                                " in the RemoteSelectSQL, how do you expect to respect the LoadProgress you have configured without a reference to this date?", CheckResult.Fail, null));
                }
            }

            if (string.IsNullOrWhiteSpace(RAWTableName))
                notifier.OnCheckPerformed(new CheckEventArgs("RAWTableName has not been set for " + GetType().Name, CheckResult.Fail));
        }