/// <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);
        }
Ejemplo n.º 2
0
        /// <summary>
        /// Constructor (altering a table)
        /// </summary>
        /// <param name="properties">SSIS components properites</param>
        /// <param name="sqlColumns">Sql column list</param>
        /// <param name="con">Sql connection</param>
        public frmCreateTable(IsagCustomProperties properties, SqlColumnList sqlColumns, SqlConnection con)
        {
            InitializeComponent();

            this.Text  = "Alter Table";
            tbSql.Text = SqlCreator.GetAlterDestinationTable(properties, sqlColumns);
            _con       = con;
        }
Ejemplo n.º 3
0
        /// <summary>
        /// Constructor (creating a table)
        /// </summary>
        /// <param name="properties">SSIS components properites</param>
        /// <param name="con">Sql connection</param>
        public frmCreateTable(IsagCustomProperties properties, SqlConnection con)
        {
            InitializeComponent();

            this.Text  = "Create Table";
            tbSql.Text = SqlCreator.GetCreateDestinationTable(properties);
            _con       = con;
        }
Ejemplo n.º 4
0
        /// <summary>
        /// Creates temporary table
        /// </summary>
        /// <param name="tempTableName">temporary table name</param>
        public void CreateTempTable(string tempTableName)
        {
            IDTSInput100 input = _componentMetaData.InputCollection[Constants.INPUT_NAME];
            string       templateCreateTempTable = SqlCreator.GetCreateTempTable(_IsagCustomProperties, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS);
            string       sqlCreateTempTable      = templateCreateTempTable.Replace(Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS, tempTableName);

            if (!_IsagCustomProperties.UseBulkInsert)
            {
                SqlExecutor.ExecSql(sqlCreateTempTable, _bulkConn, _IsagCustomProperties.TimeOutDb, _dbTransaction);
            }
        }
        /// <summary>
        /// Creates dataTable according to properties
        /// (for all database commands except BulkInsert)
        /// </summary>
        /// <param name="input">SSIS input</param>
        /// <returns>DataTable for bulk copy into temporary table</returns>
        private DataTable CreateDataTableForTempTable(IDTSInput100 input)
        {
            DataTable dt = new DataTable();

            //foreach (ColumnInfo info in _columnInfos)
            //{
            //    ColumnConfig config = _IsagCustomProperties.GetColumnConfigByInputColumnName(info.ColumnName);

            //    Type typeNet;
            //    if (config.HasInput)
            //        typeNet = SqlCreator.GetNetDataType(input.InputColumnCollection.GetObjectByID(config.InputColumnId).DataType);
            //    else
            //        typeNet = Type.GetType(config.DataTypeOutputNet);

            //    if (_IsagCustomProperties.IsColumnUsedForBulkCopy(config))
            //        dt.Columns.Add(config.InputColumnName, typeNet);
            //}

            Dictionary <string, ColumnInfo> colInfos = new Dictionary <string, ColumnInfo>();

            foreach (ColumnInfo info in _columnInfos)
            {
                colInfos.Add(info.ColumnName, info);
            }


            _columnInfos = new List <ColumnInfo>();

            foreach (ColumnConfig config in _IsagCustomProperties.BulkCopyColumnConfigLIst)
            {
                Type typeNet;
                if (config.HasInput)
                {
                    typeNet = SqlCreator.GetNetDataType(input.InputColumnCollection.GetObjectByID(config.InputColumnId).DataType);
                }
                else
                {
                    typeNet = Type.GetType(config.DataTypeOutputNet);
                }

                //if (_IsagCustomProperties.IsColumnUsedForBulkCopy(config))
                dt.Columns.Add(config.InputColumnName, typeNet);

                _columnInfos.Add(colInfos[config.InputColumnName]);
            }

            return(dt);
        }
