/// <summary>
/// Provides the component properties
/// </summary>
public override void ProvideComponentProperties()
{
base.ProvideComponentProperties();
_isagCustomProperties = new IsagCustomProperties();
//set metadata version to DLL version
ComponentMetaDataTools.UpdateVersion(this, ComponentMetaData);
//Clear out base implmentation
this.ComponentMetaData.RuntimeConnectionCollection.RemoveAll();
this.ComponentMetaData.InputCollection.RemoveAll();
this.ComponentMetaData.OutputCollection.RemoveAll();
this.RemoveAllInputsOutputsAndCustomProperties();
ComponentMetaData.UsesDispositions = false;
//Input
IDTSInput100 input = this.ComponentMetaData.InputCollection.New();
input.Name = Constants.INPUT_NAME;
input.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
//Output
IDTSOutput100 output = this.ComponentMetaData.OutputCollection.New();
output.Name = Constants.OUTPUT_NAME;
output.SynchronousInputID = input.ID;
//Custom Property: Configuration
IDTSCustomProperty100 prop = ComponentMetaData.CustomPropertyCollection.New();
prop.Name = Constants.PROP_CONFIG;
prop.Value = _isagCustomProperties.SaveToXml();
//new connection manager
IDTSRuntimeConnection100 conn = this.ComponentMetaData.RuntimeConnectionCollection.New();
conn.Name = Constants.CONNECTION_MANAGER_NAME;
conn.Description = "Connection to SQL Server";
}
/// <summary>
/// PreExecute phase: Gather all needed informations
/// </summary>
public override void PreExecute()
{
base.PreExecute();
InitializeProperties();
bool hasKey = (_isagCustomProperties.UseMatchParameter);
/* Hashtable / Get Lookup Table */
DataTable dt = new DataTable();
SqlConnection con = _mainConn;
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = _isagCustomProperties.GetSqlLookupQuery(ComponentMetaDataTools.GetTableExpressionFromTemplate(_isagCustomProperties.LU_Table, VariableDispenser, ComponentMetaData));
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(dt);
_hashLookupTable = new Hashtable();
List <object> newLine = new List <object>();
object key = null; //match parameter value
if (!hasKey)
{
key = 0; //if no match parameter is used -> the dictionaries has a single key with value 0
}
foreach (DataRow row in dt.Rows)
{
if (hasKey) //match parameter is used, so multiple keys will be used for the hashtable
{
//key/match column value: null (first row of dataTable) or has changed
if (key == null || !key.Equals(row[Constants.ALIAS_ID]))
{
//add new entry (old match column value + gathered row data) to hashtable
if (key != null)
{
_hashLookupTable.Add(key, newLine.ToArray());
}
// clear stored rows
newLine.Clear();
//get new key / match column value
if (hasKey)
{
key = row[Constants.ALIAS_ID];
}
//store row data
newLine.Add(row.ItemArray);
}
else
{
//store row data
newLine.Add(row.ItemArray);
}
}
else //match parameter is not used, so a single key will be used for the hashtable
{
newLine.Add(row.ItemArray);
}
}
//add hashtable entry at the end of the row list
if (newLine.Count > 0)
{
_hashLookupTable.Add(key, newLine.ToArray());
}
/* Input Mapping */
IDTSInput100 input = this.ComponentMetaData.InputCollection[Constants.INPUT_NAME];
_colInfo = new ColumnInfo();
_colInfo.UseMatchColumn = _isagCustomProperties.UseMatchParameter;
_colInfo.IsInclusiveUpperBound = _isagCustomProperties.LU2_IsInclusiveUpperBound;
_colInfo.BufferIndexValidFromToColumn =
this.BufferManager.FindColumnByLineageID(input.Buffer, input.InputColumnCollection[_isagCustomProperties.LU2_Validparameter].LineageID);
if (hasKey)
{
_colInfo.BufferIndexMatchColumn =
this.BufferManager.FindColumnByLineageID(input.Buffer, input.InputColumnCollection[_isagCustomProperties.LU_Matchparameter].LineageID);
}
else
{
_colInfo.BufferIndexMatchColumn = -1;
}
/* Output Mapping*/
_colInfo.BufferIndexOoutputColumns = new int[_isagCustomProperties.OutputConfigList.Count];
for (int i = 0; i < _isagCustomProperties.OutputConfigList.Count; i++)
{
_colInfo.BufferIndexOoutputColumns[i] = this.BufferManager.FindColumnByLineageID(input.Buffer, input.InputColumnCollection[_isagCustomProperties.OutputConfigList[i].DftColumn].LineageID);
}
}
/// <summary>
/// Is this component configuration valid?
/// </summary>
/// <param name="componentMetaData">the components metadata</param>
/// <param name="conn">the componenten sql connection</param>
/// <param name="variableDispenser">SSIS variables</param>
/// <returns>Is this component configuration valid?</returns>
public bool IsValid(IDTSComponentMetaData100 componentMetaData, SqlConnection conn, Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSVariableDispenser100 variableDispenser)
{
bool result = true;
//SQL Statement auf die Lookup-Tabelle prüfen
try
{
SqlCommand sqlCom = conn.CreateCommand();
sqlCom.CommandText = GetSqlLookupQuery(1, ComponentMetaDataTools.GetTableExpressionFromTemplate(LU_Table, variableDispenser, componentMetaData));
SqlDataAdapter adapter = new SqlDataAdapter(sqlCom);
DataTable dt = new DataTable();
adapter.Fill(dt);
conn.Close();
}
catch (Exception)
{
Events.Fire(componentMetaData, Events.Type.Error, "The generated SQL statement is incorrect!");
result = false;
}
//Match Parameter prüfen
if (UseMatchParameter)
{
try
{
IDTSInputColumn100 inputCol = componentMetaData.InputCollection[0].InputColumnCollection.GetObjectByID(LU_MatchparameterId);
if (inputCol.Name != LU_Matchparameter)
{
LU_Matchparameter = inputCol.Name;
Save(componentMetaData);
}
}
catch (Exception)
{
Events.Fire(componentMetaData, Events.Type.Error, "LU_Matchparameter: The column " + LU_Matchparameter + "(" + LU_MatchparameterId.ToString() + ") is not part of the InputColumnCollection.");
result = false;
}
}
//Valid Parameter prüfen
try
{
IDTSInputColumn100 inputCol = componentMetaData.InputCollection[0].InputColumnCollection.GetObjectByID(LU2_ValidparameterId);
if (inputCol.Name != LU2_Validparameter)
{
LU2_Validparameter = inputCol.Name;
Save(componentMetaData);
}
}
catch (Exception)
{
Events.Fire(componentMetaData, Events.Type.Error, "LU2_Validparameter: The column " + LU2_Validparameter + "(" + LU2_ValidparameterId.ToString() + ") is not part of the InputColumnCollection.");
result = false;
}
string sql = "";
//Output DFT Columns prüfen und SQL Statement zum prüfen der Output Table Columns erzeugen
foreach (OutputConfig outConfig in OutputConfigList)
{
try
{
if (sql != "")
{
sql += ",";
}
sql += outConfig.SqlColumn;
IDTSInputColumn100 inputCol = componentMetaData.InputCollection[0].InputColumnCollection.GetObjectByID(outConfig.DftColumnId);
if (inputCol.Name != outConfig.DftColumn)
{
outConfig.DftColumn = inputCol.Name;
Save(componentMetaData);
}
}
catch (Exception)
{
Events.Fire(componentMetaData, Events.Type.Error, "Output columns: The column " + outConfig.DftColumn + "(" + outConfig.DftColumnId.ToString() + ") is not part of the OutputColumnCollection and will be removed.");
sql = "";
result = false;
}
}
//Output Table Columns prüfen
if (sql != "")
{
try
{
SqlCommand sqlCom = conn.CreateCommand();
sqlCom.CommandText = "select top 1 " + sql + " from " + ComponentMetaDataTools.GetTableExpressionFromTemplate(LU_Table, variableDispenser, componentMetaData);
SqlDataAdapter adapter = new SqlDataAdapter(sqlCom);
DataTable dt = new DataTable();
adapter.Fill(dt);
conn.Close();
}
catch (Exception)
{
Events.Fire(componentMetaData, Events.Type.Error, "One or more output SQL columns are not part of the database table.");
result = false;
}
}
return(result);
}
