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;
}
//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 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);
}
private void BuildInputAndCleanOutputColumns()
{
// Remove the existing columns from the output in order to prevent us from adding duplicate columns
foreach (IDTSOutput100 output in ComponentMetaData.OutputCollection)
{
// We can't simply call RemoveAll on the error output column collection, as there
// are two columns (ErrorColumn and ErrorCode) that we cannot remove as it throws an exception if we try
if (output.IsErrorOut)
{
List <IDTSOutputColumn100> columns = new List <IDTSOutputColumn100>();
for (int i = 0; i < output.OutputColumnCollection.Count; i++)
{
columns.Add(output.OutputColumnCollection[i]);
}
string[] errorColumns = new string[] { "ErrorColumn", "ErrorCode" };
IEnumerable <int> columnIdsToRemove = columns.Where(column => !errorColumns.Contains(column.Name)).Select(column => column.ID);
foreach (int columnIdToRemove in columnIdsToRemove)
{
output.OutputColumnCollection.RemoveObjectByID(columnIdToRemove);
}
}
else
{
List <IDTSOutputColumn100> columns = new List <IDTSOutputColumn100>();
for (int i = 0; i < output.OutputColumnCollection.Count; i++)
{
columns.Add(output.OutputColumnCollection[i]);
}
string[] errorColumns = new string[] { "_Response_" };
IEnumerable <int> columnIdsToRemove = columns.Where(column => !errorColumns.Contains(column.Name)).Select(column => column.ID);
foreach (int columnIdToRemove in columnIdsToRemove)
{
output.OutputColumnCollection.RemoveObjectByID(columnIdToRemove);
}
}
}
// Create input collection from virtual inputs
for (int i = 0; i < ComponentMetaData.InputCollection.Count; i++)
{
ComponentMetaData.InputCollection[i].InputColumnCollection.RemoveAll();
IDTSVirtualInput100 input = ComponentMetaData.InputCollection[i].GetVirtualInput();
foreach (IDTSVirtualInputColumn100 vcol in input.VirtualInputColumnCollection)
{
input.SetUsageType(vcol.LineageID, DTSUsageType.UT_READONLY);
}
}
}
public override void OnInputPathDetached(int inputID)
{
IDTSOutput100 defaultOutput = ComponentMetaData.OutputCollection[0];
IDTSInput100 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);
IDTSVirtualInput100 vInput = input.GetVirtualInput();
foreach (IDTSVirtualInputColumn100 vCol in vInput.VirtualInputColumnCollection)
{
defaultOutput.OutputColumnCollection.RemoveObjectByIndex(input.Name + "." + vCol.Name);
}
input.InputColumnCollection.RemoveAll();
}
protected void LoadVirtualInput()
{
Debug.Assert(this.componentMetadata != null);
IDTSInputCollection100 inputCollection = this.componentMetadata.InputCollection;
if (inputCollection.Count > 0)
{
IDTSInput100 input = inputCollection[0];
this.virtualInput = input.GetVirtualInput();
}
}
public override void ReinitializeMetaData()
{
IDTSInput100 _profinput = ComponentMetaData.InputCollection["Component Input"];
_profinput.InputColumnCollection.RemoveAll();
IDTSVirtualInput100 vInput = _profinput.GetVirtualInput();
foreach (IDTSVirtualInputColumn100 vCol in vInput.VirtualInputColumnCollection)
{
this.SetUsageType(_profinput.ID, vInput, vCol.LineageID, DTSUsageType.UT_READONLY);
}
}
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.");
}
}
}
public SSISFlatFileDestination(FlatFileDestination fdst, MainPipe pipe, IDTSComponentMetaData100 src, int outputID, ConnectionManager cm)
: base(pipe, "Flat File Destination", outputID)
{
cm.Name = String.Format(CultureInfo.InvariantCulture, "FlatFile Destination Connection Manager {0}", outputID);
//Create a new FlatFileDestination component
IDTSComponentMetaData100 comp = this.MetadataCollection;
CManagedComponentWrapper dcomp = comp.Instantiate();
foreach (KeyValuePair <string, object> prop in fdst.CustomProperties.CustomPropertyCollection.InnerArrayList)
{
dcomp.SetComponentProperty(prop.Key, prop.Value);
}
/*Specify the connection manager for Src.The Connections class is a collection of the connection managers that have been added to that package and are available for use at run time*/
if (comp.RuntimeConnectionCollection.Count > 0)
{
comp.RuntimeConnectionCollection[0].ConnectionManagerID = cm.ID;
comp.RuntimeConnectionCollection[0].ConnectionManager = DtsConvert.GetExtendedInterface(cm);
}
this.Reinitialize(dcomp);
//Create datatype converter if needed
Dictionary <string, int> converted = new Dictionary <string, int>();
IDTSVirtualInput100 vInput = src.InputCollection[0].GetVirtualInput();
if (this.needDataTypeChange(vInput, comp.InputCollection[0]))
{
//create the destination column collection
Dictionary <string, MyColumn> exColumns = new Dictionary <string, MyColumn>();
foreach (IDTSExternalMetadataColumn100 exColumn in comp.InputCollection[0].ExternalMetadataColumnCollection)
{
MyColumn col = new MyColumn();
col.Name = exColumn.Name;
col.DataType = exColumn.DataType;
col.Length = exColumn.Length;
col.Precision = exColumn.Precision;
col.Scale = exColumn.Scale;
col.CodePage = exColumn.CodePage;
exColumns.Add(exColumn.Name, col);
}
SSISDataConverter ssisdc = new SSISDataConverter(pipe, src, outputID, exColumns);
src = ssisdc.MetadataCollection;
converted = ssisdc.ConvertedColumns;
outputID = 0;
}
this.ConnectComponents(src, outputID);
this.MatchInputColumns(converted, 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);
}
/// <summary>
/// Does the SSIS input column collection contain an input column without a match in the oclumn configuration list?
/// </summary>
/// <param name="vInput">SSIS virtual input</param>
/// <param name="componentMetaData">SSIS component metadata</param>
/// <returns>Does the SSIS input column collection contain an input column without a match in the oclumn configuration list?</returns>
private bool ContainsInputWithoutColumnConfig(IDTSVirtualInput100 vInput, IsagEvents events)
{
for (int i = 0; i < vInput.VirtualInputColumnCollection.Count; i++)
{
if (GetColumnConfigByInputColumnName(vInput.VirtualInputColumnCollection[i].Name) == null)
{
events.Fire(IsagEvents.IsagEventType.Error, "The input contains at least one column that is not assigned to a column of the Mapping!");
return(true);
}
}
return(false);
}
/// <summary>
/// Refresh Metatada button, corrects inputs and outputs
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnRefreshMetadata_Click(object sender, EventArgs e)
{
//Forces meadata refresh
for (int i = 0; i < this.metaData.InputCollection.Count; i++)
{
this.metaData.InputCollection[i].InputColumnCollection.RemoveAll();
IDTSVirtualInput100 input = this.metaData.InputCollection[i].GetVirtualInput();
foreach (IDTSVirtualInputColumn100 vcol in input.VirtualInputColumnCollection)
{
input.SetUsageType(vcol.LineageID, DTSUsageType.UT_READONLY);
}
}
if (this.metaData.CustomPropertyCollection["Entity"].Value != null)
{
Item entity = (Item)CRMSSIS.CRMCommon.JSONSerialization.Deserialize <Item>(this.metaData.CustomPropertyCollection["Entity"].Value.ToString());
IDTSInput100 input = this.metaData.InputCollection[0];
int connectionId = findConnectionId();
if (connectionId > -1)
{
try
{
string _connectionstring = (string)connectionService.GetConnections()[connectionId].AcquireConnection(null);
IOrganizationService service = CRMSSIS.CRMCommon.CRM.Connect(_connectionstring);
RetrieveEntityResponse retrieveEntityResponse = CRMSSIS.CRMCommon.CRM.RetrieveEntityRequestMetadata(service, entity.Text);
this.metaData.CustomPropertyCollection["Entity"].Value = CRMSSIS.CRMCommon.JSONSerialization.Serialize <Item>(new Item(entity.Text, entity.Text, retrieveEntityResponse.EntityMetadata.Attributes));
int operation = (int)EnumEx.GetValueFromDescription <Operations>(cbOperation.SelectedValue.ToString());
m.RefreshMapping(input, entity.Metadata, retrieveEntityResponse.EntityMetadata.Attributes, operation);
}
catch (Exception ex)
{
throw ex;
}
}
}
else
{
MessageBox.Show("Entity must be set", "Warning", MessageBoxButtons.OK);
}
}
/// <summary>
/// Reinitialize metadata in case input cvolumns are invalid
/// </summary>
public override void ReinitializeMetaData()
{
if (!inputColumnsValid)
{
IDTSInput input = ComponentMetaData.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
foreach (IDTSInputColumn col in input.InputColumnCollection)
{
input.InputColumnCollection.RemoveObjectByID(col.ID);
}
inputColumnsValid = true;
}
}
private void ReplaceExistingColumn(DerivedColumn column)
{
if (column.ReplaceExisting)
{
IDTSVirtualInput100 vi = _component.InputCollection[0].GetVirtualInput();
IDTSInputColumn100 col;
SetInputUsageType(vi, vi.VirtualInputColumnCollection[column.Name], DTSUsageType.UT_READWRITE);
col = _component.InputCollection[0].InputColumnCollection[column.Name];
col.CustomPropertyCollection["Expression"].Value = this.ExpressionCleanerAndInputMapBuilder(column.Expression, vi, DTSUsageType.UT_READONLY);
col.CustomPropertyCollection["FriendlyExpression"].Value = column.Expression;
}
}
public override void OnInputPathAttached(int inputID)
{
IDTSOutput100 defaultOutput = ComponentMetaData.OutputCollection[0];
IDTSInput100 input = ComponentMetaData.InputCollection.GetObjectByID(inputID);
IDTSVirtualInput100 vInput = input.GetVirtualInput();
foreach (IDTSVirtualInputColumn100 vCol in vInput.VirtualInputColumnCollection)
{
IDTSOutputColumn100 outCol = defaultOutput.OutputColumnCollection.New();
outCol.Name = input.Name + "." + vCol.Name;
outCol.SetDataTypeProperties(vCol.DataType, vCol.Length, vCol.Precision, vCol.Scale, vCol.CodePage);
SetUsageType(inputID, vInput, vCol.LineageID, DTSUsageType.UT_READONLY);
}
}
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 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));
}
public void AssociateWithInputColumn(string inputColumnName)
{
IDTSInput100 input = ParentComponent.ComponentMetaData.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
ParentComponent.DesignTimeComponent.SetUsageType(
input.ID,
vInput,
vInput.VirtualInputColumnCollection[inputColumnName].LineageID,
DTSUsageType.UT_READONLY
);
IDTSInputColumn100 inputColumn = Input.InputColumnCollection[inputColumnName];
inputColumn.ExternalMetadataColumnID = ID;
}
/// <summary>
/// Validate configuration
/// </summary>
/// <param name="componentMetaData">SSIS component metadata</param>
/// <returns>Configuration contains error that cann be corrected?</returns>
public bool IsValid(IDTSComponentMetaData100 componentMetaData, IsagEvents events)
{
IDTSInput100 input = componentMetaData.InputCollection[Constants.INPUT_NAME];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
WarnIfScdIsNotValid(events);
WarnIfMoreThanOneKeyIsSelected(events);
ContainsUnusedColumns(events);
IsKeyMissing(events);
InsertOrUpdateAutoIdColumn(events);
AreConnectionManagersValid(componentMetaData, events);
ContainsDuplicateOutputColumn(events);
return(!ContainsWrongUsageType(vInput.VirtualInputColumnCollection, events) && AreColumnNamesAndDataTypesValid(input, events) &&
!ContainsInputWithoutColumnConfig(vInput, events) && !ContainsColumnConfigWithoutOutput(events));
}
/// <summary>
/// React on input path attached
/// </summary>
/// <param name="inputID">ID of the SSIS input</param>
public override void OnInputPathAttached(int inputID)
{
base.OnInputPathAttached(inputID);
IDTSVirtualInput100 virtualInput = ComponentMetaData.InputCollection[Constants.INPUT_NAME].GetVirtualInput();
//set all input column to readwrite
for (int i = 0; i < virtualInput.VirtualInputColumnCollection.Count; i++)
{
virtualInput.SetUsageType(virtualInput.VirtualInputColumnCollection[i].LineageID, DTSUsageType.UT_READWRITE);
}
// load (will update custom input column properties) & save custom properties
InitializeProperties();
_isagCustomProperties.Save(ComponentMetaData);
}
internal IDTSInputColumn100 SetInputColumnDTSUsageType(IDTSInput100 input, string columnName, UsageType dtsUsageType)
{
// keep track of hte columns whose usage type is set to ut_readwrite...for these cols, we want to prevent a change to ut_readonly
if (dtsUsageType == UsageType.UT_READWRITE)
{
_readWriteCols.Add(columnName);
}
IDTSVirtualInput100 virtualInput = input.GetVirtualInput();
IDTSInputColumn100 inputColumn = DesignTimeComponent.SetUsageType(
input.ID,
virtualInput,
virtualInput.VirtualInputColumnCollection[columnName].LineageID,
DtsUtility.EnumAToEnumB <UsageType, DTSUsageType>(dtsUsageType)
);
return(inputColumn);
}
public SSISSharePointDestination(SharePointDestination spdst, MainPipe pipe, IDTSComponentMetaData100 src, int outputID)
: base(pipe, "SharePoint List Destination", outputID, "Microsoft.Samples.SqlServer.SSIS.SharePointListAdapters.SharePointListDestination, SharePointListAdapters, Version=1.2016.0.0, Culture=neutral, PublicKeyToken=f4b3011e1ece9d47")
{
//Create a new SharePointDestination component
IDTSComponentMetaData100 comp = this.MetadataCollection;
CManagedComponentWrapper dcomp = comp.Instantiate();
foreach (KeyValuePair <string, object> prop in spdst.CustomProperties.CustomPropertyCollection.InnerArrayList)
{
dcomp.SetComponentProperty(prop.Key, prop.Value);
}
this.Reinitialize(dcomp);
//Create datatype converter if needed
Dictionary <string, int> converted = new Dictionary <string, int>();
IDTSVirtualInput100 vInput = src.InputCollection[0].GetVirtualInput();
if (this.needDataTypeChange(vInput, comp.InputCollection[0]))
{
//create the destination column collection
Dictionary <string, MyColumn> exColumns = new Dictionary <string, MyColumn>();
foreach (IDTSExternalMetadataColumn100 exColumn in comp.InputCollection[0].ExternalMetadataColumnCollection)
{
MyColumn col = new MyColumn();
col.Name = exColumn.Name;
col.DataType = exColumn.DataType;
col.Length = exColumn.Length;
col.Precision = exColumn.Precision;
col.Scale = exColumn.Scale;
col.CodePage = exColumn.CodePage;
exColumns.Add(exColumn.Name, col);
}
SSISDataConverter ssisdc = new SSISDataConverter(pipe, src, outputID, exColumns);
src = ssisdc.MetadataCollection;
converted = ssisdc.ConvertedColumns;
outputID = 0;
}
this.ConnectComponents(src, outputID);
this.MatchInputColumns(converted, false);
}
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));
}
//Design Time - method to autocorrect VS_NEEDSNEWMETADATA error
public override void ReinitializeMetaData()
{
if (!areInputColumnsValid)
{
IDTSInput100 input = ComponentMetaData.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
foreach (IDTSInputColumn100 column in input.InputColumnCollection)
{
IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(column.LineageID);
if (vColumn == null)
{
input.InputColumnCollection.RemoveObjectByID(column.ID);
}
}
areInputColumnsValid = true;
}
}
private void SetPartitionFunctionInput(string ColumnName)
{
IDTSComponentMetaData100 comp = MetadataCollection;
CManagedComponentWrapper dcomp = comp.Instantiate();
IDTSInput100 input = comp.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
IDTSVirtualInputColumnCollection100 vColumns = vInput.VirtualInputColumnCollection;
int inputColId = FindVirtualInputColumnId(vColumns, ColumnName);
if (inputColId == 0)
{
//the column wasn't found if the Id is 0, so we'll print out a message and skip this row.
_logger.Information("DE Could not find partition function input column {ColName} in the source.", ColumnName);
}
else
{
dcomp.SetUsageType(input.ID, vInput, inputColId, DTSUsageType.UT_READONLY);
}
}
public IDTSOutput100 ModifyComp_CondSplit_AddOut(IDTSComponentMetaData100 Comp,
string OutName,
int OutEvalOrder,
string OutExpression,
string OutDesc = "SampleDescription"
)
{
CManagedComponentWrapper Inst = Comp.Instantiate();
// We need to set a column's usage type before we can use it in an expression.
// The code here will make all of the input columns available, but we could also
// restrict it to just the columns that we need in the conditional split expression(s).
IDTSInput100 splitInput = Comp.InputCollection[0];
IDTSVirtualInput100 splitVInput = splitInput.GetVirtualInput();
IDTSInputColumnCollection100 splitInputCols = splitInput.InputColumnCollection;
IDTSVirtualInputColumnCollection100 splitVInputCols = splitVInput.VirtualInputColumnCollection;
foreach (IDTSVirtualInputColumn100 vCol in splitVInputCols)
{
Inst.SetUsageType(splitInput.ID, splitVInput, vCol.LineageID, DTSUsageType.UT_READONLY);
}
// Parametrize
IDTSOutput100 splitOutput = Inst.InsertOutput(DTSInsertPlacement.IP_BEFORE, Comp.OutputCollection[0].ID);
splitOutput.Name = OutName;
splitOutput.Description = OutDesc;
splitOutput.IsErrorOut = false;
// Note: You will get an exception if you try to set these properties on the Default Output.
Inst.SetOutputProperty(splitOutput.ID, "EvaluationOrder", OutEvalOrder);
Inst.SetOutputProperty(splitOutput.ID, "FriendlyExpression", OutExpression);
Inst.AcquireConnections(null);
Inst.ReinitializeMetaData();
Inst.ReleaseConnections();
return(splitOutput);
}
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 void SSISPhoneLibDialog_Load(object sender, EventArgs e)
{
chkInputColumn.Visible = false;
chkInputColumn.Items.Clear();
IDTSVirtualInput100 virtualInput = ComponentMetaData.InputCollection[0].GetVirtualInput();
_populatingList = true;
PopulatePhoneNumberColumn(virtualInput);
PopulatePhoneNumberCountryIsoColumn(virtualInput);
if (ComponentMetaData.CustomPropertyCollection[Constants.PhoneNumberIsoCodeColumn] != null)
{
txtIso2Default.Text = (string)ComponentMetaData.CustomPropertyCollection[Constants.PhoneNumberIsoCodeColumn].Value;
}
_populatingList = false;
chkInputColumn.Visible = true;
}
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);
}
}
}
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);
}
}
/// <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;
}
// 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);
}
}
internal BipsProxyIDTSVirtualInput100(IDTSVirtualInput100 innerObject)
{
_innerObject = innerObject;
}
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 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;
}
protected virtual void SetInputUsageType(IDTSVirtualInput100 vi, IDTSVirtualInputColumn100 vcol, DTSUsageType usageType)
{
this.SetInputUsageType(vi, vcol, usageType, false);
}
/// <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;
}
}
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);
}
//TODO clean or optimze the below code
protected string ExpressionCleanerAndInputMapBuilder(string expression, IDTSVirtualInput100 vi, DTSUsageType inputColumnUsageType)
{
foreach (IDTSVirtualInputColumn100 vcol in vi.VirtualInputColumnCollection)
{
Regex regex = new Regex(@"\[\b" + Regex.Escape(vcol.Name) + @"\b\]", RegexOptions.IgnoreCase | RegexOptions.CultureInvariant);
if (regex.IsMatch(expression))
{
SetInputUsageType(vi, vcol, inputColumnUsageType);
expression = regex.Replace(expression, "#" + vcol.LineageID);
}
else
{
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 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 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 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);
}
//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 Microsoft.SqlServer.Dts.Pipeline.Wrapper.IDTSInputColumn100 SetUsageType(Int32 lInputID,
IDTSVirtualInput100 pIDTSVirtualInputObject, Int32 lLineageID, DTSUsageType eUsageType)
{
return _innerObject.SetUsageType(lInputID, pIDTSVirtualInputObject, lLineageID, eUsageType);
}
protected void LoadVirtualInput()
{
Debug.Assert(this.componentMetadata != null);
IDTSInputCollection100 inputCollection = this.componentMetadata.InputCollection;
if (inputCollection.Count > 0)
{
IDTSInput100 input = inputCollection[0];
this.virtualInput = input.GetVirtualInput();
}
}