Ejemplo n.º 6
0
        /// <summary>
        /// Gets sql expression for column
        /// (uses default value or function if available)
        /// </summary>
        /// <returns></returns>
        public string GetColumnExpression()
        {
            string result;

            if (HasDefault)
            {
                result = " isnull(cast(src." + SqlCreator.Brackets(BulkColumnName) + " as " + DataTypeOutput + ") ," + Default + ")";
            }
            else if (HasFunction)
            {
                result = Function + " ";
            }
            else
            { //kein Default Value vorhanden
                result = "src." + SqlCreator.Brackets(BulkColumnName) + " ";
            }

            return(result);
        }
        /// <summary>
        /// Creates an sql column list from a database table
        /// </summary>
        /// <param name="con">sql connection</param>
        /// <param name="destinationTableName">destination table name</param>
        /// <returns>Sql column list</returns>
        public SqlColumnList AddSqlColumnDefinitions(SqlConnection con, string destinationTableName)
        {
            SqlColumnList result = new SqlColumnList();

            try
            {
                if (con.State != ConnectionState.Open)
                {
                    con.Open();
                }

                DataTable     schema;
                SqlConnection sqlConnnection = con;
                SqlCommand    sqlCom         = sqlConnnection.CreateCommand();


                sqlCom.CommandText = "select TOP 0 * from " + SqlCreator.Brackets(destinationTableName);
                SqlDataReader reader = sqlCom.ExecuteReader(CommandBehavior.KeyInfo);
                schema = reader.GetSchemaTable();


                foreach (DataRow row in schema.Rows)
                {
                    result.Add(row["ColumnName"].ToString(), row["DataTypeName"].ToString(), row["DataType"].ToString(),
                               Int32.Parse(row["ColumnSize"].ToString()), Int32.Parse(row["NumericPrecision"].ToString()),
                               Int32.Parse(row["NumericScale"].ToString()),
                               (row["IsKey"].ToString() == "True"), (row["IsAutoIncrement"].ToString() == "True"), (row["AllowDBNull"].ToString() == "True"));
                }

                sqlConnnection.Close();

                foreach (ColumnConfig config in this.ColumnConfigList)
                {
                    config.SetSqlColumnDefinitions(result);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return(result);
        }
Ejemplo n.º 8
0
        /// <summary>
        /// Add a bulk copy thread
        /// </summary>
        /// <param name="tempTableName">temporary tablename</param>
        /// <param name="dt">Datatable (buffer with rows to write to the temporary table)</param>
        /// <param name="useTableLock">Use tablock?</param>
        public void AddBulkCopyThread(string tempTableName, DataTable dt, bool useTableLock)
        {
            //Logging.Log(IsagEvents.IsagEventType.BulkInsert, string.Format("DataTable {0} created with {1} rows.", (_createdBulkCopyThreads + 1).ToString(), dt.Rows.Count.ToString()));
            _status.AddStatus(_createdBulkCopyThreads + 1, dt.Rows.Count, Status.StatusType.dataTableCreated, IsagEvents.IsagEventType.Status);
            UpdateStatus();

            string         templateCreateTempTable = SqlCreator.GetCreateTempTable(_isagCustomProperties, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS);
            ThreadBulkCopy thread = new ThreadBulkCopy(_dbCmdThread, tempTableName, dt, templateCreateTempTable, _timeoutDb, _reattempts, (_createdBulkCopyThreads + 1).ToString(),
                                                       _cstr, _conn, useTableLock, _isagCustomProperties.UseBulkInsert);
            bool showWaitMessage = true;

            while (WaitForFreeBulkCopyThread())
            {
                if (showWaitMessage)
                {
                    _events.Fire(IsagEvents.IsagEventType.BulkInsert, "Waiting ... Max Threat Count for BulkCopys has been reached. " + DateTime.Now.ToString());
                }
                showWaitMessage = false;
                Thread.Sleep(180); //TODO: Timeout?
                UpdateStatus();
            }

            if (!showWaitMessage)
            {
                _events.Fire(IsagEvents.IsagEventType.BulkInsert, "Waiting for free Bulkcopy Thread finished. " + DateTime.Now.ToString());
            }

            if (!HasError())
            {
                _bulkCopyThreads.Add(thread);
                _createdBulkCopyThreads++;

                _status.AddStatus(_createdBulkCopyThreads, dt.Rows.Count, Status.StatusType.bulkCopyThreadCreated, IsagEvents.IsagEventType.Status);
                //Logging.Log(IsagEvents.IsagEventType.BulkInsert, string.Format("BulkCopy Thread {0} created [Datatable {1}: {2} rows]",
                //    _createdBulkCopyThreads.ToString(), _createdBulkCopyThreads.ToString(), dt.Rows.Count.ToString()));

                thread.Start();
                Logging.Log(IsagEvents.IsagEventType.BulkInsert, string.Format("BulkCopyThread Status: {0} finished, {1} created",
                                                                               _finishedBulkCopyThreads.ToString(), _createdBulkCopyThreads.ToString()));
            }
        }
        /// <summary>
        /// Gets custom sql command
        /// </summary>
        /// <returns>custom sql command</returns>
        public string GetCustomCommand()
        {
            string result = "";

            switch (DbCommand)
            {
            case IsagCustomProperties.DbCommandType.Merge:
                result = SqlCreator.GetSqlMerge(this, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS, true);
                break;

            case IsagCustomProperties.DbCommandType.Merge2005:
                result = SqlCreator.GetSqlMerge2005(this, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS, true);
                break;

            case IsagCustomProperties.DbCommandType.UpdateTblInsertRow:
                break;

            case IsagCustomProperties.DbCommandType.UpdateRowInsertRow:
                break;

            case IsagCustomProperties.DbCommandType.BulkInsert:
                break;

            case IsagCustomProperties.DbCommandType.BulkInsertRowLock:
                break;

            case IsagCustomProperties.DbCommandType.Insert:
                result = SqlCreator.GetSqlInsert(this, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS, true);
                break;

            default:
                break;
            }

            return(result);
        }
        /// <summary>
        /// Gets sql command with placeholders for tables, columns, etc.
        /// </summary>
        /// <param name="eventType">event type</param>
        /// <param name="sqlTemplate">sql command template</param>
        public void GetDbCommandDefinition(out IsagEvents.IsagEventType eventType, out string[] sqlTemplate)
        {
            switch (_IsagCustomProperties.DbCommand)
            {
            case IsagCustomProperties.DbCommandType.Merge:
                eventType   = IsagEvents.IsagEventType.MergeBegin;
                sqlTemplate = new string[] { GetExecuteStatementFromTemplate(SqlCreator.GetSqlMerge(_IsagCustomProperties, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS)) };
                break;

            case IsagCustomProperties.DbCommandType.Merge2005:
                eventType   = IsagEvents.IsagEventType.Merge2005Begin;
                sqlTemplate = new string[] { GetExecuteStatementFromTemplate(SqlCreator.GetSqlMerge2005(_IsagCustomProperties, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS)) };
                break;

            case IsagCustomProperties.DbCommandType.UpdateTblInsertRow:
                eventType = IsagEvents.IsagEventType.UpdateTblInsertRowBegin;

                string spInsertName    = "[#" + Constants.SP_INSERT_BY_CURSOR + "_" + Guid.NewGuid().ToString() + "]";
                string sqlSpInsert     = SqlCreator.GetSqlInsertSP(_IsagCustomProperties, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS).Replace("<SPName>", spInsertName);
                string sqlUpdate       = SqlCreator.GetSqlUpdate(_IsagCustomProperties, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS);
                string sqlExecSp       = "EXEC " + spInsertName;
                string sqlDropSpInsert = "DROP PROCEDURE " + spInsertName;

                sqlTemplate = new string[] { sqlSpInsert, sqlUpdate, sqlExecSp };
                break;

            case IsagCustomProperties.DbCommandType.UpdateRowInsertRow:
                eventType = IsagEvents.IsagEventType.UpdateRowInsertRowBegin;

                string spInsertName1    = "[#" + Constants.SP_INSERT_BY_CURSOR + "_" + _componentMetaData.Name + "_" + Guid.NewGuid().ToString() + "]";
                string spUpdateName1    = "[#" + Constants.SP_UPDATE_BY_CURSOR + "_" + _componentMetaData.Name + "_" + Guid.NewGuid().ToString() + "]";
                string sqlSpInsert1     = SqlCreator.GetSqlInsertSP(_IsagCustomProperties, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS).Replace("<SPName>", spInsertName1);
                string sqlSpUpdate1     = SqlCreator.GetSqlUpdateSP(_IsagCustomProperties, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS).Replace("<SPName>", spUpdateName1);
                string sqlExecInsertSp  = "EXEC " + spInsertName1;
                string sqlExecUpdateSp  = "EXEC " + spUpdateName1;
                string sqlDropSpInsert1 = "DROP PROCEDURE " + spInsertName1;
                string sqlDropSpUpdate  = "DROP PROCEDURE " + spUpdateName1;

                sqlTemplate = new string[] { sqlSpInsert1, sqlSpUpdate1, sqlExecUpdateSp, sqlExecInsertSp };

                break;

            case IsagCustomProperties.DbCommandType.Insert:
                eventType   = IsagEvents.IsagEventType.InsertBegin;
                sqlTemplate = new string[] { GetExecuteStatementFromTemplate(SqlCreator.GetSqlInsert(_IsagCustomProperties, Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS)) };
                break;

            case IsagCustomProperties.DbCommandType.BulkInsert:
                eventType   = IsagEvents.IsagEventType.BulkInsert;
                sqlTemplate = null;
                break;

            case IsagCustomProperties.DbCommandType.BulkInsertRowLock:
                eventType   = IsagEvents.IsagEventType.BulkInsert;
                sqlTemplate = null;
                break;

            default:
                _events.FireError(new string[] { "Choosen DBCommand is invalid." });
                throw new Exception("Choosen DBCommand is invalid.");
            }
        }
        /// <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);
        }
        public void RebuildMappings(IDTSVirtualInputColumnCollection100 virtualInputColumns, IDTSInputColumnCollection100 inputColumns)
        {
            Hashtable       mappingsInput         = new Hashtable();
            List <object[]> mappingsWithoutInputs = new List <object[]>();
            List <object[]> newMappings           = new List <object[]>();

            //Speichern der bisherigen Mappings in 2 Listen (1x mit Input-Mapping, 1x ohne)
            for (int i = 0; i < this.MappingRowCount; i++)
            {
                object[] row = this.GetMappingRow(i);


                if (row[Constants.MAPPING_IDX_INPUT_COL_NAME] != null &&
                    row[Constants.MAPPING_IDX_INPUT_COL_NAME].ToString() != "")
                {
                    mappingsInput.Add(row[Constants.MAPPING_IDX_INPUT_COLUMN_ID], row);
                }
                else
                {
                    mappingsWithoutInputs.Add(row);
                }
            }


            //Generieren von neuen MappingRows anhand der InputColumns
            foreach (IDTSInputColumn100 inputCol in inputColumns)
            {
                object[] row;

                if (mappingsInput.Contains(inputCol.ID))
                {
                    row = (object[])mappingsInput[inputCol.ID];
                    row[Constants.MAPPING_IDX_INPUT_COL_NAME] = inputCol.Name;
                    row[Constants.MAPPING_IDX_DATATYPE_INPUT] = SqlCreator.GetSQLServerDataTypeFromInput(inputCol);//inputCol.DataType.ToString();
                }
                else
                {
                    row = new object[Constants.MAPPING_COUNT];

                    row[Constants.MAPPING_IDX_USE_INSERT]      = true;
                    row[Constants.MAPPING_IDX_USE_UPDATE]      = true;
                    row[Constants.MAPPING_IDX_KEY]             = false;
                    row[Constants.MAPPING_IDX_INPUT_COL_NAME]  = inputCol.Name;
                    row[Constants.MAPPING_IDX_OUTPUT_COL_NAME] = Constants.MAPPING_NOT_ASSIGNED;
                    row[Constants.MAPPING_IDX_DEFAULT]         = "";
                    row[Constants.MAPPING_IDX_DATATYPE_INPUT]  = SqlCreator.GetSQLServerDataTypeFromInput(inputCol);//inputCol.DataType.ToString();
                    row[Constants.MAPPING_IDX_INPUT_COLUMN_ID] = inputCol.ID;
                }

                newMappings.Add(row);
            }

            //Aufbauen der neuen Mapping Properties
            this.RemoveMappings();

            foreach (object[] row in newMappings)
            {
                this.AddMappingProperty(row);
            }
            foreach (object[] row in mappingsWithoutInputs)
            {
                this.AddMappingProperty(row);
            }
        }
 /// <summary>
 /// Get datatype description
 /// </summary>
 /// <returns>datatype description</returns>
 public string GetDataTypeDescription()
 {
     return(SqlCreator.GetSQLServerDataType(DataType, ColumnSize.ToString(), NumericPrecision.ToString(), NumericScale.ToString()));
 }