void choosecolumnspage_GetSelectedInputOutputColumns(object sender, SelectedInputOutputColumnsArgs args)
{
this.ClearErrors();
try
{
IDTSInput100 input = this.ComponentMetadata.InputCollection[0];
IDTSInputColumnCollection100 inputColumnCollection = input.InputColumnCollection;
foreach (IDTSInputColumn100 inputColumn in inputColumnCollection)
{
SelectedInputOutputColumns element = new SelectedInputOutputColumns();
IDTSVirtualInputColumn100 virtualInputColumn =
this.GetVirtualInputColumn(inputColumn);
element.VirtualInputColumn = new DataFlowElement(virtualInputColumn.Name, virtualInputColumn);
element.InputColumn = new DataFlowElement(inputColumn.Name, inputColumn);
args.SelectedColumns.Add(element);
}
}
catch (Exception ex)
{
this.ReportErrors(ex);
}
}
void choosecolumnspage_DeleteInputOutputColumns(object sender, SetInputOutputColumnsArgs args)
{
Debug.Assert(args.VirtualColumn != null, "Invalid arguments passed from the UI");
this.ClearErrors();
try
{
IDTSInput100 input = this.ComponentMetadata.InputCollection[0];
IDTSVirtualInputColumn100 virtualInputColumn = args.VirtualColumn.Tag as IDTSVirtualInputColumn100;
if (virtualInputColumn == null)
{
throw new InvalidOperationException("The UI is in an inconsistent state: Passed argument is not valid.");
}
int lineageID = virtualInputColumn.LineageID;
this.DesigntimeComponent.SetUsageType(input.ID, this.VirtualInput, lineageID, DTSUsageType.UT_IGNORED);
}
catch (Exception ex)
{
this.ReportErrors(ex);
args.CancelAction = true;
}
}
/// <summary>
/// Helper function to load DataGridView, one row per input column. Column
/// UsageType dictates column selection state.
/// </summary>
private void SetInputVirtualInputColumns()
{
_virtualInput = _input.GetVirtualInput();
IDTSVirtualInputColumnCollection100 virtualInputColumnCollection = _virtualInput.VirtualInputColumnCollection;
IDTSInputColumnCollection100 inputColumns = _input.InputColumnCollection;
int columnCount = virtualInputColumnCollection.Count;
for (int i = 0; i < columnCount; i++)
{
IDTSVirtualInputColumn100 virtualColumn = virtualInputColumnCollection[i];
int rowIndex;
if (virtualColumn.UsageType == DTSUsageType.UT_READONLY || virtualColumn.UsageType == DTSUsageType.UT_READWRITE)
{
rowIndex = this.dgColumns.Rows.Add(new object[] { CheckState.Checked, " " + virtualColumn.Name });
}
else
{
rowIndex = this.dgColumns.Rows.Add(new object[] { CheckState.Unchecked, " " + virtualColumn.Name });
}
this.dgColumns.Rows[rowIndex].Tag = i;
DataGridViewCheckBoxCell cell = (DataGridViewCheckBoxCell)this.dgColumns.Rows[rowIndex].Cells[0];
cell.ThreeState = false;
}
}
/// <summary>
/// Hanlde column selection events from our DataGridView. Columns are selected or deselected
/// by setting the UsageType. Ensure changes are made to the through the managed wrapper (design-time interface)
/// so that changes are persisted or cancelled based on form result.
/// </summary>
/// <param name="sender">DataGridView</param>
/// <param name="e">DataGridViewCellEventArgs</param>
private void dgColumns_CellContentClick(object sender, DataGridViewCellEventArgs e)
{
if (e.ColumnIndex == 0 && e.RowIndex >= 0)
{
// Get current value and flip boolean to get new value
bool newValue = !Convert.ToBoolean(dgColumns.CurrentCell.Value);
// Get the virtual column to work with
IDTSVirtualInputColumn100 virtualColumn = _virtualInput.VirtualInputColumnCollection[e.RowIndex];
try
{
// Set the column UsageType to indicate the column is selected or not
if (newValue)
{
_designTimeComponent.SetUsageType(_input.ID, _virtualInput, virtualColumn.LineageID, DTSUsageType.UT_READWRITE);
}
else
{
_designTimeComponent.SetUsageType(_input.ID, _virtualInput, virtualColumn.LineageID, DTSUsageType.UT_IGNORED);
}
}
catch (Exception ex)
{
// Catch any error from base class SetUsageType here.
// Display simple error message from exception
MessageBox.Show(ex.Message, "Invalid Column", MessageBoxButtons.OK, MessageBoxIcon.Error);
// Rollback UI selection
dgColumns.CancelEdit();
}
}
}
protected virtual void SetInputUsageType(IDTSVirtualInput100 vi, IDTSVirtualInputColumn100 vcol, DTSUsageType usageType, bool forceOverwrite)
{
if (vcol.UsageType != DTSUsageType.UT_READWRITE || forceOverwrite)
{
_instance.SetUsageType(Component.InputCollection[0].ID, vi, vcol.LineageID, usageType);
}
}
public static bool DoesInputColumnMatchVirtualInputColumns(IDTSInput100 input)
{
if (input == null)
{
throw new ArgumentNullException("input");
}
IDTSVirtualInput100 vInput = input.GetVirtualInput();
bool Cancel = false;
bool areAllColumnsValid = true;
// Verify that the columns in the input, have the same column metadata
// as the matching virtual input column.
foreach (IDTSInputColumn100 column in input.InputColumnCollection)
{
// Get the upstream column.
IDTSVirtualInputColumn100 vColumn
= vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(column.LineageID);
if (!ComponentValidation.DoesColumnMetaDataMatch(column, vColumn))
{
areAllColumnsValid = false;
input.Component.FireError(
0,
input.Component.Name,
@"The input column metadata for column" + column.IdentificationString + @" does not match its upstream column.",
@"",
0,
out Cancel);
}
}
return(areAllColumnsValid);
}
protected virtual void SetInputUsageType(string name, DTSUsageType usageType)
{
IDTSVirtualInput100 vi = Component.InputCollection[0].GetVirtualInput();
IDTSVirtualInputColumn100 vcol = vi.VirtualInputColumnCollection[name];
SetInputUsageType(vi, vcol, usageType);
}
private void PropagateInputColumns(Dictionary <string, MyColumn> exColumns)
{
IDTSComponentMetaData100 comp = this.MetadataCollection;
CManagedComponentWrapper dcomp = comp.Instantiate();
IDTSInput100 input = comp.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
IDTSVirtualInputColumnCollection100 vColumns = vInput.VirtualInputColumnCollection;
IDTSOutput100 output = comp.OutputCollection[0];
output.TruncationRowDisposition = DTSRowDisposition.RD_NotUsed;
output.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed;
//create input columns for destination external
foreach (KeyValuePair <string, MyColumn> exColumn in exColumns)
{
int vColumnID = FindVirtualInputColumnId(vColumns, exColumn.Key);
if (vColumnID != 0)
{
//do type conversion
IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(vColumnID);
if (vColumn.DataType != exColumn.Value.DataType)
{
dcomp.SetUsageType(input.ID, vInput, vColumnID, DTSUsageType.UT_READONLY);
IDTSOutputColumn100 oColumn = output.OutputColumnCollection.New();
oColumn.Name = exColumn.Key;
oColumn.SetDataTypeProperties(exColumn.Value.DataType, exColumn.Value.Length, exColumn.Value.Precision, exColumn.Value.Scale, exColumn.Value.CodePage);
oColumn.ExternalMetadataColumnID = 0;
oColumn.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
oColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
IDTSCustomProperty100 property = oColumn.CustomPropertyCollection.New();
property.Name = "SourceInputColumnLineageID";
property.Value = vColumnID;
property = oColumn.CustomPropertyCollection.New();
property.Name = "FastParse";
property.Value = false;
//set of derived columns
m_converted.Add(oColumn.Name.ToLower(), oColumn.LineageID);
}
else
{
m_converted.Add(exColumn.Key.ToLower(), vColumnID);
}
}
else
{
//the column wasn't found if the Id is 0, so we'll print out a message and skip this row.
//PrintOutput.PrintToOutput("Converter: Could not map external column " + exColumn.Key + ". Skipping column.");
}
}
}
private static bool DoesColumnMetaDataMatch(IDTSInputColumn100 column, IDTSVirtualInputColumn100 vColumn)
{
if (vColumn.DataType == column.DataType
&& vColumn.Precision == column.Precision
&& vColumn.Length == column.Length
&& vColumn.Scale == column.Scale)
{
return true;
}
return false;
}
private static bool DoesColumnMetaDataMatch(IDTSInputColumn100 column, IDTSVirtualInputColumn100 vColumn)
{
if (vColumn.DataType == column.DataType &&
vColumn.Precision == column.Precision &&
vColumn.Length == column.Length &&
vColumn.Scale == column.Scale)
{
return(true);
}
return(false);
}
public static string GetTooltipString(object dataflowColumn)
{
if (dataflowColumn is IDTSVirtualInputColumn100)
{
IDTSVirtualInputColumn100 column = dataflowColumn as IDTSVirtualInputColumn100;
return(FormatTooltipText(
column.Name,
column.DataType.ToString(),
column.Length.ToString(CultureInfo.CurrentCulture), // Changed from CurrentUICulture
column.Scale.ToString(CultureInfo.CurrentCulture),
column.Precision.ToString(CultureInfo.CurrentCulture),
column.CodePage.ToString(CultureInfo.CurrentCulture),
column.SourceComponent));
}
else if (dataflowColumn is IDTSInputColumn100)
{
IDTSInputColumn100 column = dataflowColumn as IDTSInputColumn100;
return(FormatTooltipText(
column.Name,
column.DataType.ToString(),
column.Length.ToString(CultureInfo.CurrentCulture),
column.Scale.ToString(CultureInfo.CurrentCulture),
column.Precision.ToString(CultureInfo.CurrentCulture),
column.CodePage.ToString(CultureInfo.CurrentCulture)));
}
else if (dataflowColumn is IDTSOutputColumn100)
{
IDTSOutputColumn100 column = dataflowColumn as IDTSOutputColumn100;
return(FormatTooltipText(
column.Name,
column.DataType.ToString(),
column.Length.ToString(CultureInfo.CurrentCulture),
column.Scale.ToString(CultureInfo.CurrentCulture),
column.Precision.ToString(CultureInfo.CurrentCulture),
column.CodePage.ToString(CultureInfo.CurrentCulture)));
}
else if (dataflowColumn is IDTSExternalMetadataColumn100)
{
IDTSExternalMetadataColumn100 column = dataflowColumn as IDTSExternalMetadataColumn100;
return(FormatTooltipText(
column.Name,
column.DataType.ToString(),
column.Length.ToString(CultureInfo.CurrentCulture),
column.Scale.ToString(CultureInfo.CurrentCulture),
column.Precision.ToString(CultureInfo.CurrentCulture),
column.CodePage.ToString(CultureInfo.CurrentCulture)));
}
return(string.Empty);
}
public ExpressionMatchEvaluatorStruct(
int groupNum,
string text,
Transformation trans,
IDTSVirtualInput100 vi,
DTSUsageType inputColumnUsageType,
IDTSVirtualInputColumn100 vcol)
{
this.groupNum = groupNum;
this.text = text;
this.trans = trans;
this.vi = vi;
this.inputColumnUsageType = inputColumnUsageType;
this.vcol = vcol;
}
public ExpressionMatchEvaluatorStruct(
int groupNum,
string text,
Transformation trans,
IDTSVirtualInput100 vi,
DTSUsageType inputColumnUsageType,
IDTSVirtualInputColumn100 vcol)
{
this.groupNum = groupNum;
this.text = text;
this.trans = trans;
this.vi = vi;
this.inputColumnUsageType = inputColumnUsageType;
this.vcol = vcol;
}
public virtual IDTSInputColumn100 SetInputColumnUsage(object inputPathIndex, object columnIndex, DTSUsageType usageType, bool forceOverwrite)
{
try
{
IDTSVirtualInput100 vi = Component.InputCollection[inputPathIndex].GetVirtualInput();
IDTSVirtualInputColumn100 vcol = vi.VirtualInputColumnCollection[columnIndex];
return(SetInputColumnUsage(inputPathIndex, columnIndex, vi, vcol, usageType, forceOverwrite));
}
catch (Exception)
{
MessageEngine.Trace(_astTransformationNode, Severity.Error, "V0110", "Could not locate input column {0}", columnIndex);
}
return(null);
}
public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
IDTSVirtualInputColumn100 virtualInputColumn = virtualInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(lineageID);
if (!IsExpectedUsageType(virtualInputColumn.Name, virtualInputColumn.UsageType))
{
throw new Exception(String.Format(ErrorInvalidUsageType, virtualInputColumn.Name));
}
if (!IsExpectedDataType(virtualInputColumn.Name, virtualInputColumn.DataType))
{
throw new Exception(String.Format(ErrorInvalidDataType, virtualInputColumn.Name));
}
return(base.SetUsageType(inputID, virtualInput, lineageID, usageType));
}
/// <summary>
/// Reinitialize metadata in case input cvolumns are invalid
/// </summary>
public override void ReinitializeMetaData()
{
if (!inputColumnsValid)
{
IDTSInput input = ComponentMetaData.InputCollection[0];
IDTSVirtualInput vInput = input.GetVirtualInput();
foreach (IDTSInputColumn col in input.InputColumnCollection)
{
IDTSVirtualInputColumn100 vCol = vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(col.LineageID);
if (vCol == null)
{
input.InputColumnCollection.RemoveObjectByID(col.ID);
}
}
inputColumnsValid = true;
}
}
public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
IDTSVirtualInputColumn100 virtualInputColumn = virtualInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(lineageID);
if (usageType == DTSUsageType.UT_READONLY)
{
throw new Exception(String.Format(ErrorInvalidUsageType, virtualInputColumn.Name));
}
if (usageType == DTSUsageType.UT_READWRITE)
{
if (virtualInputColumn.DataType != DataType.DT_STR && virtualInputColumn.DataType != DataType.DT_WSTR)
{
throw new Exception(String.Format(ErrorInvalidDataType, virtualInputColumn.Name));
}
}
return(base.SetUsageType(inputID, virtualInput, lineageID, usageType));
}
/// <summary>
/// Getting tooltip text to be displayed for the given data flow column.
/// </summary>
/// <param name="dataFlowColumn"></param>
/// <returns></returns>
static public string GetTooltipString(object dataFlowColumn)
{
Debug.Assert(dataFlowColumn != null, "Tag is NULL");
if (dataFlowColumn is IDTSVirtualInputColumn100)
{
IDTSVirtualInputColumn100 column = dataFlowColumn as IDTSVirtualInputColumn100;
return(FormatTooltipText(column.Name, column.DataType.ToString(),
column.Length.ToString(CultureInfo.CurrentUICulture),
column.Scale.ToString(CultureInfo.CurrentUICulture),
column.Precision.ToString(CultureInfo.CurrentUICulture),
column.CodePage.ToString(CultureInfo.CurrentUICulture),
column.SourceComponent));
}
else if (dataFlowColumn is IDTSInputColumn100)
{
IDTSInputColumn100 column = dataFlowColumn as IDTSInputColumn100;
return(FormatTooltipText(column.Name, column.DataType.ToString(),
column.Length.ToString(CultureInfo.CurrentUICulture),
column.Scale.ToString(CultureInfo.CurrentUICulture),
column.Precision.ToString(CultureInfo.CurrentUICulture),
column.CodePage.ToString(CultureInfo.CurrentUICulture)));
}
else if (dataFlowColumn is IDTSOutputColumn100)
{
IDTSOutputColumn100 column = dataFlowColumn as IDTSOutputColumn100;
return(FormatTooltipText(column.Name, column.DataType.ToString(), column.Length.ToString(CultureInfo.CurrentUICulture),
column.Scale.ToString(CultureInfo.CurrentUICulture),
column.Precision.ToString(CultureInfo.CurrentUICulture),
column.CodePage.ToString(CultureInfo.CurrentUICulture)));
}
else if (dataFlowColumn is IDTSExternalMetadataColumn100)
{
IDTSExternalMetadataColumn100 column = dataFlowColumn as IDTSExternalMetadataColumn100;
return(FormatTooltipText(column.Name, column.DataType.ToString(),
column.Length.ToString(CultureInfo.CurrentUICulture),
column.Scale.ToString(CultureInfo.CurrentUICulture),
column.Precision.ToString(CultureInfo.CurrentUICulture),
column.CodePage.ToString(CultureInfo.CurrentUICulture)));
}
return(string.Empty);
}
/// <summary>
/// Called when a user has selected an Input column for the component. This component only accepts input columns
/// that have DTSUsageType.UT_READWRITE. Any other usage types are rejected.
/// </summary>
/// <param name="inputID">The ID of the input that the column is inserted in.</param>
/// <param name="virtualInput">The virtual input object containing that contains the new column.</param>
/// <param name="lineageID">The lineageID of the virtual input column.</param>
/// <param name="usageType">The DTSUsageType parameter that specifies how the column is used by the component.</param>
/// <returns>The newly created IDTSInputColumn100.</returns>
public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
IDTSVirtualInputColumn100 virtualColumn = virtualInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(lineageID);
if (usageType == DTSUsageType.UT_READWRITE)
{
throw new Exception("The UsageType must be set to Read Only.");
}
// Get the column
IDTSInputColumn100 col = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
//if (((virtualColumn.DataType != DataType.DT_NTEXT)
// && (virtualColumn.DataType != DataType.DT_IMAGE))
// && (virtualColumn.DataType != DataType.DT_BYTES))
//{
return(base.SetUsageType(inputID, virtualInput, lineageID, usageType));
//}
//throw new Exception("Input column data types cannot be DT_NTEXT, D_IMAGE or DT_BYTES.");
}
public static void FixInvalidInputColumnMetaData(IDTSInput100 input)
{
if (input == null)
{
throw new ArgumentNullException("input");
}
IDTSVirtualInput100 vInput = input.GetVirtualInput();
foreach (IDTSInputColumn100 inputColumn in input.InputColumnCollection)
{
IDTSVirtualInputColumn100 vColumn
= vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(inputColumn.LineageID);
if (!DoesColumnMetaDataMatch(inputColumn, vColumn))
{
vInput.SetUsageType(vColumn.LineageID, inputColumn.UsageType);
}
}
}
public void AutoMap()
{
IDTSVirtualInput100 cvi = _component.InputCollection[0].GetVirtualInput();
Dictionary <string, IDTSVirtualInputColumn100> virtualInputDictionary = new Dictionary <string, IDTSVirtualInputColumn100>();
foreach (IDTSVirtualInputColumn100 vc in cvi.VirtualInputColumnCollection)
{
virtualInputDictionary[vc.Name.ToUpperInvariant()] = vc;
}
// Automatically map columns
foreach (IDTSExternalMetadataColumn100 extCol in _component.InputCollection[0].ExternalMetadataColumnCollection)
{
if (virtualInputDictionary.ContainsKey(extCol.Name.ToUpperInvariant()))
{
IDTSVirtualInputColumn100 vc = virtualInputDictionary[extCol.Name.ToUpperInvariant()];
_instance.SetUsageType(_component.InputCollection[0].ID, cvi, vc.LineageID, DTSUsageType.UT_READONLY);
_component.InputCollection[0].InputColumnCollection[vc.Name].ExternalMetadataColumnID = extCol.ID;
}
}
// Map any overrides
foreach (Mapping mapping in ((Destination)_transformation).Mappings)
{
string dest;
bool unMap = false;
if (mapping.Destination == null)
{
unMap = true;
dest = mapping.Source;
}
else
{
dest = mapping.Destination;
}
Map(mapping.Source, dest, unMap);
}
}
private void AddInputColumn(string vcolInputName)
{
var input = _md.InputCollection[0];
var virtualInputs = _md.InputCollection[0].GetVirtualInput();
IDTSVirtualInputColumn100 vcol = null;
// Aggancia tutti i virtual input agli input veri e propri
input.InputColumnCollection.RemoveAll();
foreach (IDTSVirtualInputColumn100 vc in virtualInputs.VirtualInputColumnCollection)
{
var col = input.InputColumnCollection.New();
col.Name = vc.Name;
col.LineageID = vc.LineageID;
}
/*
* CManagedComponentWrapper destDesignTime = _md.Instantiate();
* // Il metodo seguente effettua il mapping tra una VirtualInputColumn (che di fatto corrisponde all'output del componente in gerarchia) ed una colonna fisica
* // di questo componente. Nel nostro caso abbiamo una sola colonna, quindi ci basta eseguire questo metodo una sola volta.
* destDesignTime.SetUsageType(input.ID, virtualInputs, vcol.LineageID, DTSUsageType.UT_READONLY);
* */
}
protected virtual bool needDataTypeChange(IDTSVirtualInput100 vinput, IDTSInput100 input)
{
IDTSExternalMetadataColumnCollection100 exColumns = input.ExternalMetadataColumnCollection;
foreach (IDTSExternalMetadataColumn100 exColumn in exColumns)
{
int vColumnID = FindVirtualInputColumnId(vinput.VirtualInputColumnCollection, exColumn.Name);
if (vColumnID != 0)
{
IDTSVirtualInputColumn100 vColumn = vinput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(vColumnID);
if (exColumn.DataType != vColumn.DataType ||
exColumn.Length != vColumn.Length ||
exColumn.Precision != vColumn.Precision ||
exColumn.Scale != vColumn.Scale
)
{
return(true);
}
}
}
return(false);
}
public void MapColumnsSPList(IDTSComponentMetaData100 DestinationTask
, CManagedComponentWrapper InstanceDestination
, DTSUsageType dtsUsageType, string ErrorDetail)
{
#region map the columns
IDTSInput100 input = DestinationTask.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
IDTSInputColumn100 vCol = null;
string columnName = string.Empty;
try
{
foreach (IDTSExternalMetadataColumn100 exCol in input.ExternalMetadataColumnCollection)
{
columnName = exCol.Name;
IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection[exCol.Name];
vCol = InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, dtsUsageType);
InstanceDestination.MapInputColumn(input.ID, vCol.ID, exCol.ID);
}
}
catch (Exception ex)
{
throw new Exception("Table Mapping failed for source to destination for the column '" + columnName + "'. " + ErrorDetail);
}
}
/// <summary>
/// Validate HashColumnsTransform metadata
/// </summary>
/// <returns></returns>
public override DTSValidationStatus Validate()
{
if (ComponentMetaData.InputCollection.Count != 1)
{
FireComponentMetadataError(0, Resources.ErrorIncorrectNumberOfInputs);
return(DTSValidationStatus.VS_NEEDSNEWMETADATA);
}
if (ComponentMetaData.OutputCollection.Count != 1)
{
FireComponentMetadataError(0, Resources.ErrorIncorrectNumberOfOutputs);
return(DTSValidationStatus.VS_NEEDSNEWMETADATA);
}
IDTSInput input = ComponentMetaData.InputCollection[0];
IDTSVirtualInput vInput = input.GetVirtualInput();
foreach (IDTSInputColumn column in input.InputColumnCollection)
{
try
{
IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(column.LineageID);
}
catch
{
FireComponentMetadataError(0, string.Format(Resources.ErrorInputColumnNotInUpstreamComponent, column.IdentificationString));
inputColumnsValid = false;
return(DTSValidationStatus.VS_NEEDSNEWMETADATA);
}
}
IDTSOutput output = ComponentMetaData.OutputCollection[0];
List <IDTSOutputColumn> redundantColumns = new List <IDTSOutputColumn>(output.OutputColumnCollection.Count);
bool xmlColumnExists = false;
List <int> missingLineages = new List <int>();
List <int> missingInputLineages = new List <int>();
bool missingError = false;
foreach (IDTSOutputColumn outputColumn in output.OutputColumnCollection)
{
bool isRendundant = true;
bool isInputColumn = false;
foreach (IDTSInputColumn inputColumn in input.InputColumnCollection)
{
if (inputColumn.Name == outputColumn.Name)
{
isRendundant = false;
isInputColumn = true;
break;
}
}
//Check if XML Column;
if (!isInputColumn)
{
foreach (IDTSCustomProperty prop in outputColumn.CustomPropertyCollection)
{
if (prop.Name == Resources.XmlInputColumnsPropertyname)
{
isRendundant = false;
xmlColumnExists = true;
var lineages = InputColumns.ParseInputLineages(prop.Value.ToString());
foreach (IDTSInputColumn icol in input.InputColumnCollection)
{
int l = icol.LineageID;
lineages.Remove(l);
if (lineages.Count == 0)
{
break;
}
}
if (lineages.Count > 0)
{
foreach (int l in lineages)
{
if (!missingInputLineages.Contains(l))
{
missingInputLineages.Add(l);
}
}
foreach (IDTSVirtualInputColumn vcol in vInput.VirtualInputColumnCollection)
{
int l = vcol.LineageID;
lineages.Remove(l);
if (lineages.Count == 0)
{
break;
}
}
if (lineages.Count > 0)
{
foreach (int l in lineages)
{
if (!missingLineages.Contains(l))
{
missingLineages.Add(l);
}
}
}
}
}
if (prop.Name == Resources.XmlSaveOptionPropertyName)
{
isRendundant = false;
xmlColumnExists = true;
if (!Enum.IsDefined(typeof(SaveOptions), prop.Value))
{
FireComponentMetadataError(0, string.Format(Resources.ErrorInvalidSaveOption, outputColumn.Name));
return(DTSValidationStatus.VS_NEEDSNEWMETADATA);
}
else
{
if (outputColumn.DataType != DataType.DT_NTEXT && outputColumn.DataType != DataType.DT_WSTR)
{
FireComponentMetadataError(0, string.Format(Resources.ErrorInvalidDataType, outputColumn.Name));
return(DTSValidationStatus.VS_NEEDSNEWMETADATA);
}
}
}
}
}
//add redundand column to redundand list
if (isRendundant)
{
redundantColumns.Add(outputColumn);
}
}
if (missingLineages.Count > 0)
{
string ml = string.Join(", ", missingLineages.ConvertAll <string>(l => l.ToString()).ToArray());
FireComponentMetadataError(0, string.Format("Output columns are referencing input column lineages which do not exists: {0}", ml));
missingError = true;
}
if (missingInputLineages.Count > 0)
{
string ml = string.Join(", ", missingInputLineages.ConvertAll <string>(l => l.ToString()).ToArray());
FireComponentMetadataError(0, string.Format("Output columns are referencing input column lineages which are not selected as Input columns: {0}", ml));
missingError = true;
}
if (missingError)
{
return(DTSValidationStatus.VS_NEEDSNEWMETADATA);
}
//remove redundand output columns
foreach (IDTSOutputColumn col in redundantColumns)
{
output.OutputColumnCollection.RemoveObjectByID(col.ID);
}
//If XmlColumn does not exists, create a default one
if (!xmlColumnExists)
{
IDTSOutputColumn xmlCol = output.OutputColumnCollection.New();
xmlCol.Name = Resources.XmlColumnDefaultName;
xmlCol.Description = Resources.XmlColumnDefaultDesccription;
SetXmlColumnProperties(xmlCol);
}
return(DTSValidationStatus.VS_ISVALID);
}
protected virtual void SetInputUsageType(IDTSVirtualInput100 vi, IDTSVirtualInputColumn100 vcol, DTSUsageType usageType, bool forceOverwrite)
{
if (vcol.UsageType != DTSUsageType.UT_READWRITE || forceOverwrite)
{
_instance.SetUsageType(Component.InputCollection[0].ID, vi, vcol.LineageID, usageType);
}
}
protected virtual void SetInputUsageType(IDTSVirtualInput100 vi, IDTSVirtualInputColumn100 vcol, DTSUsageType usageType)
{
this.SetInputUsageType(vi, vcol, usageType, false);
}
/// <summary>
/// Called when the user finishes editing a cell. We apply changes
/// directly to the component input column here.
/// </summary>
private void mappingGridView_CellEndEdit(object sender, DataGridViewCellEventArgs e)
{
// Ignore cell header changes!
if (e.RowIndex < 0)
{
return;
}
// Get the currently selected input/virtual input, and virtual input column from
// the changed row.
IDTSInput100 input = component_.InputCollection[inputComboBox.SelectedItem];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
// Use the column name in column 1 of the grid to find the lineage id
// of the column we're editing.
IDTSVirtualInputColumn100 vColumn =
vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(mapColumnNameToLineageID_[(string)mappingGridView.Rows[e.RowIndex].Cells[1].Value]);
// pull out the first column value to determine if the cell is mapped.
bool mapped = (bool)mappingGridView.Rows[e.RowIndex].Cells[0].Value;
// If the checkbox cell is the one that changed, we need to either set or clear the properties.
if (e.ColumnIndex == 0)
{
if (mapped)
{
// The checkbox was just set -- we need to map this column and set default values
// for the XML mapping properties.
// Map the column
vInput.SetUsageType(vColumn.LineageID, DTSUsageType.UT_READONLY);
// Fetch the IDTSInputColumn100 now that it is mapped.
IDTSInputColumn100 inputColumn = input.InputColumnCollection.GetInputColumnByLineageID(vColumn.LineageID);
// Create the custom mapping properties on this input column.
AddCustomInputColumnProps(inputColumn);
// Set default values for the new properties.
// Use XmlConvert.EncodeLocalName to create a default tag name from the column name.
mappingGridView.Rows[e.RowIndex].Cells[2].Value = System.Xml.XmlConvert.EncodeLocalName((string)mappingGridView.Rows[e.RowIndex].Cells[1].Value);
mappingGridView.Rows[e.RowIndex].Cells[2].ReadOnly = false;
inputColumn.CustomPropertyCollection[Constants.AttributeOrElementNameProperty].Value =
mappingGridView.Rows[e.RowIndex].Cells[2].Value;
// Set the item to an element style.
mappingGridView.Rows[e.RowIndex].Cells[3].ReadOnly = false;
mappingGridView.Rows[e.RowIndex].Cells[3].Value = Constants.ElementStyle;
inputColumn.CustomPropertyCollection[Constants.StyleProperty].Value =
mappingGridView.Rows[e.RowIndex].Cells[3].Value;
}
else
{
// The mapping for this olumn is unchecked.
// Clear it all out. Don't sweat clearing the custom properties.
mappingGridView.Rows[e.RowIndex].Cells[2].ReadOnly = true;
mappingGridView.Rows[e.RowIndex].Cells[3].ReadOnly = true;
mappingGridView.Rows[e.RowIndex].Cells[3].Value = Constants.ElementStyle;
mappingGridView.Rows[e.RowIndex].Cells[2].Value = "";
vInput.SetUsageType(vColumn.LineageID, DTSUsageType.UT_IGNORED);
}
}
if (e.ColumnIndex == 2 && mapped)
{
// The tag/attribute name has changed and the column is mapped --
// change the value of the property appropriately.
input.InputColumnCollection.GetInputColumnByLineageID(vColumn.LineageID).CustomPropertyCollection[Constants.AttributeOrElementNameProperty].Value =
(string)mappingGridView.Rows[e.RowIndex].Cells[2].Value;
}
if (e.ColumnIndex == 3 && mapped)
{
// The style of this column has changed, update the property appropriately.
input.InputColumnCollection.GetInputColumnByLineageID(vColumn.LineageID).CustomPropertyCollection[Constants.StyleProperty].Value =
(string)mappingGridView.Rows[e.RowIndex].Cells[3].Value;
}
}
public override void Emit(SsisEmitterContext context)
{
ProcessBindings(context);
// TODO: Add Input column custom property mapping
foreach (var column in _astSlowlyChangingDimensionNode.Mappings)
{
int columnType;
switch (column.MappingType)
{
case ScdColumnMappingType.ChangingAttribute:
columnType = 2;
break;
case ScdColumnMappingType.FixedAttribute:
columnType = 4;
break;
case ScdColumnMappingType.HistoricalAttribute:
columnType = 3;
break;
case ScdColumnMappingType.Key:
columnType = 1;
break;
case ScdColumnMappingType.Other:
columnType = 0;
break;
default:
MessageEngine.Trace(_astSlowlyChangingDimensionNode, Severity.Error, "V0140", "Unrecognized ScdColumnMappingType {0} on column {1}", column.MappingType.ToString(), column.QueryColumnName);
return;
}
IDTSInputColumn100 inputColumn = TransformationUtility.FindInputColumnByName(column.QueryColumnName, Component.InputCollection[0], true);
if (inputColumn == null)
{
IDTSVirtualInputColumn100 virtualInputColumn = TransformationUtility.FindVirtualInputColumnByName(column.QueryColumnName, Component.InputCollection[0], true);
if (virtualInputColumn == null)
{
MessageEngine.Trace(_astSlowlyChangingDimensionNode, Severity.Error, "V0141", "Column {0} could not be found", column.QueryColumnName);
return;
}
inputColumn = Component.InputCollection[0].InputColumnCollection.New();
inputColumn.Name = column.QueryColumnName;
inputColumn.LineageID = virtualInputColumn.LineageID;
}
IDTSCustomProperty100 propColumnType = TransformationUtility.FindCustomPropertyByName("ColumnType", inputColumn.CustomPropertyCollection, true);
if (propColumnType == null)
{
propColumnType = inputColumn.CustomPropertyCollection.New();
propColumnType.Name = "ColumnType";
propColumnType.TypeConverter = "ColumnType";
}
propColumnType.Value = columnType;
}
}
internal BipsProxyIDTSVirtualInputColumn100(IDTSVirtualInputColumn100 innerObject)
{
_innerObject = innerObject;
}
//Design time - Metadata Validataor
public override DTSValidationStatus Validate()
{
bool pbCancel = false;
// Validate that there is only one input.
if (ComponentMetaData.InputCollection.Count != 1)
{
ComponentMetaData.FireError(0, ComponentMetaData.Name, "Incorrect number of inputs.", "", 0, out pbCancel);
return(DTSValidationStatus.VS_ISCORRUPT);
}
// Determine whether the metdada needs refresh
IDTSInput100 input = ComponentMetaData.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
bool cancel = false;
foreach (IDTSInputColumn100 column in input.InputColumnCollection)
{
try
{
IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(column.LineageID);
}
catch
{
ComponentMetaData.FireError(0, ComponentMetaData.Name, "The input column " + column.IdentificationString + " does not match a column in the upstream component.", "", 0, out cancel);
areInputColumnsValid = false;
return(DTSValidationStatus.VS_NEEDSNEWMETADATA);
}
}
_phonenumberLinage = GetCustomPropertyValue <int>(Constants.PhoneNumberLinageColumn);
_phoneNumberIsoLinage = GetCustomPropertyValue <int>(Constants.PhoneNumberIsoLinageColumn);
_phoneNumberIsoCode = GetCustomPropertyValue <string>(Constants.PhoneNumberIsoCodeColumn);
// check configuration
if (-1 == _phonenumberLinage || (-1 == _phoneNumberIsoLinage && string.IsNullOrEmpty(_phoneNumberIsoCode)))
{
return(DTSValidationStatus.VS_NEEDSNEWMETADATA);
}
//validate input to be of type string/numeric only
for (int x = 0; x < input.InputColumnCollection.Count; x++)
{
if (!(input.InputColumnCollection[x].DataType == DataType.DT_STR ||
input.InputColumnCollection[x].DataType == DataType.DT_WSTR ||
input.InputColumnCollection[x].DataType == DataType.DT_DECIMAL ||
input.InputColumnCollection[x].DataType == DataType.DT_NUMERIC))
{
ComponentMetaData.FireError(0, ComponentMetaData.Name, "Invalid Data Type specified for " + input.InputColumnCollection[x].Name
+ ". Supported Data Types are DT_STR,DT_WSTR,DT_NUMERIC and DT_NUMERIC", "", 0, out pbCancel);
return(DTSValidationStatus.VS_ISCORRUPT);
}
}
//create corresponding output columns dynamically
IDTSOutput100 output = ComponentMetaData.OutputCollection[0];
foreach (IDTSInputColumn100 inputcolumn in input.InputColumnCollection)
{
if (inputcolumn.LineageID == _phonenumberLinage)
{
GenerateOutputColumns(output, inputcolumn);
}
}
var outputColumnToRemove = new List <int>();
foreach (IDTSOutputColumn100 outputColumn in output.OutputColumnCollection)
{
if (!_outputColumnList.Any(c => c.ColumnName == outputColumn.Name))
{
outputColumnToRemove.Add(outputColumn.ID);
}
}
outputColumnToRemove.ForEach(o => output.OutputColumnCollection.RemoveObjectByID(o));
return(DTSValidationStatus.VS_ISVALID);
}
public virtual IDTSInputColumn100 SetInputColumnUsage(object inputPathIndex, object columnIndex, IDTSVirtualInput100 virtualInput, IDTSVirtualInputColumn100 virtualInputColumn, DTSUsageType usageType, bool forceOverwrite)
{
if ((virtualInputColumn.UsageType == usageType || virtualInputColumn.UsageType == DTSUsageType.UT_READWRITE) && !forceOverwrite)
{
}
else
{
Instance.SetUsageType(Component.InputCollection[inputPathIndex].ID, virtualInput, virtualInputColumn.LineageID, usageType);
}
return TransformationUtility.FindInputColumnByName(columnIndex.ToString(), Component.InputCollection[inputPathIndex], true);
}
public IDTSComponentMetaData100 AddComp_OleDBCommand(string componentName,
string conManName,
IDTSOutput100 outCols,
string sqlCmd,
Dictionary <string, string> paramMapping
)
{
//
// --------------------------------------------------------------------------------------------------------
// Create
IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New();
Comp.ComponentClassID = "Microsoft.OLEDBCommand";
// Instantiate
CManagedComponentWrapper Inst = Comp.Instantiate();
Inst.ProvideComponentProperties();
Comp.Name = componentName;
Comp.Description = "bloczek OLEDBSource";
// Connect
IDTSPath100 pth = dmp.PathCollection.New();
pth.AttachPathAndPropagateNotifications(outCols, Comp.InputCollection[0]);
// GetConnectionManager
ConnectionManager cm = prj.ConnectionManagerItems[conManName + ".conmgr"].ConnectionManager;
// Parametrize #1
Comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm);
Comp.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
Inst.SetComponentProperty("SqlCommand", sqlCmd);
Inst.AcquireConnections(null); // Establishes a connection to a connection manager
Inst.ReinitializeMetaData(); // Called to allow the component to repair problems with the IDTSComponentMetaData100 object that were identified by the component during the Validate() method.
Inst.ReleaseConnections(); // Frees the connections established by the component during AcquireConnections(Object).
// Parametrize #2
IDTSInput100 input = Comp.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
IDTSVirtualInputColumnCollection100 sourceColumns = vInput.VirtualInputColumnCollection;
foreach (KeyValuePair <string, string> kvp in paramMapping)
{
IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection[kvp.Value];
Inst.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
IDTSExternalMetadataColumn100 exColumn = Comp.InputCollection[0].ExternalMetadataColumnCollection[kvp.Key];
IDTSInputColumn100 inColumn = Comp.InputCollection[0].InputColumnCollection[kvp.Value];
Inst.MapInputColumn(Comp.InputCollection[0].ID, inColumn.ID, exColumn.ID);
}
return(Comp);
}
public override DTSValidationStatus Validate()
{
// Determine whether the metadata needs refresh
IDTSInput100 input = ComponentMetaData.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
bool cancel = false;
foreach (IDTSInputColumn100 column in input.InputColumnCollection)
{
try
{
IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(column.LineageID);
}
catch (Exception)
{
var msg = string.Format("The input column {0} does not match a coulumn in the upstream component.", column.IdentificationString);
ComponentMetaData.FireError(0, ComponentMetaData.Name, msg, "", 0, out cancel);
return(DTSValidationStatus.VS_NEEDSNEWMETADATA);
}
}
// validate input to be of type string/numeric only
bool pbCancel = false;
for (int x = 0; x < input.InputColumnCollection.Count; x++)
{
if (!(input.InputColumnCollection[x].DataType == DataType.DT_STR ||
input.InputColumnCollection[x].DataType == DataType.DT_WSTR ||
input.InputColumnCollection[x].DataType == DataType.DT_DECIMAL ||
input.InputColumnCollection[x].DataType == DataType.DT_NUMERIC ||
input.InputColumnCollection[x].DataType == DataType.DT_TEXT))
{
var msg = string.Format("Column {0} cannot be used for data masking.\nThe data typoe supplied was {1}.\nThe Supported data types are DT_STR, DT_WSTR, DT_DECIMAL, DT_NUMERIC and DT_TEXT.\nUnmark the offending column(s) to correct.", input.InputColumnCollection[x].Name, input.InputColumnCollection[x].DataType.ToString());
ComponentMetaData.FireError(0, ComponentMetaData.Name, msg, "", 0, out pbCancel);
return(DTSValidationStatus.VS_ISBROKEN);
}
}
// create corresponding output columns dynamically
IDTSOutput100 output = ComponentMetaData.OutputCollection[0];
foreach (IDTSInputColumn100 inputColumn in input.InputColumnCollection)
{
bool isExist = false;
foreach (IDTSOutputColumn100 outputColumn in output.OutputColumnCollection)
{
if (outputColumn.Name == string.Format("Masked_{0}", inputColumn.Name))
{
isExist = true;
}
}
if (!isExist)
{
IDTSOutputColumn100 outputCol = output.OutputColumnCollection.New();
outputCol.Name = string.Format("Masked_{0}", inputColumn.Name);
outputCol.Description = string.Format("Masked {0}", inputColumn.Name);
outputCol.SetDataTypeProperties(inputColumn.DataType, inputColumn.Length, inputColumn.Precision, inputColumn.Scale, inputColumn.CodePage);
}
}
// remove redundant output columns that don't match the input columns
if (output.OutputColumnCollection.Count > input.InputColumnCollection.Count)
{
foreach (IDTSOutputColumn100 outputColumn in output.OutputColumnCollection)
{
bool isRedundant = true;
foreach (IDTSInputColumn100 inputColumn in input.InputColumnCollection)
{
isRedundant = outputColumn.Name.Contains(string.Format("Masked_{0}", inputColumn.Name)) ? false : true;
if (!isRedundant)
{
break;
}
if (isRedundant)
{
output.OutputColumnCollection.RemoveObjectByID(outputColumn.ID);
}
}
}
}
return(DTSValidationStatus.VS_ISVALID);
}
public void GetTooltipStringTest()
{
string expected = string.Empty;
string result = string.Empty;
Microsoft.SqlServer.Dts.Runtime.Package package;
IDTSComponentMetaData100 multipleHash;
CManagedComponentWrapper multipleHashInstance;
String lineageString;
MainPipe dataFlowTask;
// Microsoft.SqlServer.Dts.Runtime.Application app;
StaticTestUtilities.BuildSSISPackage(out package, out multipleHash, out multipleHashInstance, out lineageString, out dataFlowTask /*, out app */);
result = DataFlowComponentUI.GetTooltipString(multipleHash.InputCollection[0].InputColumnCollection[0]);
expected = "Name: StringData\nData type: DT_STR\nLength: 255\nScale: 0\nPrecision: 0\nCode page: 1252";
Assert.AreEqual(expected, result, "Checking IDTSInputColumn");
int outputID = multipleHash.OutputCollection[0].ID;
int outputColumnPos = multipleHash.OutputCollection[0].OutputColumnCollection.Count;
// Add output column CRC32BinaryOutput (CRC32, Binary)
IDTSOutputColumn100 CRC32CBinaryOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "BinaryOutput", "CRC32C Hash of the input");
CRC32CBinaryOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Binary;
CRC32CBinaryOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.CRC32C;
CRC32CBinaryOutput.Name = "BinaryOutput";
CRC32CBinaryOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString;
Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.CRC32C, MultipleHash.OutputTypeEnumerator.Binary, CRC32CBinaryOutput);
result = DataFlowComponentUI.GetTooltipString(CRC32CBinaryOutput);
expected = "Name: BinaryOutput\nData type: DT_BYTES\nLength: 4\nScale: 0\nPrecision: 0\nCode page: 0";
Assert.AreEqual(expected, result, "Check IDTSOutputColumn100");
// Add output column CRC32CHexOutput (CRC32C, HexString)
IDTSOutputColumn100 CRC32CHexOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "HexOutput", "CRC32C Hash of the input");
CRC32CHexOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.HexString;
CRC32CHexOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.CRC32C;
CRC32CHexOutput.Name = "HexOutput";
CRC32CHexOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString;
Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.CRC32C, MultipleHash.OutputTypeEnumerator.HexString, CRC32CHexOutput);
// Add output column CRC32CBaseOutput (CRC32C, Base64String)
IDTSOutputColumn100 CRC32CBaseOutput = multipleHashInstance.InsertOutputColumnAt(outputID, outputColumnPos++, "BaseOutput", "CRC32C Hash of the input");
CRC32CBaseOutput.CustomPropertyCollection[Utility.OutputColumnOutputTypePropName].Value = MultipleHash.OutputTypeEnumerator.Base64String;
CRC32CBaseOutput.CustomPropertyCollection[Utility.HashTypePropName].Value = MultipleHash.HashTypeEnumerator.CRC32C;
CRC32CBaseOutput.Name = "BaseOutput";
CRC32CBaseOutput.CustomPropertyCollection[Utility.InputColumnLineagePropName].Value = lineageString;
Utility.SetOutputColumnDataType(MultipleHash.HashTypeEnumerator.CRC32C, MultipleHash.OutputTypeEnumerator.Base64String, CRC32CBaseOutput);
// Add SQL CE Destination
// Add SQL CE Connection
ConnectionManager sqlCECM = null;
IDTSComponentMetaData100 sqlCETarget = null;
CManagedComponentWrapper sqlCEInstance = null;
StaticTestUtilities.CreateSQLCEComponent(package, dataFlowTask, sqlCEDatabaseName, sqlCEPassword, "TestRecords", out sqlCECM, out sqlCETarget, out sqlCEInstance);
// StaticTestUtilities.CreatePath(dataFlowTask, multipleHash.OutputCollection[0], sqlCETarget, sqlCEInstance);
// Create the path from source to destination.
IDTSPath100 path = dataFlowTask.PathCollection.New();
path.AttachPathAndPropagateNotifications(multipleHash.OutputCollection[0], sqlCETarget.InputCollection[0]);
// Get the destination's default input and virtual input.
IDTSInput100 input = sqlCETarget.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection[0];
result = DataFlowComponentUI.GetTooltipString(vColumn);
expected = "Name: StringData\nData type: DT_STR\nLength: 255\nScale: 0\nPrecision: 0\nCode page: 1252\nSource: Flat File Source";
Assert.AreEqual(expected, result, "Check IDTSVirtualInputColumn100");
result = DataFlowComponentUI.GetTooltipString(sqlCETarget.InputCollection[0].ExternalMetadataColumnCollection[0]);
expected = "Name: StringData\nData type: DT_WSTR\nLength: 255\nScale: 0\nPrecision: 0\nCode page: 0";
Assert.AreEqual(expected, result, "Check IDTSExternalMetadataColumn");
result = DataFlowComponentUI.GetTooltipString("String");
expected = String.Empty;
Assert.AreEqual(expected, result, "Check String Object");
}
// todo:
// CacheMode
// https://blogs.msdn.microsoft.com/mattm/2009/01/02/api-sample-lookup-transform/
public IDTSComponentMetaData100 AddComp_Lookup(string componentName,
string conManName,
string sqlComm,
IDTSOutput100 outCols,
List <string> joinColumns,
Dictionary <string, string> newColumns,
DTSRowDisposition rowDisposition = DTSRowDisposition.RD_IgnoreFailure,
int redirectRowsToNoMatchOutput = 0
)
{
// Create
IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New();
Comp.ComponentClassID = "Microsoft.Lookup";
// Instantiate
CManagedComponentWrapper Inst = Comp.Instantiate();
Inst.ProvideComponentProperties();
Comp.Name = componentName;
Comp.Description = "Dodany Lookup";
// Connect
IDTSPath100 pth = dmp.PathCollection.New();
pth.AttachPathAndPropagateNotifications(outCols, Comp.InputCollection[0]);
// GetConnectionManager
ConnectionManager cm = prj.ConnectionManagerItems[conManName + ".conmgr"].ConnectionManager;
Comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm);
Comp.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
// Parametrize #1
IDTSOutput100 lmo = Comp.OutputCollection["Lookup Match Output"];
lmo.ErrorRowDisposition = rowDisposition;
// Cache Type:
// 0 = Full
// 1 = Partial
// 2 = None
Inst.SetComponentProperty("CacheType", 0);
// 0 = FailComponent, IgnoreFailure, RedirectRowsToErrorOutput
// 1 = RedirectRowsToNoMatchOutput
Inst.SetComponentProperty("NoMatchBehavior", redirectRowsToNoMatchOutput);
Inst.SetComponentProperty("SqlCommand", sqlComm);
// MetaData
Inst.AcquireConnections(null);
Inst.ReinitializeMetaData();
Inst.ReleaseConnections();
// Parametrize #2 - Join Columns
IDTSInput100 lkpInput = Comp.InputCollection[0];
IDTSInputColumnCollection100 lkpInputCols = lkpInput.InputColumnCollection;
IDTSVirtualInput100 lkpVirtInput = lkpInput.GetVirtualInput();
IDTSVirtualInputColumnCollection100 lkpVirtInputCols = lkpVirtInput.VirtualInputColumnCollection;
foreach (string columnName in joinColumns)
{
IDTSVirtualInputColumn100 vColumn = lkpVirtInputCols[columnName];
IDTSInputColumn100 inputColumn = Inst.SetUsageType(lkpInput.ID, lkpVirtInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
Inst.SetInputColumnProperty(lkpInput.ID, inputColumn.ID, "JoinToReferenceColumn", columnName);
}
// Parametrize #3 - New Lookup Columns
IDTSOutput100 lookupMatchOutput = Comp.OutputCollection["Lookup Match Output"];
foreach (string srcCol in newColumns.Keys)
{
string newColumnName = newColumns[srcCol];
string description = string.Format("Copy of {0}", srcCol);
IDTSOutputColumn100 outputColumn = Inst.InsertOutputColumnAt(lookupMatchOutput.ID, 0, newColumnName, description);
Inst.SetOutputColumnProperty(lookupMatchOutput.ID, outputColumn.ID, "CopyFromReferenceColumn", srcCol);
}
// Return
return(Comp);
}
