/// <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>
/// 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>
/// Pre an dpost sql statements may contain placeholder for variables.
/// Those placeholder will be replaced with variable values.
/// </summary>
/// <param name="templateStatement">sql statement</param>
/// <returns>sql statement without placeholder</returns>
public string GetExecuteStatementFromTemplate(string templateStatement)
{
string result = templateStatement;
string varName = "";
try
{
if (result != "")
{
while (result.Contains("@("))
{
IDTSVariables100 var = null;
int start = result.IndexOf("@(", 0);
int end = result.IndexOf(")", start);
varName = result.Substring(start + 2, end - start - 2);
_variableDispenser.LockOneForRead(varName, ref var);
result = result.Replace("@(" + varName + ")", var[varName].Value.ToString());
var.Unlock();
}
}
}
catch (Exception ex)
{
_events.Fire(IsagEvents.IsagEventType.ErrorVariableNotFound, "[{0}]: Variable not found: {1}",
new string[] { "Pre-/PostSql", ex.Message });
}
return(result);
}
/// <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);
}
/// <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>
/// 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>
/// 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>
/// 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>
/// 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.");
}
}
}
/// <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>
/// Executes the database command
/// (data is written from temporary table to destination table)
/// </summary>
/// <param name="tempTableName">temporary table</param>
public void ExecuteDbCommand(string tempTableName, int reattempts)
{
try
{
int rowsAffected = 0;
IsagEvents.IsagEventType eventType = IsagEvents.IsagEventType.MergeBegin;
string[] sqlTemplate = null;
_dbCommand.GetDbCommandDefinition(out eventType, out sqlTemplate);
if (sqlTemplate.Length > 0)
{
string sql;
SqlCommand comm = _conn.CreateCommand();
comm.CommandTimeout = _IsagCustomProperties.TimeOutDb;
if (_dbTransaction != null)
{
comm.Transaction = _dbTransaction;
}
for (int i = 0; i < sqlTemplate.Length; i++)
{
sql = sqlTemplate[i].Replace(Constants.TEMP_TABLE_PLACEHOLDER_BRACKETS, tempTableName)
.Replace(Constants.TEMP_TABLE_PLACEHOLDER, tempTableName);
bool executeDbCommand = true;
int attempt = 1;
while (executeDbCommand)
{
try
{
comm.CommandText = sql;
rowsAffected = comm.ExecuteNonQuery();
executeDbCommand = false;
}
catch (Exception ex)
{
if (!ex.Message.Contains("Timeout") || (attempt > reattempts && reattempts != 0))
{
_events.FireError(new string[] { string.Format("DbCommand failed. [{0}]: {1}",
DateTime.Now.ToString(), sql) + ex.ToString() });
throw ex;
}
else
{
attempt++;
_events.Fire(IsagEvents.IsagEventType.Sql,
string.Format("DbCommand: Timeout...trying again... [{0}]", DateTime.Now.ToString()));
}
}
}
}
}
_events.Fire(eventType, string.Format("[Exec DbCommand: {0}]: {1} rows were affected by the Sql Command. ({2})",
eventType.ToString(), rowsAffected.ToString(), DateTime.Now.ToString()));
}
catch (Exception ex)
{
_events.FireError(new string[] { string.Format("DbCommand failed. [{0}]",
DateTime.Now.ToString()) + ex.ToString() });
throw;
}
}
/// <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);
}