/// <summary>
/// Sets RowNumber default properties
/// </summary>
/// <param name="rowNumberColumn">RowNumber column which properties should be set</param>
public static void SetRowNumberColumnProperties(IDTSOutputColumn rowNumberColumn)
{
rowNumberColumn.CustomPropertyCollection.RemoveAll();
rowNumberColumn.SetDataTypeProperties(DataType.DT_I4, 0, 0, 0, 0);
//Initial Value
IDTSCustomProperty initialValue = rowNumberColumn.CustomPropertyCollection.New();
initialValue.Name = Resources.RowNumberInitialValuePropertyName;
initialValue.Description = Resources.RowNumberInitialValuePropertyDescription;
initialValue.ContainsID = false;
initialValue.EncryptionRequired = false;
initialValue.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
initialValue.Value = 1;
//Increment By
IDTSCustomProperty incrementBy = rowNumberColumn.CustomPropertyCollection.New();
incrementBy.Name = Resources.RowNumberIncrementByPropertyName;
incrementBy.Description = Resources.RowNumberIncrementByPropertyDescription;
incrementBy.ContainsID = false;
incrementBy.EncryptionRequired = false;
incrementBy.ExpressionType = DTSCustomPropertyExpressionType.CPET_NOTIFY;
incrementBy.Value = 1;
}
public DerivedColumn AddDerivedColumn(DerivedColumnItem derivedColumnItem)
{
if (!derivedColumnItem.IsReplacingColumn)
{
Pipe.IDTSOutputColumn100 col = SSISDefaultOutput.OutputColumnCollection.New();
col.Name = derivedColumnItem.Name;
col.SetDataTypeProperties((Wrapper.DataType)derivedColumnItem.DataType, derivedColumnItem.Length, derivedColumnItem.Precision, derivedColumnItem.Scale, derivedColumnItem.CodePage);
col.ExternalMetadataColumnID = 0;
col.ErrorRowDisposition = Pipe.DTSRowDisposition.RD_FailComponent;
col.TruncationRowDisposition = Pipe.DTSRowDisposition.RD_FailComponent;
Pipe.IDTSCustomProperty100 propEx = col.CustomPropertyCollection.New();
propEx.Name = "Expression";
propEx.Value = derivedColumnItem.Expression;
Pipe.IDTSCustomProperty100 propFex = col.CustomPropertyCollection.New();
propFex.Name = "FriendlyExpression";
propFex.Value = derivedColumnItem.Expression;
}
else
{
Pipe.IDTSInputColumn100 col = base.FindSSISInputColumn(derivedColumnItem.ReplaceColumnName);
col.ExternalMetadataColumnID = 0;
col.ErrorRowDisposition = Pipe.DTSRowDisposition.RD_FailComponent;
col.TruncationRowDisposition = Pipe.DTSRowDisposition.RD_FailComponent;
Pipe.IDTSCustomProperty100 propEx = col.CustomPropertyCollection["Expression"];
propEx.Value = derivedColumnItem.Expression;
Pipe.IDTSCustomProperty100 propFex = col.CustomPropertyCollection["FriendlyExpression"];
propFex.Value = derivedColumnItem.Expression;
}
return(this);
}
//Dissallow DT_NTEXT, DT_TEXT and DT_IMAGE data types.
public override bool CheckColumnForInputValidity(IDTSVirtualInput vInput, IDTSInput input, FormInputColumn icol)
{
bool validForInput = false;
var dt = icol.DTSVirtualColumn.DataType;
if (dt == DataType.DT_NTEXT || dt == DataType.DT_TEXT || dt == DataType.DT_IMAGE)
{
validForInput = false;
}
else
{
if (icol.DTSInputColumn != null)
{
//IDTSCustomProperty nullCol = icol.DTSInputColumn.CustomPropertyCollection[Resources.LookupErrorAggIsNullColumnName];
//bool isNullCol = (bool)nullCol.Value;
IDTSCustomProperty keyCol = icol.DTSInputColumn.CustomPropertyCollection[Resources.LookupErrorAggIsKeyColumnName];
bool isKeyCol = (bool)keyCol.Value;
if (!isKeyCol)
{
DesignTimeComponent.SetUsageType(input.ID, vInput, icol.LineageID, DTSUsageType.UT_IGNORED);
icol.DTSInputColumn = null;
}
}
validForInput = true;
}
return(validForInput);
}
public LookupErrorAggregationProperties(IDTSCustomProperty hashAlgorithm, IDTSCustomProperty nullColumn)
{
//this.lookupSourceDescription = sourceName;
//this.lookupSourceID = sourceID;
this.hashAlgorithmType = hashAlgorithm;
this.nullColumn = nullColumn;
}
protected override void DTSOutputColumnSetProcessing()
{
base.DTSOutputColumnSetProcessing();
saveOptionsProperty = null;
serializeLineage = null;
serializeDataType = null;
if (DTSOutputColumn != null)
{
foreach (IDTSCustomProperty prop in DTSOutputColumn.CustomPropertyCollection)
{
if (prop.Name == Resources.XmlSaveOptionPropertyName)
{
saveOptionsProperty = prop;
}
else if (prop.Name == Resources.XmlSerializeLineageName)
{
serializeLineage = prop;
}
else if (prop.Name == Resources.XmlSerializeDataTypeName)
{
serializeDataType = prop;
}
}
}
}
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;
}
protected override void DTSOutputColumnSetProcessing()
{
base.DTSOutputColumnSetProcessing();
if (DTSOutputColumn != null)
{
hashTypeProperty = DTSOutputColumn.CustomPropertyCollection[Resources.HashAlgorithmPropertyName];
hashInputColumns = DTSOutputColumn.CustomPropertyCollection[Resources.HashColumnHashInputColumnsPropertyName];
hashImplementationType = DTSOutputColumn.CustomPropertyCollection[Resources.HashColumnHashImplementationTypePropertyName];
hashFieldSeparator = DTSOutputColumn.CustomPropertyCollection[Resources.HashColumnHashFieldSeparatorPropertyName];
nullReplacementValue = DTSOutputColumn.CustomPropertyCollection[Resources.HashColumnNullReplacementValue];
stringTrimming = DTSOutputColumn.CustomPropertyCollection[Resources.HashColumnStringTrimmingPropertyName];
_inputColumns = null;
}
else
{
hashTypeProperty = null;
hashInputColumns = null;
hashFieldSeparator = null;
nullReplacementValue = null;
stringTrimming = null;
_inputColumns = new InputColumns();
}
UpdateReadOnlyProperty("HashInputColumns", !InputIsAttached);
UpdateReadOnlyProperties();
}
/// <summary>
/// Procerss the associated IDTSOutputColumn and retrieves the MetadataColumn specific Custom Properties
/// </summary>
protected override void DTSOutputColumnSetProcessing()
{
base.DTSOutputColumnSetProcessing();
if (DTSOutputColumn != null)
{
initialValueProperty = DTSOutputColumn.CustomPropertyCollection[Resources.RowNumberInitialValuePropertyName];
incrementByProperty = DTSOutputColumn.CustomPropertyCollection[Resources.RowNumberIncrementByPropertyName];
//Dynamically update the ReadOnly attribute of the RowNumberColumn depending on the DataType property
var value = this.DataType;
PropertyDescriptor descriptor = TypeDescriptor.GetProperties(this.GetType())["Precision"];
ReadOnlyAttribute attrib = (ReadOnlyAttribute)descriptor.Attributes[typeof(ReadOnlyAttribute)];
FieldInfo isReadOnly = attrib.GetType().GetField("isReadOnly", BindingFlags.NonPublic | BindingFlags.Instance);
isReadOnly.SetValue(attrib, value == RowNumberDataTypeType.DT_NUMERIC ? false : true);
descriptor = TypeDescriptor.GetProperties(this.GetType())["Scale"];
attrib = (ReadOnlyAttribute)descriptor.Attributes[typeof(ReadOnlyAttribute)];
isReadOnly = attrib.GetType().GetField("isReadOnly", BindingFlags.NonPublic | BindingFlags.Instance);
isReadOnly.SetValue(attrib, value == RowNumberDataTypeType.DT_NUMERIC ? false : true);
}
else
{
initialValueProperty = null;
incrementByProperty = null;
}
}
private void ReplaceCustomPropertiesLineageIDs(IDTSCustomPropertyCollectionXX customProps)
{
IDTSCustomPropertyXX friendlyExpression = null;
IDTSCustomPropertyXX expression = null;
foreach (IDTSCustomPropertyXX prop in customProps)
{
if (prop.Name == "FriendlyExpression")
{
friendlyExpression = prop;
}
else if (prop.Name == "Expression")
{
prop.Value = ReplaceValueLineageIDs(prop.Value);
expression = prop;
}
else if (prop.ContainsID)
{
if (prop.TypeConverter == "LCMappingType")
{
//this property shouldn't have ContainsID=true set, so we have to workaround this bug: https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=338029
}
else
{
prop.Value = ReplaceValueLineageIDs(prop.Value);
}
}
}
if (friendlyExpression != null && expression != null)
{
friendlyExpression.Value = expression.Value;
}
}
/// <summary>
/// Procerss the associated IDTSOutputColumn and retrieves the MetadataColumn specific Custom Properties
/// </summary>
protected override void DTSOutputColumnSetProcessing()
{
base.DTSOutputColumnSetProcessing();
if (DTSOutputColumn != null)
{
inputDataColumnProperty = DTSOutputColumn.CustomPropertyCollection[Resources.RegExExtractionInputColumnLinageIdPropertyName];
regularExpression = DTSOutputColumn.CustomPropertyCollection[Resources.RegExExtractionRegularExpressionName];
groupCaptureNumber = DTSOutputColumn.CustomPropertyCollection[Resources.RegExExtractionGroupCaptureNumberPropertyName];
matchNumber = DTSOutputColumn.CustomPropertyCollection[Resources.RegExExtractionMatchNumberPropertyName];
regexOutput = DTSOutputColumn.CustomPropertyCollection[Resources.RegExExtractionRegexOutput];
regexOutputType = DTSOutputColumn.CustomPropertyCollection[Resources.RegExExtractionRegexOutputType];
regexOptions = DTSOutputColumn.CustomPropertyCollection[Resources.RegExExtractionRegexOptions];
}
else
{
inputDataColumnProperty = null;
regularExpression = null;
matchNumber = null;
groupCaptureNumber = null;
regexOutput = null;
regexOutputType = null;
regexOptions = null;
}
UpdateReadOnlyProperties();
}
public HistoryLookupProperties(IDTSCustomProperty noMatchBehavior, IDTSCustomProperty cacheType, IDTSCustomProperty noMatchCacheSize, IDTSCustomProperty defaultCacheSize, IDTSCustomProperty keyHashAlgorithm)
{
this._noMatchBehavior = noMatchBehavior;
this._caheType = cacheType;
this._noMatchCacheSize = noMatchCacheSize;
this._defaultCacheSize = defaultCacheSize;
this._keyHashAlgorithm = keyHashAlgorithm;
}
public void SelectLookupInputColumn(System.Windows.Forms.CheckedListBox clbInputColumns, System.Windows.Forms.ListBox lbSelectedItems, int checkedIndex, System.Windows.Forms.CheckState state, int nullColumnLineageId)
{
FormInputColumn icol = clbInputColumns.Items[checkedIndex] as FormInputColumn;
IDTSInput input = ComponentMetadata.InputCollection[0];
if (icol != null)
{
if (state == CheckState.Checked)
{
DesignTimeComponent.SetUsageType(input.ID, VirtualInput, icol.LineageID, DTSUsageType.UT_READONLY);
IDTSInputColumn inputCol = input.InputColumnCollection.GetInputColumnByLineageID(icol.LineageID);
IDTSCustomProperty keyCol = inputCol.CustomPropertyCollection[Resources.LookupErrorAggIsKeyColumnName];
keyCol.Value = true;
icol.DTSInputColumn = inputCol;
if (lbSelectedItems != null && !lbSelectedItems.Items.Contains(icol))
{
int sortOrder = 0;
if (lbSelectedItems.Items.Count > 0)
{
FormInputColumn lastCol = lbSelectedItems.Items[lbSelectedItems.Items.Count - 1] as FormInputColumn;
sortOrder = lastCol.SortOrder;
}
icol.SortOrder = sortOrder + 1;
lbSelectedItems.Items.Add(icol);
}
}
else
{
bool isInput = false;
if (icol.DTSInputColumn != null)
{
IDTSCustomProperty prop = icol.DTSInputColumn.CustomPropertyCollection[Resources.LookupErrorAggIsNullColumnName];
isInput = (bool)prop.Value;
IDTSCustomProperty keyCol = icol.DTSInputColumn.CustomPropertyCollection[Resources.LookupErrorAggIsKeyColumnName];
keyCol.Value = false;
}
if (!isInput)
{
DesignTimeComponent.SetUsageType(input.ID, VirtualInput, icol.LineageID, DTSUsageType.UT_IGNORED);
icol.DTSInputColumn = null;
}
if (lbSelectedItems != null && lbSelectedItems.Items.Contains(icol))
{
lbSelectedItems.Items.Remove(icol);
}
}
}
}
private void ColumnsToXmlTransformationForm_Load(object sender, EventArgs e)
{
UIHelper.FormInitialize(clbInputColumns, lbSelectedItems, trvOutputColumns, LookupErrorColumn.CreateInstance, o_PropertyChanged);
IDTSCustomProperty hashAlgorithm = UIHelper.ComponentMetadata.CustomPropertyCollection[LookupErrorAggregationTransformation.GetPropertyName(LookupErrorAggregationTransformation.CustomProperties.HashAlgorithm)];
IDTSCustomProperty nullColumn = UIHelper.ComponentMetadata.CustomPropertyCollection[LookupErrorAggregationTransformation.GetPropertyName(LookupErrorAggregationTransformation.CustomProperties.NullColumn)];
properties = new LookupErrorAggregationProperties(hashAlgorithm, nullColumn);
propComponent.SelectedObject = properties;
}
internal static void AddComponentCustomProperties(IDTSComponentMetaData100 componentMetaData)
{
IDTSCustomProperty parallel = null;
parallel = componentMetaData.CustomPropertyCollection.New();
parallel.Name = Resources.HashTransformationParallelProcessingPropertyName;
parallel.Description = "Specifies whether Hash processing for multiple output columsn should run in multiple threads.";
parallel.ContainsID = false;
parallel.EncryptionRequired = false;
parallel.TypeConverter = typeof(HashColumnsTransformation.ParallelProcessing).AssemblyQualifiedName;
parallel.Value = HashColumnsTransformation.ParallelProcessing.Off;
}
protected override void DTSOutputColumnSetProcessing()
{
base.DTSOutputColumnSetProcessing();
saveOptionsProperty = null;
serializeLineage = null;
serializeDataType = null;
if (DTSOutputColumn != null)
{
foreach (IDTSCustomProperty prop in DTSOutputColumn.CustomPropertyCollection)
{
if (prop.Name == Resources.XmlSaveOptionPropertyName)
{
saveOptionsProperty = prop;
}
else if (prop.Name == Resources.XmlSerializeLineageName)
{
serializeLineage = prop;
}
else if (prop.Name == Resources.XmlSerializeDataTypeName)
{
serializeDataType = prop;
}
else if (prop.Name == Resources.XmlInputColumnsPropertyname)
{
xmlInputColumns = prop;
}
else if (prop.Name == Resources.XmlSourceIdPropertyName)
{
sourceID = prop;
}
else if (prop.Name == Resources.XmlSourceNamePropertyName)
{
sourceName = prop;
}
}
}
var value = this.DataType;
UpdateReadOnlyProperty("DataLength", value == XmlDataType.DT_WSTR ? false : true);
}
///// <summary>
///// Get Maximum Input Sort Order
///// </summary>
///// <returns>Maximu Sort Order of Input Columns</returns>
//public static int GetMaxInputColumnsSortOrder(IDTSInput input)
//{
// int maxOrder = 0;
// foreach (IDTSInputColumn col in input.InputColumnCollection)
// {
// foreach (IDTSCustomProperty prop in col.CustomPropertyCollection)
// {
// if (prop.Name == Resources.InputSortOrderPropertyName)
// {
// int order = prop.Value != null ? (int)prop.Value : 0;
// if (order > maxOrder)
// maxOrder = order;
// break;
// }
// }
// }
// return maxOrder;
//}
public static void UpdateSortOrder(IDTSInput input, int inputColumnID, int val)
{
IDTSInputColumn currentColumn = input.InputColumnCollection.GetObjectByID(inputColumnID);
IDTSCustomProperty currentProp = currentColumn.CustomPropertyCollection[Resources.InputSortOrderPropertyName];
int currentValue = (int)currentProp.Value;
foreach (IDTSInputColumn col in input.InputColumnCollection)
{
if (col.ID == inputColumnID)
{
continue;
}
IDTSCustomProperty prop = col.CustomPropertyCollection[Resources.InputSortOrderPropertyName];
if (prop != null && (int)prop.Value == val)
{
prop.Value = currentValue;
//UpdateSortOrder(inputID, col.ID, val + 1);
}
}
}
private void btnOk_Click(object sender, EventArgs e)
{
if (properties.NullColumnLineageId != -1)
{
IDTSInput input = UIHelper.GetInput(0);
IDTSVirtualInput vInput = UIHelper.GetVirtualInput(0);
IDTSVirtualInputColumn vCol = vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(properties.NullColumnLineageId);;
IDTSInputColumn col;
if (vCol.UsageType != DTSUsageType.UT_IGNORED)
{
col = input.InputColumnCollection.GetInputColumnByLineageID(vCol.LineageID);
}
else
{
col = UIHelper.DesignTimeComponent.SetUsageType(input.ID, vInput, properties.NullColumnLineageId, DTSUsageType.UT_READONLY);
}
IDTSCustomProperty nullCol = col.CustomPropertyCollection[Resources.LookupErrorAggIsNullColumnName];
nullCol.Value = true;
}
this.Close();
}
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 HashColumnsTransformationForm_Load(object sender, EventArgs e)
{
IDTSInput input = UIHelper.GetInput(0);
if (input == null || input.IsAttached == false)
{
inputIsAttached = false;
}
HashOutputColumn.InputIsAttached = inputIsAttached;
UIHelper.FormInitialize(null, null, trvOutputColumns, HashOutputColumn.CreateInstance, o_PropertyChanged);
IDTSCustomProperty parallelProcessing = UIHelper.ComponentMetadata.CustomPropertyCollection[Resources.HashTransformationParallelProcessingPropertyName];
HashComponentProperties properties = new HashComponentProperties(parallelProcessing);
propComponent.SelectedObject = properties;
if (inputIsAttached == false)
{
MessageBox.Show("Hash Columns Transformation has no input attached. Columns selection will be disabled", "Input not attached", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);
}
}
protected override void DTSOutputColumnSetProcessing()
{
base.DTSOutputColumnSetProcessing();
prop = DTSOutputColumn.CustomPropertyCollection[Resources.LookupErrorAggLookupSourceDescriptionName];
}
public HashComponentProperties(IDTSCustomProperty parallelProcessing)
{
this.parallelProcessing = parallelProcessing;
}
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;
}
}
public static void SetHashColumnProperties(HashColumnsTransformation.HashOuputColumnProperties propsToSet, IDTSOutputColumn hashColumn, HashColumnsTransformation.HashType hashType, HashColumnsTransformation.HashImplementationType implementation, HashColumnsTransformation.HashOutputDataType dataType, int locale)
{
List <int> propsToRemove = new List <int>();
foreach (IDTSCustomProperty prop in hashColumn.CustomPropertyCollection)
{
if (
(prop.Name == Resources.HashAlgorithmPropertyName && (propsToSet & HashColumnsTransformation.HashOuputColumnProperties.HashAlgorithm) == HashColumnsTransformation.HashOuputColumnProperties.HashAlgorithm) ||
(prop.Name == Resources.HashColumnHashInputColumnsPropertyName && (propsToSet & HashColumnsTransformation.HashOuputColumnProperties.HashColumns) == HashColumnsTransformation.HashOuputColumnProperties.HashColumns) ||
(prop.Name == Resources.HashColumnHashFieldSeparatorPropertyName && (propsToSet & HashColumnsTransformation.HashOuputColumnProperties.HashFieldSeparator) == HashColumnsTransformation.HashOuputColumnProperties.HashFieldSeparator) ||
(prop.Name == Resources.HashColumnHashImplementationTypePropertyName && (propsToSet & HashColumnsTransformation.HashOuputColumnProperties.HashImplementationType) == HashColumnsTransformation.HashOuputColumnProperties.HashImplementationType) ||
(prop.Name == Resources.HashColumnNullReplacementValue && (propsToSet & HashColumnsTransformation.HashOuputColumnProperties.HashNullReplacement) == HashColumnsTransformation.HashOuputColumnProperties.HashNullReplacement) ||
(prop.Name == Resources.HashColumnStringTrimmingPropertyName && (propsToSet & HashColumnsTransformation.HashOuputColumnProperties.TrimStrings) == HashColumnsTransformation.HashOuputColumnProperties.TrimStrings)
)
{
propsToRemove.Add(prop.ID);
}
}
foreach (int id in propsToRemove)
{
hashColumn.CustomPropertyCollection.RemoveObjectByID(id);
}
if ((propsToSet & HashColumnsTransformation.HashOuputColumnProperties.HashAlgorithm) == HashColumnsTransformation.HashOuputColumnProperties.HashAlgorithm)
{
//as the Output columns are Hash Columns, add HashType property with default MD5
IDTSCustomProperty hashAlgorithm = hashColumn.CustomPropertyCollection.New();
hashAlgorithm.Description = Resources.HashTypePropertyDescription;
hashAlgorithm.Name = Resources.HashAlgorithmPropertyName;
hashAlgorithm.ContainsID = false;
hashAlgorithm.EncryptionRequired = false;
hashAlgorithm.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE;
hashAlgorithm.TypeConverter = typeof(HashColumnsTransformation.HashType).AssemblyQualifiedName;
hashAlgorithm.Value = hashType;
}
if ((propsToSet & HashColumnsTransformation.HashOuputColumnProperties.HashImplementationType) == HashColumnsTransformation.HashOuputColumnProperties.HashImplementationType)
{
IDTSCustomProperty hashImplementation = hashColumn.CustomPropertyCollection.New();
hashImplementation.Description = "Implementation version of the Hash Type";
hashImplementation.Name = Resources.HashColumnHashImplementationTypePropertyName; //"HashImplementationType";
hashImplementation.ContainsID = false;
hashImplementation.EncryptionRequired = false;
hashImplementation.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE;
hashImplementation.TypeConverter = typeof(HashColumnsTransformation.HashImplementationType).AssemblyQualifiedName;
hashImplementation.Value = implementation;
}
if ((propsToSet & HashColumnsTransformation.HashOuputColumnProperties.HashColumns) == HashColumnsTransformation.HashOuputColumnProperties.HashColumns)
{
IDTSCustomProperty hashColumns = hashColumn.CustomPropertyCollection.New();
hashColumns.Description = "List of input columns to build HASH";
hashColumns.Name = Resources.HashColumnHashInputColumnsPropertyName; // "HashInputColumns";
hashColumns.ContainsID = true;
hashColumns.EncryptionRequired = false;
hashColumns.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE;
hashColumns.Value = string.Empty;
}
if ((propsToSet & HashColumnsTransformation.HashOuputColumnProperties.HashFieldSeparator) == HashColumnsTransformation.HashOuputColumnProperties.HashFieldSeparator)
{
IDTSCustomProperty hashFieldSeparator = hashColumn.CustomPropertyCollection.New();
hashFieldSeparator.Description = "Specifies the field separator for Unicode String Implementations";
hashFieldSeparator.Name = Resources.HashColumnHashFieldSeparatorPropertyName;
hashFieldSeparator.ContainsID = false;
hashFieldSeparator.EncryptionRequired = false;
hashFieldSeparator.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE;
hashFieldSeparator.Value = DefaultFieldtSeparator;
}
if ((propsToSet & HashColumnsTransformation.HashOuputColumnProperties.HashNullReplacement) == HashColumnsTransformation.HashOuputColumnProperties.HashNullReplacement)
{
IDTSCustomProperty hashFieldSeparator = hashColumn.CustomPropertyCollection.New();
hashFieldSeparator.Description = "Specifies the NULL replacement value for non NULL safe Unicode String Implementations";
hashFieldSeparator.Name = Resources.HashColumnNullReplacementValue;
hashFieldSeparator.ContainsID = false;
hashFieldSeparator.EncryptionRequired = false;
hashFieldSeparator.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE;
hashFieldSeparator.Value = DefaultNullReplacement;
}
if ((propsToSet & HashColumnsTransformation.HashOuputColumnProperties.TrimStrings) == HashColumnsTransformation.HashOuputColumnProperties.TrimStrings)
{
IDTSCustomProperty stringTrimming = hashColumn.CustomPropertyCollection.New();
stringTrimming.Description = "Specifies whether and how string values should be trimmed";
stringTrimming.Name = Resources.HashColumnStringTrimmingPropertyName;
stringTrimming.ContainsID = false;
stringTrimming.EncryptionRequired = false;
stringTrimming.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE;
stringTrimming.TypeConverter = typeof(HashColumnsTransformation.StringTrimming).AssemblyQualifiedName;
stringTrimming.Value = HashColumnsTransformation.StringTrimming.None;
}
if (
((propsToSet & HashColumnsTransformation.HashOuputColumnProperties.HashAlgorithm) == HashColumnsTransformation.HashOuputColumnProperties.HashAlgorithm)
)
{
//Set data type for the column accordingly with the hashType - default for MD4
SetHashColumnDataType(hashType, hashColumn, locale);
}
}
/// <summary>
/// Connects to SharePoint and gets any columns on the target
/// </summary>
/// <param name="output"></param>
/// <param name="p"></param>
private void CreateExternalMetaDataColumns(
IDTSOutput output, string sharepointUrl, string listName, string viewName,
Dictionary <string, string> existingColumnData)
{
// No need to load if the Url is bad.
if ((sharepointUrl == null) || (sharepointUrl.Length == 0))
{
return;
}
// Need a list to continue
if ((listName == null) || (listName.Length == 0))
{
return;
}
// If the list has changed, then we do not want any of the exisiting column data to
// influence it (provides a way to actually reset the names if needed)
if (output.Description != listName)
{
existingColumnData.Clear();
output.Description = listName;
}
try
{
List <SharePointUtility.DataObject.ColumnData> accessibleColumns =
GetAccessibleSharePointColumns(sharepointUrl, listName, viewName);
foreach (var column in accessibleColumns)
{
// Setup the primary column details from the List
IDTSExternalMetadataColumn dtsColumnMeta = output.ExternalMetadataColumnCollection.New();
if (existingColumnData.ContainsKey(column.Name))
{
dtsColumnMeta.Name = existingColumnData[column.Name];
}
else
{
dtsColumnMeta.Name = column.FriendlyName;
}
dtsColumnMeta.Description = column.DisplayName;
dtsColumnMeta.Length = 0;
dtsColumnMeta.Precision = 0;
dtsColumnMeta.Scale = 0;
if ("Boolean|AllDayEvent|Attachments|CrossProjectLink|Recurrence".Contains(column.SharePointType))
{
dtsColumnMeta.DataType = DataType.DT_BOOL;
}
else if (column.SharePointType == "DateTime")
{
dtsColumnMeta.DataType = DataType.DT_DBTIMESTAMP;
}
else if ("Number|Currency".Contains(column.SharePointType))
{
// Max = 100,000,000,000.00000
dtsColumnMeta.DataType = DataType.DT_R8;
}
else if ("Counter|Integer".Contains(column.SharePointType))
{
dtsColumnMeta.DataType = DataType.DT_I4;
}
else if ("Guid".Contains(column.SharePointType))
{
dtsColumnMeta.DataType = DataType.DT_GUID;
}
else
{
if (column.MaxLength == -1)
{
dtsColumnMeta.DataType = DataType.DT_NTEXT;
dtsColumnMeta.Length = 0;
}
else
{
dtsColumnMeta.DataType = DataType.DT_WSTR;
dtsColumnMeta.Length = column.MaxLength;
}
}
IDTSCustomProperty fieldNameMeta = dtsColumnMeta.CustomPropertyCollection.New();
fieldNameMeta.Name = "Id";
fieldNameMeta.Description = "SharePoint ID";
fieldNameMeta.Value = column.Name;
// Create default output columns for all of the fields returned and link to the original columns
IDTSOutputColumn dtsColumn = output.OutputColumnCollection.New();
dtsColumn.Name = dtsColumnMeta.Name;
dtsColumn.Description = dtsColumnMeta.Description;
dtsColumn.ExternalMetadataColumnID = dtsColumnMeta.ID;
IDTSCustomProperty fieldName = dtsColumn.CustomPropertyCollection.New();
fieldName.Name = fieldNameMeta.Name;
fieldName.Description = fieldNameMeta.Description;
fieldName.Value = fieldNameMeta.Value;
dtsColumn.SetDataTypeProperties(
dtsColumnMeta.DataType, dtsColumnMeta.Length, dtsColumnMeta.Precision, dtsColumnMeta.Scale, 0);
}
}
catch (ApplicationException)
{
// Exception happened, so clear the columns, which will invalidate this object.
output.ExternalMetadataColumnCollection.RemoveAll();
}
}
/// <summary>
/// Upgrades the component from earlier versions
/// </summary>
/// <param name="pipelineVersion"></param>
public override void PerformUpgrade(int pipelineVersion)
{
DtsPipelineComponentAttribute componentAttribute = (DtsPipelineComponentAttribute)Attribute.GetCustomAttribute(this.GetType(), typeof(DtsPipelineComponentAttribute), false);
int currentVersion = componentAttribute.CurrentVersion;
// Get the attributes for the SSIS Package
int metadataVersion = ComponentMetaData.Version;
if (currentVersion < ComponentMetaData.Version)
{
throw new Exception(Properties.Resources.ErrorWrongRuntimeVersion);
}
else if (currentVersion > ComponentMetaData.Version)
{
IDTSInput input = ComponentMetaData.InputCollection[0];
List <int> propsToRemove = new List <int>();
List <KeyValuePair <int, int> > colLienagesSort = new List <KeyValuePair <int, int> >(input.InputColumnCollection.Count);
string sourceID = string.Empty;
string sourceName = string.Empty;
try
{
IDTSCustomProperty prop = ComponentMetaData.CustomPropertyCollection[Resources.ColumnsToXmlSourceNameProperty];
if (prop != null)
{
sourceName = prop.Value.ToString();
ComponentMetaData.CustomPropertyCollection.RemoveObjectByID(prop.ID);
}
}
catch { }
try
{
IDTSCustomProperty prop = ComponentMetaData.CustomPropertyCollection[Resources.ColumnsToXmlSourceIDProperty];
if (prop != null)
{
sourceID = prop.Value.ToString();
ComponentMetaData.CustomPropertyCollection.RemoveObjectByID(prop.ID);
}
}
catch
{ }
foreach (IDTSInputColumn col in input.InputColumnCollection)
{
int sortOrder = int.MaxValue;
IDTSCustomProperty prop = null;
propsToRemove = new List <int>();
foreach (IDTSCustomProperty p in col.CustomPropertyCollection)
{
if (p.Name == Resources.InputSortOrderPropertyName)
{
sortOrder = (int)p.Value;
propsToRemove.Add(p.ID);
break;
}
}
colLienagesSort.Add(new KeyValuePair <int, int>(col.LineageID, sortOrder));
foreach (int id in propsToRemove)
{
col.CustomPropertyCollection.RemoveObjectByID(id);
}
}
colLienagesSort.Sort((a, b) => a.Value.CompareTo(b.Value));
IDTSOutput output = ComponentMetaData.OutputCollection[0];
foreach (IDTSOutputColumn col in output.OutputColumnCollection)
{
SetPropertyType setProp = SetPropertyType.All;
bool additionalPropertyExists = false;
bool setDataType = !(col.DataType == DataType.DT_NTEXT || (col.DataType == DataType.DT_WSTR && col.Length <= 4000));
foreach (IDTSCustomProperty prop in col.CustomPropertyCollection)
{
if (prop.Name == Resources.XmlSaveOptionPropertyName)
{
setProp ^= SetPropertyType.XmlSaveOptions;
}
else if (prop.Name == Resources.XmlSerializeLineageName)
{
setProp ^= SetPropertyType.XmlSerializeLineage;
}
else if (prop.Name == Resources.XmlSerializeDataTypeName)
{
setProp ^= SetPropertyType.XmlSerializeDataType;
}
else if (prop.Name == Resources.XmlInputColumnsPropertyname)
{
setProp ^= SetPropertyType.XmlInputColumns;
}
else if (prop.Name == Resources.XmlSourceIdPropertyName)
{
setProp ^= SetPropertyType.XMlSourceID;
}
else if (prop.Name == Resources.XmlSourceNamePropertyName)
{
setProp ^= SetPropertyType.XmlSourceName;
}
else
{
additionalPropertyExists = true;
}
}
if (setProp != SetPropertyType.None || additionalPropertyExists || setDataType)
{
SetXmlColumnProperties(col, setProp, setDataType);
}
if ((setProp & SetPropertyType.XmlInputColumns) == SetPropertyType.XmlInputColumns && colLienagesSort.Count > 0)
{
string lineages = InputColumns.BuildInputLineagesString(colLienagesSort.ConvertAll <int>(kvp => kvp.Key));
col.CustomPropertyCollection[Resources.XmlInputColumnsPropertyname].Value = lineages;
}
if ((setProp & SetPropertyType.XMlSourceID) == SetPropertyType.XMlSourceID)
{
col.CustomPropertyCollection[Resources.XmlSourceIdPropertyName].Value = sourceID;
}
if ((setProp & SetPropertyType.XmlSourceName) == SetPropertyType.XmlSourceName)
{
col.CustomPropertyCollection[Resources.XmlSourceNamePropertyName].Value = sourceName;
}
}
ComponentMetaData.Version = currentVersion;
}
}
/// <summary>
/// Sets XmlColumn properties for Output Column
/// </summary>
/// <param name="xmlColumn">Output Column to set XmlColumn Properties</param>
internal static void SetXmlColumnProperties(IDTSOutputColumn xmlColumn, SetPropertyType propType, bool setDataType)
{
List <int> IdsToRemove = new List <int>();
foreach (IDTSCustomProperty prop in xmlColumn.CustomPropertyCollection)
{
if (
(prop.Name == Resources.XmlSaveOptionPropertyName && (propType & SetPropertyType.XmlSaveOptions) == SetPropertyType.XmlSaveOptions) ||
(prop.Name == Resources.XmlSerializeLineageName && (propType & SetPropertyType.XmlSerializeLineage) == SetPropertyType.XmlSerializeLineage) ||
(prop.Name == Resources.XmlSerializeDataTypeName && (propType & SetPropertyType.XmlSerializeDataType) == SetPropertyType.XmlSerializeDataType) ||
(prop.Name == Resources.XmlInputColumnsPropertyname && (propType & SetPropertyType.XmlInputColumns) == SetPropertyType.XmlInputColumns) ||
(prop.Name == Resources.XmlSourceIdPropertyName && (propType & SetPropertyType.XMlSourceID) == SetPropertyType.XMlSourceID) ||
(prop.Name == Resources.XmlSourceNamePropertyName && (propType & SetPropertyType.XmlSourceName) == SetPropertyType.XmlSourceName)
)
{
IdsToRemove.Add(prop.ID);
}
}
foreach (int id in IdsToRemove)
{
xmlColumn.CustomPropertyCollection.RemoveObjectByID(id);
}
//Add Save Options
if ((propType & SetPropertyType.XmlSaveOptions) == SetPropertyType.XmlSaveOptions)
{
IDTSCustomProperty xmlSaveOption = xmlColumn.CustomPropertyCollection.New();
xmlSaveOption.Description = Resources.XmlSaveOptionsDescriptions;
xmlSaveOption.Name = Resources.XmlSaveOptionPropertyName;
xmlSaveOption.ContainsID = false;
xmlSaveOption.EncryptionRequired = false;
xmlSaveOption.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE;
xmlSaveOption.TypeConverter = typeof(SaveOptions).AssemblyQualifiedName;
xmlSaveOption.Value = SaveOptions.None;
}
//Lineage & Colun IDs information
if ((propType & SetPropertyType.XmlSerializeLineage) == SetPropertyType.XmlSerializeLineage)
{
IDTSCustomProperty lineage = xmlColumn.CustomPropertyCollection.New();
lineage.Description = Resources.XmlSerializeLineageDescription;
lineage.Name = Resources.XmlSerializeLineageName;
lineage.ContainsID = false;
lineage.EncryptionRequired = false;
lineage.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE;
lineage.TypeConverter = typeof(bool).AssemblyQualifiedName;
lineage.Value = false;
}
//Data Type Information
if ((propType & SetPropertyType.XmlSerializeDataType) == SetPropertyType.XmlSerializeDataType)
{
IDTSCustomProperty storeDataType = xmlColumn.CustomPropertyCollection.New();
storeDataType.Description = Resources.XmlStoreDataTypeDescription;
storeDataType.Name = Resources.XmlSerializeDataTypeName;
storeDataType.ContainsID = false;
storeDataType.EncryptionRequired = false;
storeDataType.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE;
storeDataType.TypeConverter = typeof(bool).AssemblyQualifiedName;
storeDataType.Value = false;
}
if ((propType & SetPropertyType.XmlInputColumns) == SetPropertyType.XmlInputColumns)
{
IDTSCustomProperty storeDataType = xmlColumn.CustomPropertyCollection.New();
storeDataType.Name = Resources.XmlInputColumnsPropertyname;
storeDataType.Description = "Specifies the columns to be used to build Xml";
storeDataType.ContainsID = true;
storeDataType.EncryptionRequired = false;
storeDataType.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE;
storeDataType.Value = string.Empty;
}
if ((propType & SetPropertyType.XMlSourceID) == SetPropertyType.XMlSourceID)
{
IDTSCustomProperty storeDataType = xmlColumn.CustomPropertyCollection.New();
storeDataType.Name = Resources.XmlSourceIdPropertyName;
storeDataType.Description = "Specifies optional descriptive ID of the culumns source";
storeDataType.ContainsID = false;
storeDataType.EncryptionRequired = false;
storeDataType.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE;
storeDataType.Value = string.Empty;
}
if ((propType & SetPropertyType.XmlSourceName) == SetPropertyType.XmlSourceName)
{
IDTSCustomProperty storeDataType = xmlColumn.CustomPropertyCollection.New();
storeDataType.Name = Resources.XmlSourceNamePropertyName;
storeDataType.Description = "Specifies optional descriptive Name of the columns source";
storeDataType.ContainsID = false;
storeDataType.EncryptionRequired = false;
storeDataType.ExpressionType = DTSCustomPropertyExpressionType.CPET_NONE;
storeDataType.Value = string.Empty;
}
//Column Data Type
if (setDataType)
{
xmlColumn.SetDataTypeProperties(DataType.DT_NTEXT, 0, 0, 0, 0);
}
}
