/// <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>
/// 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;
}
/// <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;
}
/// <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);
}
/// <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);
}
/// <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()));
}
