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