/// <summary>
        /// Rebuilds mapping of input and output columns
        /// (errors are corrected if possible)
        /// </summary>
        /// <param name="componentMetaData">SSIS components metadata</param>
        public void RebuildMappings(IDTSComponentMetaData100 componentMetaData, IsagEvents events)
        {
            IDTSInput100        input  = componentMetaData.InputCollection[Constants.INPUT_NAME];
            IDTSVirtualInput100 vInput = input.GetVirtualInput();

            Dictionary <int, ColumnConfig> mappingsInput         = new Dictionary <int, ColumnConfig>();
            List <ColumnConfig>            mappingsWithoutInputs = new List <ColumnConfig>();
            List <ColumnConfig>            newMappings           = new List <ColumnConfig>();

            if (this.ContainsWrongUsageType(vInput.VirtualInputColumnCollection, events))
            {
                ComponentMetaDataTools.SetUsageTypeReadOnly(vInput);
            }

            //Writre existing mappings in 2 lists (one with input columns, one without)
            foreach (ColumnConfig config in this.ColumnConfigList)
            {
                if (config.HasInput)
                {
                    mappingsInput.Add(config.InputColumnId, config);
                }
                else
                {
                    mappingsWithoutInputs.Add(config);
                }
            }

            //Generate new mapping using SSIS input columns
            foreach (IDTSInputColumn100 inputCol in input.InputColumnCollection)
            {
                ColumnConfig config;

                if (mappingsInput.ContainsKey(inputCol.ID))
                {
                    config = mappingsInput[inputCol.ID];
                    config.InputColumnName = inputCol.Name;
                    config.DataTypeInput   = SqlCreator.GetSQLServerDataTypeFromInput(inputCol, config.IsGeometryDataType);
                }
                else
                {
                    config = new ColumnConfig(inputCol.Name, SqlCreator.GetSQLServerDataTypeFromInput(inputCol, isGeometry: false), inputCol);
                }

                newMappings.Add(config);
            }

            //Add properties to the newly created mapping
            ColumnConfigList.Clear();

            foreach (ColumnConfig config in newMappings)
            {
                ColumnConfigList.Add(config);
            }
            foreach (ColumnConfig config in mappingsWithoutInputs)
            {
                ColumnConfigList.Add(config);
            }

            this.Save(componentMetaData);
        }
        /// <summary>
        /// Checks if column names and datatype are valid
        /// Does the mapping contain a row without a match (input column ids are compared) in the SSIS input column collection?
        /// If match is found: Are mappings input columm name and datatype equal to SSIS input column name and datatype?
        /// </summary>
        /// <param name="input">SSIS input</param>
        /// <param name="componentMetaData">SSIS component metadata</param>
        /// <returns>Are column names and datatypes valid?</returns>
        private bool AreColumnNamesAndDataTypesValid(IDTSInput100 input, IsagEvents events)
        {
            foreach (ColumnConfig config in this.ColumnConfigList)
            {
                if (config.HasInput)
                {
                    IDTSInputColumn100 inputColumn;

                    try
                    {
                        inputColumn = input.InputColumnCollection.GetObjectByID(config.InputColumnId);
                    }
                    catch (Exception)
                    {
                        events.Fire(IsagEvents.IsagEventType.Error, "The Mapping contains at least one column with a non existent, but assigned input column!");
                        return(false);
                    }

                    if (inputColumn.Name != config.InputColumnName || config.DataTypeInput != SqlCreator.GetSQLServerDataTypeFromInput(inputColumn, config.IsGeometryDataType))
                    {
                        events.Fire(IsagEvents.IsagEventType.Error, "The Mapping contains at least one column with a name or datatype differing from the assigned input column!");

                        return(false);
                    }
                }
            }

            return(true);
        }
 /// <summary>
 /// constructor
 /// </summary>
 /// <param name="isagCustomProperties">custom properties for the component</param>
 /// <param name="events">Isag events</param>
 /// <param name="componentMetaData">SSIS metadata for the component</param>
 /// <param name="variableDispenser">SSIS variable dispenser</param>
 public TlDbCommand(IsagCustomProperties isagCustomProperties, IsagEvents events,
                    IDTSComponentMetaData100 componentMetaData, IDTSVariableDispenser100 variableDispenser)
 {
     _IsagCustomProperties = isagCustomProperties;
     _events            = events;
     _componentMetaData = componentMetaData;
     _variableDispenser = variableDispenser;
 }
 /// <summary>
 /// Fires event/statistic/error messages
 /// </summary>
 /// <param name="events">Isag events</param>
 /// <param name="status">Status</param>
 public void FireMessages(IsagEvents events, Status status)
 {
     _messageList.FireEvents(events);
     _statusList.LogStatusEvents(status);
     if (Status == StatusType.Error)
     {
         events.FireError(new string[] { "BulkCopy", "Thread " + _threadNr.ToString(), _errorMessage });
     }
 }
