private void LoadOutputColumns()
{
try
{
lvOutputColumns.SuspendLayout();
lvOutputColumns.Items.Clear();
if (UIHelper.Input2 != null && UIHelper.VirtualInput2 != null && UIHelper.Output != null)
{
//Iterate through the VirualInputColumns
foreach (IDTSVirtualInputColumn vCol in UIHelper.VirtualInput2.VirtualInputColumnCollection)
{
var item = lvOutputColumns.Items.Add(vCol.Name);
item.Tag = vCol; //Store VirtualInputColumn in the Tag
var liOutput = item.SubItems.Add(string.Empty);
item.Checked = false;
foreach (IDTSOutputColumn oCol in UIHelper.Output.OutputColumnCollection)
{
IDTSCustomProperty prop = oCol.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.SourceLineageID)];
int sourceLineageID = (int)prop.Value;
if (vCol.LineageID == sourceLineageID)
{
item.Checked = true;
liOutput.Text = oCol.Name;
liOutput.Tag = oCol.ID; //StoreOutputColumn ID into the First SubItem Tag
break;
}
}
}
}
}
finally
{
lvOutputColumns.ResumeLayout();
}
}
private void ColumnsToXmlTransformationForm_Load(object sender, EventArgs e)
{
//Get Current HistoryLookukp properties
IDTSCustomProperty noMatchBehavior = UIHelper.ComponentMetadata.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.NoMatchBehavior)];
IDTSCustomProperty cacheType = UIHelper.ComponentMetadata.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.CacheType)];
IDTSCustomProperty noMatchCacheSize = UIHelper.ComponentMetadata.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.NoMatchCacheSize)];
IDTSCustomProperty defaultCacheSize = UIHelper.ComponentMetadata.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.DefaultCacheSize)];
IDTSCustomProperty keyhashAlgorithm = UIHelper.ComponentMetadata.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.KeyHashAlgorithm)];
_historyLookupProperties = new HistoryLookupProperties(noMatchBehavior, cacheType, noMatchCacheSize, defaultCacheSize, keyhashAlgorithm);
try
{
_formLoading = true;
//Load Input columns and populate mappings ListView
LoadInputColumns();
//Load Lookup and Output Columsn and populate Output Columns ListView
LoadOutputColumns();
}
finally
{
_formLoading = false;
}
//assign handler to detect properties changes to validate status
_historyLookupProperties.ObjectPropertiesChanged += Properties_ObjectPropertiesChanged;
//Check current prperties
Properties_ObjectPropertiesChanged(_historyLookupProperties);
//Assing current proerties to PropetyGrid
propComponent.SelectedObject = _historyLookupProperties;
}
private void LoadInputColumns()
{
try
{
lvMappings.SuspendLayout();
lvMappings.Items.Clear();
int missingCount = 0;
if (UIHelper.Input != null && UIHelper.VirtualInput != null && UIHelper.Input2 != null && UIHelper.VirtualInput2 != null)
{
//Generate ColumnsMappings basedon InputColumns
foreach (IDTSInputColumn col in UIHelper.Input.InputColumnCollection)
{
IDTSCustomProperty uTypeProp = col.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.InputColumnUsageType)];
HistoryLookupTransformation.InputColumnUsageType usageType = (HistoryLookupTransformation.InputColumnUsageType)uTypeProp.Value;
IDTSCustomProperty lookLinIDProp = col.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.LookupKeyLineageID)];
int lookupLineageID = (int)lookLinIDProp.Value;
//if UsageType is LookupColumn, gegerate mapping in the MappingView
if ((usageType & HistoryLookupTransformation.InputColumnUsageType.LookupColumn) == HistoryLookupTransformation.InputColumnUsageType.LookupColumn && lookupLineageID > 0)
{
ConvertMetadataInputColumn inputDataCol = HistoryLookupTransformationUI.InputColumns.Find(ic => ic.VirtualColumn != null && ic.LineageID != 0 && ic.VirtualColumn.LineageID == col.LineageID);
ConvertMetadataInputColumn lookupCol = HistoryLookupTransformationUI.LookupColumns.Find(ic => ic.VirtualColumn != null && ic.LineageID != 0 && ic.VirtualColumn.LineageID == lookupLineageID);
if (inputDataCol != null && lookupCol != null && inputDataCol.DataType == lookupCol.DataType)
{
var li = lvMappings.Items.Add(inputDataCol.DisplayName);
li.Tag = inputDataCol;
var si = li.SubItems.Add(lookupCol.DisplayName);
si.Tag = lookupCol;
}
else
{
missingCount++;
}
}
//If UsageType is DateColumn, then assing the LineageID to the _historyLookupProperties for editing
if ((usageType & HistoryLookupTransformation.InputColumnUsageType.DateColumn) == HistoryLookupTransformation.InputColumnUsageType.DateColumn)
{
_historyLookupProperties.DataDateColumnLineageID = col.LineageID;
}
}
foreach (IDTSInputColumn col in UIHelper.Input2.InputColumnCollection)
{
IDTSCustomProperty uTypeProp = col.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.InputColumnUsageType)];
HistoryLookupTransformation.InputColumnUsageType usageType = (HistoryLookupTransformation.InputColumnUsageType)uTypeProp.Value;
IDTSCustomProperty compProp = col.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.DateComparison)];
HistoryLookupTransformation.DateComparison dateComparison = (HistoryLookupTransformation.DateComparison)compProp.Value;
//If UsageType is DateFromColumn, then assing the LineageID and DateComparison to the _historyLookupProperties for editing
if ((usageType & HistoryLookupTransformation.InputColumnUsageType.DateFromColumn) == HistoryLookupTransformation.InputColumnUsageType.DateFromColumn)
{
_historyLookupProperties.LookupDateFromLineageID = col.LineageID;
_historyLookupProperties.LookupDateFromComparison = dateComparison == HistoryLookupTransformation.DateComparison.None ? DateFromComparison.GreaterOrEqual : (DateFromComparison)dateComparison;
}
//If UsageType is DateToColumn, then assing the LineageID and DateComparison to the _historyLookupProperties for editing
if ((usageType & HistoryLookupTransformation.InputColumnUsageType.DateToColumn) == HistoryLookupTransformation.InputColumnUsageType.DateToColumn)
{
_historyLookupProperties.LookupDateToLineageID = col.LineageID;
_historyLookupProperties.LookupDateToComparison = dateComparison == HistoryLookupTransformation.DateComparison.None ? DateToComparison.Lower : (DateToComparison)dateComparison;
}
}
}
if (missingCount > 0)
{
MessageBox.Show("There are invalid mappings between columns. Invalid Mappings are removed", "History Lookup Transformation", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
finally
{
lvMappings.ResumeLayout();
btnRemoveMapping.Enabled = lvMappings.SelectedIndices.Count > 0;
}
}
private void UpdateComponentData()
{
//Set History Looup Properties
UIHelper.ComponentMetadata.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.NoMatchBehavior)].Value = _historyLookupProperties.NoMatchBehavior;
UIHelper.ComponentMetadata.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.CacheType)].Value = _historyLookupProperties.CacheType;
UIHelper.ComponentMetadata.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.NoMatchCacheSize)].Value = _historyLookupProperties.NoMatchCacheSize;
UIHelper.ComponentMetadata.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.DefaultCacheSize)].Value = _historyLookupProperties.DefaultCacheSize;
UIHelper.ComponentMetadata.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.KeyHashAlgorithm)].Value = _historyLookupProperties.KeyHashAlgorithm;
switch (_historyLookupProperties.NoMatchBehavior)
{
case HistoryLookupTransformation.NoMatchBehavior.FailComponent:
UIHelper.Input.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
UIHelper.Input.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
UIHelper.Input2.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
UIHelper.Input2.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
UIHelper.ComponentMetadata.UsesDispositions = false;
break;
case HistoryLookupTransformation.NoMatchBehavior.RedirectToErrorOutput:
UIHelper.Input.ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow;
UIHelper.Input.TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow;
UIHelper.Input2.ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow;
UIHelper.Input2.TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow;
UIHelper.ComponentMetadata.UsesDispositions = true;
break;
case HistoryLookupTransformation.NoMatchBehavior.RedirectToNoMatchOutput:
UIHelper.Input.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
UIHelper.Input.TruncationRowDisposition = DTSRowDisposition.RD_NotUsed;
UIHelper.Input2.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
UIHelper.Input2.TruncationRowDisposition = DTSRowDisposition.RD_NotUsed;
UIHelper.ComponentMetadata.UsesDispositions = false;
break;
case HistoryLookupTransformation.NoMatchBehavior.IgnoreError:
UIHelper.Input.ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;
UIHelper.Input.TruncationRowDisposition = DTSRowDisposition.RD_IgnoreFailure;
UIHelper.Input2.ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;
UIHelper.Input2.TruncationRowDisposition = DTSRowDisposition.RD_IgnoreFailure;
UIHelper.ComponentMetadata.UsesDispositions = false;
break;
default:
break;
}
//Lists to hold information about processed ooutput columns
List <OutputColumnDetail> outputCols = new List <OutputColumnDetail>();
List <OutputColumnDetail> processedOutputCols = new List <OutputColumnDetail>();
List <int> oColsToRemove = new List <int>();
if (UIHelper.Output != null)
{
//Get Output Columns Detailsfrom Output Columns ListView
foreach (ListViewItem lvi in lvOutputColumns.Items)
{
if (lvi.Checked)
{
outputCols.Add(new OutputColumnDetail((IDTSVirtualInputColumn)lvi.Tag, lvi.SubItems[1].Text, lvi.SubItems[1].Tag != null ? (int)lvi.SubItems[1].Tag : -1));
}
}
//Iterate through existing OutputColumns and try to update them
foreach (IDTSOutputColumn oCol in UIHelper.Output.OutputColumnCollection)
{
OutputColumnDetail od = outputCols.Find(ocd => ocd.ID == oCol.ID);
if (od != null)
{
outputCols.Remove(od);
processedOutputCols.Add(od);
oCol.Name = od.AliasName;
oCol.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.SourceLineageID)].Value = od.VirtualInputColumn.LineageID;
HistoryLookupTransformation.SetOutputColumnDataType(oCol, od.VirtualInputColumn);
od.OutputLineageID = oCol.LineageID;
}
else
{
oColsToRemove.Add(oCol.ID);
}
}
//Remove Not Existing Output Columns
foreach (int id in oColsToRemove)
{
UIHelper.Output.OutputColumnCollection.RemoveObjectByID(id);
}
//Add new OutputColumns
foreach (OutputColumnDetail od in outputCols)
{
processedOutputCols.Add(od);
IDTSOutputColumn oCol = UIHelper.Output.OutputColumnCollection.New();
oCol.Name = od.AliasName;
HistoryLookupTransformation.SetOutputColumnProperties(oCol);
HistoryLookupTransformation.SetOutputColumnDataType(oCol, od.VirtualInputColumn);
oCol.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.SourceLineageID)].Value = od.VirtualInputColumn.LineageID;
od.OutputLineageID = oCol.LineageID;
}
}
List <ColumnMappingDetail> mappings = new List <ColumnMappingDetail>(lvMappings.Items.Count);
//Process DataInput
if (UIHelper.VirtualInput != null)
{
//Iterete through mappings and store them in List for easy searching
foreach (ListViewItem lvi in lvMappings.Items)
{
mappings.Add(new ColumnMappingDetail(((ConvertMetadataInputColumn)lvi.Tag).LineageID, ((ConvertMetadataInputColumn)lvi.SubItems[1].Tag).LineageID));
}
//Iterate tghrough virtual input
foreach (IDTSVirtualInputColumn vCol in UIHelper.VirtualInput.VirtualInputColumnCollection)
{
int lookupLineageID = 0;
HistoryLookupTransformation.InputColumnUsageType usageType = HistoryLookupTransformation.InputColumnUsageType.None;
//if column LineageID equalsto DataDateColumnLineageID in HistoryLookuProperties, then it was selected as Date Column
if (vCol.LineageID == _historyLookupProperties.DataDateColumnLineageID)
{
usageType |= HistoryLookupTransformation.InputColumnUsageType.DateColumn;
}
//Try to lookup defined mapping
ColumnMappingDetail cmd = mappings.Find(cm => cm.DataColumnLineageID == vCol.LineageID);
//If mapping was found then it is lookup column
if (cmd != null)
{
usageType |= HistoryLookupTransformation.InputColumnUsageType.LookupColumn;
lookupLineageID = cmd.LookupColumnLineageID;
}
//If usageType is None then ignore column
if (usageType == HistoryLookupTransformation.InputColumnUsageType.None)
{
UIHelper.DesignTimeComponent.SetUsageType(UIHelper.Input.ID, UIHelper.VirtualInput, vCol.LineageID, DTSUsageType.UT_IGNORED);
}
else
{
IDTSInput inp = UIHelper.ComponentMetadata.InputCollection[0];
IDTSInputColumn iCol = UIHelper.DesignTimeComponent.SetUsageType(UIHelper.Input.ID, UIHelper.VirtualInput, vCol.LineageID, DTSUsageType.UT_READONLY);
HistoryLookupTransformation.SetDataInputColumnProperties(iCol);
iCol.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.InputColumnUsageType)].Value = usageType;
iCol.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.LookupKeyLineageID)].Value = lookupLineageID;
}
}
}
//Process HistoryLookupInput
if (UIHelper.VirtualInput2 != null)
{
foreach (IDTSVirtualInputColumn vCol in UIHelper.VirtualInput2.VirtualInputColumnCollection)
{
int oColLineageID = 0;
HistoryLookupTransformation.DateComparison dateComparison = HistoryLookupTransformation.DateComparison.None;
HistoryLookupTransformation.InputColumnUsageType usageType = HistoryLookupTransformation.InputColumnUsageType.None;
ColumnMappingDetail mapping = mappings.Find(m => m.LookupColumnLineageID == vCol.LineageID);
if (mapping != null)
{
usageType |= HistoryLookupTransformation.InputColumnUsageType.LookupColumn;
}
//If it matches LooupDateFromLineageID then it is LookupFromDate
if (vCol.LineageID == _historyLookupProperties.LookupDateFromLineageID)
{
usageType |= HistoryLookupTransformation.InputColumnUsageType.DateFromColumn;
dateComparison = (HistoryLookupTransformation.DateComparison)_historyLookupProperties.LookupDateFromComparison;
}
//If it matches LookupDateToLineageID then it is LookupToDate
if (vCol.LineageID == _historyLookupProperties.LookupDateToLineageID)
{
usageType |= HistoryLookupTransformation.InputColumnUsageType.DateToColumn;
dateComparison = (HistoryLookupTransformation.DateComparison)_historyLookupProperties.LookupDateToComparison;
}
var ocol = processedOutputCols.Find(oc => oc.VirtualInputColumn.LineageID == vCol.LineageID);
if (ocol != null)
{
usageType |= HistoryLookupTransformation.InputColumnUsageType.OutputColumn;
oColLineageID = ocol.OutputLineageID;
}
//Uf UsageType is None then Ignore the input column
if (usageType == HistoryLookupTransformation.InputColumnUsageType.None)
{
UIHelper.DesignTimeComponent.SetUsageType(UIHelper.Input2.ID, UIHelper.VirtualInput2, vCol.LineageID, DTSUsageType.UT_IGNORED);
}
else //Set the column as Input Column
{
IDTSInputColumn iCol = UIHelper.DesignTimeComponent.SetUsageType(UIHelper.Input2.ID, UIHelper.VirtualInput2, vCol.LineageID, DTSUsageType.UT_READONLY);
HistoryLookupTransformation.SetLookupInputColumnProperties(iCol);
iCol.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.InputColumnUsageType)].Value = usageType;
iCol.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.OutputColumnLineageID)].Value = oColLineageID;
iCol.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.DateComparison)].Value = dateComparison;
}
}
}
}