public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
IDTSInputColumn100 inputColumn = null;
IDTSOutput100 output = this.ComponentMetaData.OutputCollection[0];
IDTSOutputColumnCollection100 outputColumnCollection = output.OutputColumnCollection;
IDTSInput100 input = this.ComponentMetaData.InputCollection[0];
IDTSInputColumnCollection100 inputColumnCollection = input.InputColumnCollection;
if (usageType == DTSUsageType.UT_IGNORED)
{
inputColumn = inputColumnCollection.GetInputColumnByLineageID(lineageID);
IDTSOutputColumn100 outputColumn = outputColumnCollection.FindObjectByID(inputColumn.MappedColumnID);
if (outputColumn != null)
{
outputColumnCollection.RemoveObjectByID(outputColumn.ID);
}
inputColumn = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
}
else
{
inputColumn = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
IDTSOutputColumn100 outputColumn = outputColumnCollection.New();
outputColumn.Name = inputColumn.Name;
outputColumn.SetDataTypeProperties(inputColumn.DataType, inputColumn.Length, inputColumn.Precision, inputColumn.Scale, inputColumn.CodePage);
outputColumn.MappedColumnID = inputColumn.LineageID;
inputColumn.MappedColumnID = outputColumn.LineageID;
AddInputColumnProperty(inputColumn, GeoColumnPropName, "True if the column holds geometry objects to be split.", false);
}
return inputColumn;
}
/// <summary>
/// This method parses a friendly Derived Columns expression entered by the user into a Lineage-ID based
/// expression which is required by SSIS, using a regular-expression based parser.
/// Additionally, it will set the Input Column Usage for any columns found in the expression.
/// </summary>
/// <param name="expression">Expression to be parsed.</param>
/// <param name="transformation">Transformation to use for evaluating the lineage IDs</param>
/// <param name="vi">Transformation Virtual Input used to search for input columns.</param>
/// <param name="inputColumnUsageType">DTSUsageType for columns mapped in this expression.</param>
/// <returns>Expression struct with the pre-processed and post-processed expression.</returns>
public static Expression ExpressionCleanerAndInputMapBuilder(string expression, Transformation transformation, IDTSVirtualInput100 vi, DTSUsageType inputColumnUsageType)
{
Expression exp = new Expression();
exp.OriginalExpression = expression;
exp.ProcessedExpression = expression;
exp.FriendlyExpression = expression;
exp.ContainsId = false;
foreach (IDTSVirtualInputColumn100 vcol in vi.VirtualInputColumnCollection)
{
string regexString = String.Format(CultureInfo.CurrentCulture, "(\"(?:[^\"]|(?<=\\\\)\")*\")|(?<vCol>(?<!@\\[?|:)\\[?\\b{0}\\b\\]?)", Regex.Escape(vcol.Name));
var regex = new Regex(regexString, RegexOptions.IgnoreCase | RegexOptions.CultureInvariant);
int groupNumber = regex.GroupNumberFromName("vCol");
var processedEme = new ExpressionMatchEvaluatorStruct(groupNumber, "#" + vcol.LineageID, transformation, vi, inputColumnUsageType, vcol);
var friendlyEme = new ExpressionMatchEvaluatorStruct(groupNumber, vcol.Name, null, null, DTSUsageType.UT_IGNORED, null);
exp.ProcessedExpression = regex.Replace(exp.ProcessedExpression, new MatchEvaluator(processedEme.EvaluateMatch));
exp.FriendlyExpression = regex.Replace(exp.FriendlyExpression, new MatchEvaluator(friendlyEme.EvaluateMatch));
}
if (exp.ProcessedExpression != exp.OriginalExpression)
{
exp.ContainsId = true;
}
return exp;
}
public virtual void SetInputUsageType(string name, DTSUsageType usageType)
{
IDTSVirtualInput90 vi = Component.InputCollection[0].GetVirtualInput();
IDTSVirtualInputColumn90 vcol = vi.VirtualInputColumnCollection[name];
SetInputUsageType(vi, vcol, usageType);
}
/// <summary>
/// Creates a new Input Column
/// </summary>
/// <param name="inputID"></param>
/// <param name="virtualInput"></param>
/// <param name="lineageID"></param>
/// <param name="usageType"></param>
/// <returns></returns>
public override IDTSInputColumn SetUsageType(int inputID, IDTSVirtualInput virtualInput, int lineageID, DTSUsageType usageType)
{
DTSUsageType uType = usageType == DTSUsageType.UT_READWRITE ? DTSUsageType.UT_READONLY : usageType;
IDTSInputColumn col = base.SetUsageType(inputID, virtualInput, lineageID, uType);
return(col);
}
/// <summary>
/// Selexts an InputColumn (to become available in the input buffer)
/// </summary>
/// <param name="lineageID">LineageID of the InputColumn</param>
/// <param name="isSelected">Selection Status of the Column</param>
public IDTSInputColumn SelectInputColumn(int lineageID, bool selected)
{
IDTSInputColumn icol = null;
if (Input != null)
{
DTSUsageType usageType = selected ? DTSUsageType.UT_READONLY : DTSUsageType.UT_IGNORED;
icol = DesignTimeComponent.SetUsageType(Input.ID, VirtualInput, lineageID, usageType);
}
return(icol);
}
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 void MapColumns(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
{
if (dtsUsageType == DTSUsageType.UT_READONLY)
{
foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, dtsUsageType);
}
foreach (IDTSInputColumn100 col in input.InputColumnCollection)
{
columnName = col.Name;
IDTSExternalMetadataColumn100 exCol = input.ExternalMetadataColumnCollection[col.Name];
InstanceDestination.MapInputColumn(input.ID, col.ID, exCol.ID);
}
}
else
{
foreach (IDTSVirtualInputColumn100 vColumn in vInput.VirtualInputColumnCollection)
{
vCol = InstanceDestination.SetUsageType(input.ID, vInput, vColumn.LineageID, dtsUsageType);
columnName = vCol.Name;
IDTSExternalMetadataColumn100 exCol = input.ExternalMetadataColumnCollection[vColumn.Name];
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);
}
#endregion
}
private bool IsExpectedUsageType(string columnName, DTSUsageType usageType)
{
//if (columnName.Equals(FullAddressColumnName)
// || columnName.Equals(Address1ColumnName)
// || columnName.Equals(Address2ColumnName)
// || columnName.Equals(CityColumnName)
// || columnName.Equals(StateColumnName)
// || columnName.Equals(ZipColumnName))
//{
// return usageType == DTSUsageType.UT_READONLY;
//}
//if (columnName.Equals(LatitudeColumnName)
// || columnName.Equals(LongitudeColumnName)
// || columnName.Equals(LocationColumnName))
//{
// return usageType == DTSUsageType.UT_READWRITE;
//}
return(true);
}
private void btnOk_Click(object sender, EventArgs e)
{
List <int> inputLineages = new List <int>();
foreach (TreeNode outputNode in trvOutputColumns.Nodes)
{
foreach (TreeNode node in outputNode.Nodes)
{
MetadataColumn col = node.Tag as MetadataColumn;
if (col != null && col.InputDataColumnLineageID > 0 && !inputLineages.Contains(col.InputDataColumnLineageID))
{
inputLineages.Add(col.InputDataColumnLineageID);
}
}
}
foreach (IDTSVirtualInputColumn iCol in UIHelper.VirtualInput.VirtualInputColumnCollection)
{
DTSUsageType usageType = inputLineages.Contains(iCol.LineageID) ? DTSUsageType.UT_READONLY : DTSUsageType.UT_IGNORED;
UIHelper.DesignTimeComponent.SetUsageType(UIHelper.Input.ID, UIHelper.VirtualInput, iCol.LineageID, usageType);
}
this.Close();
}
public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
if (usageType == DTSUsageType.UT_READWRITE)
{
throw new Exception("The UsageType must be set to Read Only.");
}
return(base.SetUsageType(inputID, virtualInput, lineageID, usageType));
}
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 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;
}
//Override SetUsagetype to set ErrorRowDisposition and TruncationRowDisposition
public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
IDTSInputColumn100 inputColumn = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
if (inputColumn != null)
{
inputColumn.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
inputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
}
return inputColumn;
}
public void AddOutputColumn(string colName, DataType type, string expression, int length, int precision, int scale, int codepage, DTSUsageType inputColumnUsageType)
{
IDTSOutputColumn90 col = _dcCom.OutputCollection[0].OutputColumnCollection.New();
col.Name = colName;
col.Description = colName;
col.ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;
col.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
col.SetDataTypeProperties(type, length, precision, scale, codepage);
col.ExternalMetadataColumnID = 0;
IDTSCustomProperty90 propExpression = col.CustomPropertyCollection.New();
propExpression.Name = "Expression";
propExpression.Value = expression;
IDTSCustomProperty90 propFriendlyExpression = col.CustomPropertyCollection.New();
propFriendlyExpression.Name = "FriendlyExpression";
propFriendlyExpression.Value = expression;
IDTSVirtualInput90 vi = _dcCom.InputCollection[0].GetVirtualInput();
propExpression.Value = this.ExpressionCleanerAndInputMapBuilder(expression, vi, inputColumnUsageType);
}
public string ExpressionCleanerAndInputMapBuilder(string expression, IDTSVirtualInput90 vi, DTSUsageType inputColumnUsageType)
{
foreach (IDTSVirtualInputColumn90 vcol in vi.VirtualInputColumnCollection)
{
Regex regex = new Regex(@"\b" + Regex.Escape(vcol.Name) + @"\b", RegexOptions.IgnoreCase | RegexOptions.CultureInvariant);
//Slow, speed this up with a proper lookup and parse
if (regex.IsMatch(expression))
{
SetInputUsageType(vi, vcol, inputColumnUsageType);
expression = regex.Replace(expression, "#" + vcol.LineageID);
}
}
return(expression);
}
/// <summary>
/// IDTSDesigntimeComponent100 (OK)
/// </summary>
/// <param name="inputID"> </param>
/// <param name="virtualInput"> </param>
/// <param name="lineageID"> </param>
/// <param name="usageType"> </param>
/// <returns> </returns>
public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
var retval = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
// something likely changed, lets update
this.ComponentMetadataWrapper.CreateInputColumnProperties();
return retval;
}
public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
var column = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
if (usageType == DTSUsageType.UT_READWRITE)
{
Utility.AddColumnProperties(column);
}
else
{
Utility.RemoveColumnProperties(column);
}
return(column);
}
public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
IDTSInputColumn100 inp = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
if (inp != null)
{
if (inp.UsageType == DTSUsageType.UT_READWRITE)
{
throw new Exception("You cannot set a column to read write for this destination");
}
}
return(inp);
}
public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
IDTSInputColumn100 inputColumn = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
if (usageType != DTSUsageType.UT_IGNORED)
{
AddInputColumnProperty(inputColumn, TransPropName, "Contains list of transformaion operations to be peformed on objects in this column", "");
}
return(inputColumn);
}
/// <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 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);
}
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, bool forceOverwrite)
{
if (vcol.UsageType != DTSUsageType.UT_READWRITE || forceOverwrite)
{
_instance.SetUsageType(Component.InputCollection[0].ID, vi, vcol.LineageID, usageType);
}
}
public virtual void SetInputUsageType(string name, DTSUsageType usageType)
{
IDTSVirtualInput90 vi = Component.InputCollection[0].GetVirtualInput();
IDTSVirtualInputColumn90 vcol = vi.VirtualInputColumnCollection[name];
SetInputUsageType(vi, vcol, usageType);
}
protected virtual void SetInputUsageType(IDTSVirtualInput100 vi, IDTSVirtualInputColumn100 vcol, DTSUsageType usageType)
{
this.SetInputUsageType(vi, vcol, usageType, false);
}
public virtual void SetInputUsageType(IDTSVirtualInput90 vi, IDTSVirtualInputColumn90 vcol, DTSUsageType usageType)
{
this.SetInputUsageType(vi, vcol, usageType, false);
}
public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
IDTSInputColumn100 inp = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
if (inp != null)
{
if (inp.UsageType == DTSUsageType.UT_READWRITE)
{
throw new Exception("You cannot set a column to read write for this destination");
}
}
return inp;
}
public virtual void SetInputUsageType(IDTSVirtualInput90 vi, IDTSVirtualInputColumn90 vcol, DTSUsageType usageType, bool forceOverwrite)
{
// This will not make a DT_READWRITE column more restrctive :) Only less restrictive
if (vcol.UsageType != DTSUsageType.UT_READWRITE || forceOverwrite)
{
ComponentInstance.SetUsageType(
Component.InputCollection[0].ID,
vi,
vcol.LineageID,
usageType
);
}
}
public string ExpressionCleanerAndInputMapBuilder(string expression, IDTSVirtualInput90 vi, DTSUsageType inputColumnUsageType)
{
foreach (IDTSVirtualInputColumn90 vcol in vi.VirtualInputColumnCollection)
{
Regex regex = new Regex(@"\b" + Regex.Escape(vcol.Name) + @"\b", RegexOptions.IgnoreCase | RegexOptions.CultureInvariant);
//Slow, speed this up with a proper lookup and parse
if (regex.IsMatch(expression))
{
SetInputUsageType(vi,vcol, inputColumnUsageType);
expression = regex.Replace(expression, "#" + vcol.LineageID);
}
}
return expression;
}
public int SetUsageType(Int32 lLineageID, DTSUsageType eUsageType)
{
return _innerObject.SetUsageType(lLineageID, eUsageType);
}
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);
}
public Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSInputColumn100 HostSetUsageType(
IDTSManagedComponentWrapper100 pWrapper, Int32 lInputID, IDTSVirtualInput100 pIDTSVirtualInputObject,
Int32 lLineageID, DTSUsageType eUsageType)
{
return _innerObject.HostSetUsageType(pWrapper, lInputID, pIDTSVirtualInputObject, lLineageID, eUsageType);
}
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 Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSInputColumn100 SetUsageType(Int32 lInputID,
IDTSVirtualInput100 pIDTSVirtualInputObject, Int32 lLineageID, DTSUsageType eUsageType)
{
return _innerObject.SetUsageType(lInputID, pIDTSVirtualInputObject, lLineageID, eUsageType);
}
public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
IDTSInputColumn100 inputColumn = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
if (usageType != DTSUsageType.UT_IGNORED)
{
AddInputColumnProperty(inputColumn, TransPropName, "Contains list of transformaion operations to be peformed on objects in this column", "");
}
return inputColumn;
}
public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
IDTSInputColumn100 inputColumn = null;
IDTSOutput100 output = this.ComponentMetaData.OutputCollection[0];
IDTSOutputColumnCollection100 outputColumnCollection = output.OutputColumnCollection;
IDTSInput100 input = this.ComponentMetaData.InputCollection[0];
IDTSInputColumnCollection100 inputColumnCollection = input.InputColumnCollection;
if (usageType == DTSUsageType.UT_IGNORED)
{
inputColumn = inputColumnCollection.GetInputColumnByLineageID(lineageID);
IDTSOutputColumn100 outputColumn = outputColumnCollection.FindObjectByID(inputColumn.MappedColumnID);
if (outputColumn != null)
{
outputColumnCollection.RemoveObjectByID(outputColumn.ID);
}
inputColumn = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
}
else
{
inputColumn = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
IDTSOutputColumn100 outputColumn = outputColumnCollection.New();
outputColumn.Name = inputColumn.Name;
outputColumn.SetDataTypeProperties(inputColumn.DataType, inputColumn.Length, inputColumn.Precision, inputColumn.Scale, inputColumn.CodePage);
outputColumn.MappedColumnID = inputColumn.LineageID;
inputColumn.MappedColumnID = outputColumn.LineageID;
AddInputColumnProperty(inputColumn, GeoColumnPropName, "True if the column holds geometry objects to be joined together.", false);
AddInputColumnProperty(inputColumn, GroupByColumnPropName, "True if the column holds group by values.", false);
}
return(inputColumn);
}
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 virtual void SetInputUsageType(IDTSVirtualInput90 vi, IDTSVirtualInputColumn90 vcol, DTSUsageType usageType)
{
this.SetInputUsageType(vi, vcol, usageType, false);
}
/// <summary>
/// This method parses a friendly Derived Columns expression entered by the user into a Lineage-ID based
/// expression which is required by SSIS, using a regular-expression based parser.
/// Additionally, it will set the Input Column Usage for any columns found in the expression.
/// </summary>
/// <param name="expression">Expression to be parsed.</param>
/// <param name="transformation">Transformation to use for evaluating the lineage IDs</param>
/// <param name="vi">Transformation Virtual Input used to search for input columns.</param>
/// <param name="inputColumnUsageType">DTSUsageType for columns mapped in this expression.</param>
/// <returns>Expression struct with the pre-processed and post-processed expression.</returns>
public static Expression ExpressionCleanerAndInputMapBuilder(string expression, Transformation transformation, IDTSVirtualInput100 vi, DTSUsageType inputColumnUsageType)
{
Expression exp = new Expression();
exp.OriginalExpression = expression;
exp.ProcessedExpression = expression;
exp.FriendlyExpression = expression;
exp.ContainsId = false;
foreach (IDTSVirtualInputColumn100 vcol in vi.VirtualInputColumnCollection)
{
string regexString = String.Format(CultureInfo.CurrentCulture, "(\"(?:[^\"]|(?<=\\\\)\")*\")|(?<vCol>(?<!@\\[?|:)\\[?\\b{0}\\b\\]?)", Regex.Escape(vcol.Name));
var regex = new Regex(regexString, RegexOptions.IgnoreCase | RegexOptions.CultureInvariant);
int groupNumber = regex.GroupNumberFromName("vCol");
var processedEme = new ExpressionMatchEvaluatorStruct(groupNumber, "#" + vcol.LineageID, transformation, vi, inputColumnUsageType, vcol);
var friendlyEme = new ExpressionMatchEvaluatorStruct(groupNumber, vcol.Name, null, null, DTSUsageType.UT_IGNORED, null);
exp.ProcessedExpression = regex.Replace(exp.ProcessedExpression, new MatchEvaluator(processedEme.EvaluateMatch));
exp.FriendlyExpression = regex.Replace(exp.FriendlyExpression, new MatchEvaluator(friendlyEme.EvaluateMatch));
}
if (exp.ProcessedExpression != exp.OriginalExpression)
{
exp.ContainsId = true;
}
return(exp);
}
protected virtual void SetInputUsageType(IDTSVirtualInput100 vi, IDTSVirtualInputColumn100 vcol, DTSUsageType usageType)
{
this.SetInputUsageType(vi, vcol, usageType, false);
}
/// <summary>
/// IDTSDesigntimeComponent100 (OK)
/// </summary>
/// <param name="inputID"> </param>
/// <param name="virtualInput"> </param>
/// <param name="lineageID"> </param>
/// <param name="usageType"> </param>
/// <returns> </returns>
public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
var retval = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
// something likely changed, lets update
this.ComponentMetadataWrapper.CreateInputColumnProperties();
return(retval);
}
public void AddOutputColumn(string colName, DataType type, string expression, int length, int precision, int scale, int codepage, DTSUsageType inputColumnUsageType)
{
IDTSOutputColumn90 col = _dcCom.OutputCollection[0].OutputColumnCollection.New();
col.Name = colName;
col.Description = colName;
col.ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure;
col.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
col.SetDataTypeProperties(type, length, precision, scale, codepage);
col.ExternalMetadataColumnID = 0;
IDTSCustomProperty90 propExpression = col.CustomPropertyCollection.New();
propExpression.Name = "Expression";
propExpression.Value = expression;
IDTSCustomProperty90 propFriendlyExpression = col.CustomPropertyCollection.New();
propFriendlyExpression.Name = "FriendlyExpression";
propFriendlyExpression.Value = expression;
IDTSVirtualInput90 vi = _dcCom.InputCollection[0].GetVirtualInput();
propExpression.Value = this.ExpressionCleanerAndInputMapBuilder(expression, vi, inputColumnUsageType);
}
//Override SetUsagetype to set ErrorRowDisposition and TruncationRowDisposition
public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
IDTSInputColumn100 inputColumn = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
if (inputColumn != null)
{
inputColumn.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent;
inputColumn.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent;
}
return(inputColumn);
}
private bool IsExpectedUsageType(string columnName, DTSUsageType usageType)
{
//if (columnName.Equals(FullAddressColumnName)
// || columnName.Equals(Address1ColumnName)
// || columnName.Equals(Address2ColumnName)
// || columnName.Equals(CityColumnName)
// || columnName.Equals(StateColumnName)
// || columnName.Equals(ZipColumnName))
//{
// return usageType == DTSUsageType.UT_READONLY;
//}
//if (columnName.Equals(LatitudeColumnName)
// || columnName.Equals(LongitudeColumnName)
// || columnName.Equals(LocationColumnName))
//{
// return usageType == DTSUsageType.UT_READWRITE;
//}
return true;
}
// done
public override IDTSInputColumn100 SetUsageType(int inputID, IDTSVirtualInput100 virtualInput, int lineageID, DTSUsageType usageType)
{
IDTSInputColumn100 inputColumn;
IDTSComponentMetaData100 metadata = this.ComponentMetaData;
switch (usageType)
{
case DTSUsageType.UT_READONLY:
IDTSVirtualInputColumn100 column = virtualInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(lineageID);
if (column.DataType != DataType.DT_BYTES)
{
PostError("Component operates only on bytes input. Other types are forbidden.");
throw new PipelineComponentHResultException(HResults.DTS_E_CANTSETUSAGETYPE);
}
else
{
if (column.Length > 14)
{
PostError("Component accepts a maximum field length of 14.");
throw new PipelineComponentHResultException(HResults.DTS_E_CANTSETUSAGETYPE);
}
else
{
inputColumn = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
IDTSCustomPropertyCollection100 customProperties = inputColumn.CustomPropertyCollection;
IDTSCustomProperty100 customProperty = customProperties.New();
customProperty.Name = "PackedScale"; // do not localize
customProperty.ContainsID = false;
customProperty.Value = 0; // default is zero for scale
IDTSOutputCollection100 outputCollection = metadata.OutputCollection;
IDTSOutput100 output = outputCollection[0];
IDTSOutputColumnCollection100 outputColumnCollection = output.OutputColumnCollection;
// this will generate a unique name, because upstream component names can't have dots int them
IDTSOutputColumn100 newColumn = base.InsertOutputColumnAt(output.ID, outputColumnCollection.Count,
inputColumn.UpstreamComponentName + "." + inputColumn.Name + ".Decimal", "");
newColumn.SetDataTypeProperties(DataType.DT_DECIMAL, 0, 0, 0, 0);
customProperties = newColumn.CustomPropertyCollection;
customProperty = customProperties.New();
customProperty.Name = "InputColumnID"; // do not localize
// support cut and paste
customProperty.ContainsID = true;
customProperty.Value = inputColumn.ID;
return inputColumn;
}
}
case DTSUsageType.UT_READWRITE:
PostError("Component requires that input columns be marked read only.");
throw new PipelineComponentHResultException(HResults.DTS_E_CANTSETUSAGETYPE);
case DTSUsageType.UT_IGNORED:
IDTSInputCollection100 inputCollection = metadata.InputCollection;
IDTSInput100 input = inputCollection[0];
IDTSInputColumnCollection100 inputColumnCollection = input.InputColumnCollection;
inputColumn = inputColumnCollection.GetInputColumnByLineageID(lineageID);
this.OnDeletingInputColumn(inputID, inputColumn.ID);
inputColumn = base.SetUsageType(inputID, virtualInput, lineageID, usageType);
return inputColumn;
default:
throw new PipelineComponentHResultException(HResults.DTS_E_CANTSETUSAGETYPE);
}
}
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));
}
public virtual void SetInputUsageType(IDTSVirtualInput90 vi, IDTSVirtualInputColumn90 vcol, DTSUsageType usageType, bool forceOverwrite)
{
// This will not make a DT_READWRITE column more restrctive :) Only less restrictive
if (vcol.UsageType != DTSUsageType.UT_READWRITE || forceOverwrite)
{
ComponentInstance.SetUsageType(
Component.InputCollection[0].ID,
vi,
vcol.LineageID,
usageType
);
}
}
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));
}