Exemple #5
0
 /// <summary>
 /// constructor
 /// </summary>
 /// <param name="events">Isag events</param>
 /// <param name="conn">Main sql connection</param>
 /// <param name="isagCustomProperties">components custom properties</param>
 /// <param name="dbCommand">Database command type</param>
 /// <param name="bulkConn">Bulk csql connection</param>
 /// <param name="componentMetaData">SSIS component metadata</param>
 public TxAll(IsagEvents events, SqlConnection conn,
              IsagCustomProperties isagCustomProperties, TlDbCommand dbCommand, SqlConnection bulkConn,
              IDTSComponentMetaData100 componentMetaData)
 {
     _events = events;
     _conn   = conn;
     _IsagCustomProperties = isagCustomProperties;
     _dbCommand            = dbCommand;
     _bulkConn             = bulkConn;
     _componentMetaData    = componentMetaData;
 }
        /// <summary>
        /// If update or insert is set, a destination column has to be set, too. Checks if condition is fullfilled.
        /// </summary>
        /// <param name="componentMetaData">SSIS component metadata</param>
        /// <returns>If update or insert is set, a destination column has to be set, too. Is condition is fullfilled?</returns>
        private bool ContainsColumnConfigWithoutOutput(IsagEvents events)
        {
            foreach (ColumnConfig config in ColumnConfigList)
            {
                if (!config.HasOutput && (config.Insert || config.Update))
                {
                    events.Fire(IsagEvents.IsagEventType.Error, @"If ""Use (insert)"" oder Use ""(Update)"" is choosen a destination columns has to be selected!");
                    return(true);
                }
            }

            return(false);
        }
        /// <summary>
        /// Does the SSIS input column collection contain an input column without a match in the oclumn configuration list?
        /// </summary>
        /// <param name="vInput">SSIS virtual input</param>
        /// <param name="componentMetaData">SSIS component metadata</param>
        /// <returns>Does the SSIS input column collection contain an input column without a match in the oclumn configuration list?</returns>
        private bool ContainsInputWithoutColumnConfig(IDTSVirtualInput100 vInput, IsagEvents events)
        {
            for (int i = 0; i < vInput.VirtualInputColumnCollection.Count; i++)
            {
                if (GetColumnConfigByInputColumnName(vInput.VirtualInputColumnCollection[i].Name) == null)
                {
                    events.Fire(IsagEvents.IsagEventType.Error, "The input contains at least one column that is not assigned to a column of the Mapping!");
                    return(true);
                }
            }

            return(false);
        }
        /// <summary>
        /// Checks if SCD configuration is correct and returns Isag events if problems are found
        /// </summary>
        /// <param name="events">Isag events</param>
        private void WarnIfScdIsNotValid(IsagEvents events)
        {
            SCDList scdList = new SCDList(ColumnConfigList, DestinationTable);

            string message = "";

            if (!scdList.IsValid(ref message))
            {
                events.Fire(IsagEvents.IsagEventType.Warning, message);
            }

            bool isValid = true;

            foreach (ColumnConfig config in ColumnConfigList)
            {
                if (config.IsScdColumn && config.IsScdValidFrom)
                {
                    isValid = false;
                }
            }
            if (!isValid)
            {
                events.Fire(IsagEvents.IsagEventType.Warning, @"You have to choose ""SCD Column"" OR ""SCD ValidFrom"" for one column but not both!");
            }

            isValid = true;
            foreach (ColumnConfig config in ColumnConfigList)
            {
                if (string.IsNullOrEmpty(config.ScdTable) && (config.IsScdColumn || config.IsScdValidFrom))
                {
                    isValid = false;
                }
            }
            if (!isValid)
            {
                events.Fire(IsagEvents.IsagEventType.Warning, @"If choosing ""SCD Column"" or ""SCD ValidFrom"" you also have to fill out ""SCD Table"".");
            }

            isValid = true;
            foreach (ColumnConfig config in ColumnConfigList)
            {
                if (!string.IsNullOrEmpty(config.ScdTable) && !config.IsScdColumn && !config.IsScdValidFrom)
                {
                    isValid = false;
                }
            }
            if (!isValid)
            {
                events.Fire(IsagEvents.IsagEventType.Warning, @"If filling out ""SCD Table"" you have to choose ""SCD Column"" or ""SCD ValidFrom"".");
            }
        }
        /// <summary>
        /// Does the mapping contian ia destination column, but insert or update is not marked?
        /// </summary>
        /// <param name="componentMetaData">SSIS component metadata</param>
        /// <returns>Does the mapping contian ia destination column, but insert or update is not marked?</returns>
        private bool ContainsUnusedColumns(IsagEvents events)
        {
            foreach (ColumnConfig config in ColumnConfigList)
            {
                if (config.HasOutput && (config.HasInput || config.HasFunction || config.HasDefault) && (!config.Insert && !config.Update) && !config.IsInputColumnUsed)
                {
                    events.Fire(IsagEvents.IsagEventType.Warning,
                                @"The Mapping contains a column with an output column and an input column, function or default value, but is not marked as ""Use (Insert)"" or ""Use (Update)""");
                    return(true);
                }
            }

            return(false);
        }
        /// <summary>
        /// Initializes custom properties
        /// </summary>
        /// <param name="needsStandardConfiguration">Is standard configuration needed?</param>
        private void InitProperties(bool needsStandardConfiguration)
        {
            try
            {
                _IsagCustomProperties = IsagCustomProperties.Load(ComponentMetaData, needsStandardConfiguration);
            }
            catch (Exception ex)
            {
                _events.FireError(new string[] { "InitProperties", "Load", ex.Message });
            }


            _events        = new IsagEvents(ComponentMetaData, VariableDispenser, _IsagCustomProperties.DestinationTable, _IsagCustomProperties.CustumLoggingTemplate, _IsagCustomProperties.LogLevel);
            Logging.Events = _events;
        }
 /// <summary>
 /// If destination column is identity, neither insert nor update must be marked
 /// (exception: custom database command)
 /// </summary>
 /// <param name="componentMetaData">SSIS component metadata</param>
 /// <returns>If destination column is identity, neither insert nor update must be marked. Is condition fulfilled</returns>
 private bool InsertOrUpdateAutoIdColumn(IsagEvents events)
 {
     if (!UseCustomMergeCommand)
     {
         foreach (ColumnConfig config in ColumnConfigList)
         {
             if (config.IsOutputAutoId && (config.Insert || config.Update))
             {
                 events.Fire(IsagEvents.IsagEventType.Error, @"AutoID-Columns must not be marked as ""Use Insert"" or ""UseUpdate"".");
                 return(true);
             }
         }
     }
     return(false);
 }
        /// <summary>
        /// Validate configuration
        /// </summary>
        /// <param name="componentMetaData">SSIS component metadata</param>
        /// <returns>Configuration contains error that cann be corrected?</returns>
        public bool IsValid(IDTSComponentMetaData100 componentMetaData, IsagEvents events)
        {
            IDTSInput100        input  = componentMetaData.InputCollection[Constants.INPUT_NAME];
            IDTSVirtualInput100 vInput = input.GetVirtualInput();

            WarnIfScdIsNotValid(events);
            WarnIfMoreThanOneKeyIsSelected(events);
            ContainsUnusedColumns(events);
            IsKeyMissing(events);
            InsertOrUpdateAutoIdColumn(events);
            AreConnectionManagersValid(componentMetaData, events);
            ContainsDuplicateOutputColumn(events);

            return(!ContainsWrongUsageType(vInput.VirtualInputColumnCollection, events) && AreColumnNamesAndDataTypesValid(input, events) &&
                   !ContainsInputWithoutColumnConfig(vInput, events) && !ContainsColumnConfigWithoutOutput(events));
        }
        /// <summary>
        /// Fire even, status and error messages
        /// </summary>
        /// <param name="events">Isag events</param>
        /// <param name="status">Status</param>
        public void FireMessages(IsagEvents events, Status status)
        {
            _messageList.FireEvents(events);
            _statusList.LogStatusEvents(status);

            if (HasError)
            {
                foreach (string errorMessage in _errorMessages)
                {
                    events.FireError(new string[] { "DbCommand", errorMessage });
                }

                if (_errorMessages.Count == 0)
                {
                    events.FireError(new string[] { "DbCommand", "Fatal error: No Error Message available" });
                }
            }
        }
        /// <summary>
        /// Checks if more that one key is selected and returns Isag events if problems are found
        /// </summary>
        /// <param name="events">Isag events</param>
        private void WarnIfMoreThanOneKeyIsSelected(IsagEvents events)
        {
            if (DbCommand == DbCommandType.Merge || DbCommand == DbCommandType.Merge2005)
            {
                int keys = 0;
                foreach (ColumnConfig config in ColumnConfigList)
                {
                    if (config.Key)
                    {
                        keys++;
                    }
                }

                if (keys > 1)
                {
                    events.Fire(IsagEvents.IsagEventType.Warning, "Es sind mehrere Keys ausgewählt.");
                }
            }
        }
Exemple #15
0
        /// <summary>
        /// constructor
        /// </summary>
        /// <param name="input">SSIS input</param>
        /// <param name="isagCustomProperties">Components custom properties</param>
        /// <param name="cstr">Conectionststring</param>
        /// <param name="conn">Sql connection</param>
        /// <param name="events">Isag events</param>
        /// <param name="dbCommandEventType">Database command event type</param>
        /// <param name="dbCommandTemplate">Database command template</param>
        /// <param name="status"></param>
        public ThreadHandler(IDTSInput100 input, IsagCustomProperties isagCustomProperties,
                             string cstr, SqlConnection conn, IsagEvents events,
                             IsagEvents.IsagEventType dbCommandEventType, string[] dbCommandTemplate, Status status)
        {
            _conn = conn;

            if (dbCommandTemplate != null)
            {
                _dbCmdThread = new ThreadDbCommand(conn, dbCommandEventType, isagCustomProperties.TimeOutDb, isagCustomProperties.Reattempts, dbCommandTemplate);
            }

            _maxAllowdThreads     = isagCustomProperties.MaxThreadCount;
            _input                = input;
            _isagCustomProperties = isagCustomProperties;
            _timeoutDb            = isagCustomProperties.TimeOutDb;
            _reattempts           = isagCustomProperties.Reattempts;
            _cstr   = cstr;
            _events = events;
            _status = status;
        }
        /// <summary>
        /// For database commands other "Bulk Insert" and "Insert", at least one column has to be marked as a key.
        /// </summary>
        /// <param name="componentMetaData">SSIS component metadata</param>
        /// <returns>For database commands other "Bulk Insert" and "Insert", at least one column has to be marked as a key. Is condition fulfilled?</returns>
        private bool IsKeyMissing(IsagEvents events)
        {
            if (DbCommand != DbCommandType.BulkInsert && DbCommand != DbCommandType.BulkInsertRowLock && DbCommand != DbCommandType.Insert)
            {
                foreach (ColumnConfig config in ColumnConfigList)
                {
                    if (config.Key)
                    {
                        return(false);
                    }
                }
            }
            else
            {
                return(false);
            }

            events.Fire(IsagEvents.IsagEventType.Error, @"No Key has been selected!");

            return(true);
        }
        /// <summary>
        /// Is an output column assigned to an input column twice?
        /// </summary>
        /// <param name="events">Isag events</param>
        /// <returns>Is an output column assigned to an input column twice?
        /// </returns>
        private bool ContainsDuplicateOutputColumn(IsagEvents events)
        {
            List <string> outputColumns = new List <string>();

            foreach (ColumnConfig config in ColumnConfigList)
            {
                if (config.OutputColumnName != "")
                {
                    if (outputColumns.Contains(config.OutputColumnName))
                    {
                        events.Fire(IsagEvents.IsagEventType.Error, "Please assign Outputcolumns only once.");
                        return(true);
                    }
                    else
                    {
                        outputColumns.Add(config.OutputColumnName);
                    }
                }
            }

            return(false);
        }
        /// <summary>
        /// Connection managers are valid, if
        /// - main connection is set
        ///
        /// Sofern die externe Transaktion gewählt ist,
        /// If external transaction is used
        /// - bulk connection must be set
        /// - main connection must be able to access tthe temporary table created with the bulk connection
        /// - main connection and bulk connection must not be the same connections
        /// </summary>
        /// <param name="componentMetaData">SSIS component metadata</param>
        /// <returns>Are all connection managers valid?</returns>
        private bool AreConnectionManagersValid(IDTSComponentMetaData100 componentMetaData, IsagEvents events)
        {
            IDTSRuntimeConnection100 runtimeConn = null;
            SqlConnection            mainConn    = null;
            SqlConnection            bulkConn    = null;

            //Main
            try
            {
                runtimeConn = componentMetaData.RuntimeConnectionCollection[Constants.CONNECTION_MANAGER_NAME_MAIN];
            }
            catch (Exception) { }

            if (runtimeConn == null || runtimeConn.ConnectionManager == null)
            {
                events.Fire(IsagEvents.IsagEventType.Error, "ADO.NET [Main] DB Connection Manager has not been initialized.");
                return(false);
            }
            else
            {
                object tempConn = componentMetaData.RuntimeConnectionCollection[Constants.CONNECTION_MANAGER_NAME_MAIN].ConnectionManager.AcquireConnection(null);

                if (tempConn is SqlConnection)
                {
                    mainConn = (SqlConnection)tempConn;
                }
                else
                {
                    events.Fire(IsagEvents.IsagEventType.Error, "Only ADO.NET SQL Server connections are supported for the ADO.NET [Main] Connection.");
                    return(false);
                }
            }

            runtimeConn = null;

            //Bulk
            if (!UseExternalTransaction && mainConn != null)
            {
                bulkConn = mainConn;
            }
            else
            {
                try
                {
                    runtimeConn = componentMetaData.RuntimeConnectionCollection[Constants.CONNECTION_MANAGER_NAME_BULK];
                }
                catch (Exception) { }

                if (runtimeConn == null || runtimeConn.ConnectionManager == null)
                {
                    events.Fire(IsagEvents.IsagEventType.Error, "ADO.NET [Bulk] Connection Manager has not been initialized.");
                    return(false);
                }
                else
                {
                    object tempConn = componentMetaData.RuntimeConnectionCollection[Constants.CONNECTION_MANAGER_NAME_BULK].ConnectionManager.AcquireConnection(null);

                    if (tempConn is SqlConnection)
                    {
                        bulkConn = (SqlConnection)tempConn;
                    }
                    else
                    {
                        events.Fire(IsagEvents.IsagEventType.Error, "Only ADO.NET SQL Server connections are supported for the ADO.NET [Bulk] Connection.");
                        return(false);
                    }
                }
            }


            //External Transaction
            if (mainConn != null && bulkConn != null && UseExternalTransaction)
            {
                string mainConnectionServer = mainConn.DataSource;
                string bulkConnectionServer = bulkConn.DataSource;

                if (mainConnectionServer.StartsWith("."))
                {
                    mainConnectionServer = "localhost" + mainConnectionServer.Substring(1);
                }
                if (bulkConnectionServer.StartsWith("."))
                {
                    bulkConnectionServer = "localhost" + bulkConnectionServer.Substring(1);
                }

                // Die Main Connection muss Zugriff auf die Temporäre Tabelle der Bulk Connection haben
                if (mainConnectionServer != bulkConnectionServer)
                {
                    events.Fire(IsagEvents.IsagEventType.Error, "Please make sure that the Main Connection can access the temporary table created with the bulk Connection");

                    return(false);
                }


                // Die Main - und Bulk Connection dürfen nicht identsich sein
                bool areConnectionsIdentic =
                    componentMetaData.RuntimeConnectionCollection[Constants.CONNECTION_MANAGER_NAME_MAIN].ConnectionManagerID ==
                    componentMetaData.RuntimeConnectionCollection[Constants.CONNECTION_MANAGER_NAME_BULK].ConnectionManagerID;

                if (areConnectionsIdentic)
                {
                    events.Fire(IsagEvents.IsagEventType.Error, "Please make sure that the Main- and the Bulk Connection are not identical.");

                    return(false);
                }
            }

            return(true);
        }
        /// <summary>
        /// Are all columns usage types set to readonly?
        /// </summary>
        /// <param name="vInputColumnCollection">SSIS virtual input column collection</param>
        /// <param name="componentMetaData">SSIS component metadata</param>
        /// <returns>Are all columns usage types set to readonly?</returns>
        private bool ContainsWrongUsageType(IDTSVirtualInputColumnCollection100 vInputColumnCollection, IsagEvents events)
        {
            for (int i = 0; i < vInputColumnCollection.Count; i++)
            {
                if (vInputColumnCollection[i].UsageType != DTSUsageType.UT_READONLY)
                {
                    events.Fire(IsagEvents.IsagEventType.Error, "The UsageType of all input columns has to be ReadOnly!");
                    return(true);
                }
            }

            return(false);
        }