/// <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;
}
}
protected virtual void MatchInputColumns(Dictionary <string, int> converted, bool needschema)
{
IDTSComponentMetaData100 comp = this.MetadataCollection;
CManagedComponentWrapper dcomp = comp.Instantiate();
IDTSInput100 input = comp.InputCollection[0];
IDTSVirtualInput100 vInput = input.GetVirtualInput();
IDTSVirtualInputColumnCollection100 vColumns = vInput.VirtualInputColumnCollection;
IDTSExternalMetadataColumnCollection100 exColumns = input.ExternalMetadataColumnCollection;
if (exColumns != null && exColumns.Count > 0)
{
bool hasMatch = false;
foreach (IDTSExternalMetadataColumn100 exColumn in exColumns)
{
int inputColId;
if (converted.ContainsKey(exColumn.Name.ToLower()))
{
inputColId = (int)converted[exColumn.Name.ToLower()];
}
else
{
inputColId = FindVirtualInputColumnId(vColumns, exColumn.Name);
}
if (inputColId == 0)
{
//the column wasn't found if the Id is 0, so we'll print out a message and skip this row.
PrintOutput.PrintToOutput("DE could not map external column " + exColumn.Name + ". Skipping column.", DERun.Debug);
}
else
{
// create input column
IDTSInputColumn100 vCol = dcomp.SetUsageType(input.ID, vInput, inputColId, DTSUsageType.UT_READONLY);
// and then we'll map it to the input row.
dcomp.MapInputColumn(input.ID, vCol.ID, exColumn.ID);
hasMatch = true;
}
}
if (!hasMatch)
{
throw new InvalidArgumentException("Unable to map input to destination");
}
}
//if output schema is required and not provided
else if (needschema)
{
//PrintOutput.PrintToError("No destination columns available");
throw new InvalidArgumentException("No destination columns available");
}
//otherwise use virtual inputs
else
{
foreach (IDTSVirtualInputColumn100 vColumn in vColumns)
{
// create input column for all virtual input columns
dcomp.SetUsageType(input.ID, vInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
}
}
}
void choosecolumnspage_GetAvailableColumns(object sender, AvailableColumnsArgs args)
{
Debug.Assert(this.VirtualInput != null, "Virtual Input is not valid.");
this.ClearErrors();
try
{
IDTSVirtualInputColumnCollection100 virtualInputColumnCollection = this.VirtualInput.VirtualInputColumnCollection;
foreach (IDTSVirtualInputColumn100 virtualInputColumn in virtualInputColumnCollection)
{
AvailableColumnElement element = new AvailableColumnElement();
element.Selected = virtualInputColumn.UsageType != DTSUsageType.UT_IGNORED;
element.AvailableColumn = new DataFlowElement(virtualInputColumn.Name, virtualInputColumn);
args.AvailableColumnCollection.Add(element);
}
}
catch (Exception ex)
{
this.ReportErrors(ex);
}
}
public SourceAdvancedUI(Variables vars, IServiceProvider sp, IDTSVirtualInputColumnCollection100 virtualInputs, int metadataVersion = 0, int attributeVersion = 0)
{
_vars = vars;
_sp = sp;
_inputs = virtualInputs;
_sourceView = new SourceView(_vars, _sp, _inputs);
_sourceView.Visible = false;
_columnView = new ColumnView(_inputs);
_columnView.Visible = false;
_advancedView = new AdvancedView();
_advancedView.Visible = false;
InitializeComponent();
mainPanel.Controls.Add(_sourceView);
_sourceView.Dock = DockStyle.Fill;
mainPanel.Controls.Add(_columnView);
_columnView.Dock = DockStyle.Fill;
mainPanel.Controls.Add(_advancedView);
_advancedView.Dock = DockStyle.Fill;
menulist.SelectedIndex = 0;
_metadataVersion = metadataVersion;
_attributeVersion = attributeVersion;
labelComponentVersion.Text = "" + _attributeVersion;
labelMetadataVersion.Text = "" + _metadataVersion;
}
public IDTSComponentMetaData100 AddComp_Sort(string ComponentName,
IDTSOutput100 outCols,
Dictionary <string, bool> sortColumns
)
{
// Create
IDTSComponentMetaData100 Comp = dmp.ComponentMetaDataCollection.New();
Comp.ComponentClassID = "Microsoft.Sort";
// Instantiate
CManagedComponentWrapper Inst = Comp.Instantiate();
Inst.ProvideComponentProperties();
Comp.Name = ComponentName;
Comp.Description = "Dodany Sort";
// Connect
IDTSPath100 pth = dmp.PathCollection.New();
pth.AttachPathAndPropagateNotifications(outCols, Comp.InputCollection[0]);
// Parametrize
IDTSInput100 lkpInput = Comp.InputCollection[0];
IDTSInputColumnCollection100 lkpInputCols = lkpInput.InputColumnCollection;
IDTSVirtualInput100 lkpVirtInput = lkpInput.GetVirtualInput();
IDTSVirtualInputColumnCollection100 lkpVirtInputCols = lkpVirtInput.VirtualInputColumnCollection;
// Parametrize #2 - Join Columns
int i = 1;
foreach (IDTSVirtualInputColumn100 vColumn in lkpVirtInputCols)
{
IDTSInputColumn100 inputColumn = Inst.SetUsageType(lkpInput.ID, lkpVirtInput, vColumn.LineageID, DTSUsageType.UT_READONLY);
foreach (var colKVP in sortColumns)
{
if (vColumn.Name == colKVP.Key)
{
Inst.SetInputColumnProperty(lkpInput.ID, inputColumn.ID, "NewComparisonFlags", 0);
Inst.SetInputColumnProperty(lkpInput.ID, inputColumn.ID, "NewSortKeyPosition", colKVP.Value ? i: -i);
i++;
}
}
}
// Return
return(Comp);
}
//Loop through the Virtual Input column Collection, and see if one matches the name
protected int FindVirtualInputColumnId(IDTSVirtualInputColumnCollection100 in_ColumnCollection, string in_columnName)
{
in_columnName = in_columnName.ToLower();
foreach (IDTSVirtualInputColumn100 inputColumn in in_ColumnCollection)
{
if (inputColumn.Name.ToLower() == in_columnName)
{
return(inputColumn.LineageID);
}
}
return(0);
}
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.");
}
}
}
/// <summary>
/// Are all columns usage types set to readonly?
/// </summary>
/// <param name="vInputColumnCollection">SSIS virtual input column collection</param>
/// <param name="componentMetaData">SSIS component metadata</param>
/// <returns>Are all columns usage types set to readonly?</returns>
private bool ContainsWrongUsageType(IDTSVirtualInputColumnCollection100 vInputColumnCollection, IsagEvents events)
{
for (int i = 0; i < vInputColumnCollection.Count; i++)
{
if (vInputColumnCollection[i].UsageType != DTSUsageType.UT_READONLY)
{
events.Fire(IsagEvents.IsagEventType.Error, "The UsageType of all input columns has to be ReadOnly!");
return(true);
}
}
return(false);
}
public ColumnView(IDTSVirtualInputColumnCollection100 inputs)
{
_inputs = inputs;
InitializeComponent();
// Add input columns as selectable output. As default, all of them will be selected.
foreach (IDTSVirtualInputColumn100 inputCol in _inputs)
{
int index = inputsCb.Items.Add(inputCol.Name, CheckState.Checked);
}
(uiIOGrid.Columns["OutColumnType"] as DataGridViewComboBoxColumn).DataSource = Enum.GetNames(typeof(JsonTypes));
uiRootType.DataSource = Enum.GetNames(typeof(RootType));
}
/// <summary>
/// Fills dft column lists (used as itemlists for comboboxes)
/// </summary>
private void PopulateDftColumnItemlists()
{
//DFT Names
cmbValidparameter.Items.Clear();
cmbMatchparameter.Items.Clear();
IDTSVirtualInputColumnCollection100 vInputCols = _metadata.InputCollection[Constants.INPUT_NAME].GetVirtualInput().VirtualInputColumnCollection;
cmbMatchparameter.Items.Add("");
_dftColumnList.Clear();
foreach (IDTSVirtualInputColumn100 col in vInputCols)
{
cmbValidparameter.Items.Add(col.Name);
cmbMatchparameter.Items.Add(col.Name);
_dftColumnList.Add(col.Name);
}
}
public void GenerateColumnForVirtualInput(ConnectionManager conMgrDestination,
IDTSComponentMetaData100 flatFileDestination, string ColumnDelimeter, bool UniCode)
{
// Add columns to the FlatFileConnectionManager
wrap.IDTSConnectionManagerFlatFile100 fileConnection = null;
fileConnection = conMgrDestination.InnerObject as wrap.IDTSConnectionManagerFlatFile100;
fileConnection.Unicode = UniCode;
DtsConvert.GetExtendedInterface(conMgrDestination);
if (fileConnection == null)
{
throw new Exception("Invalid file connection");
}
// Get the upstream columns
IDTSVirtualInputColumnCollection100 vColumns = flatFileDestination.InputCollection[0].GetVirtualInput().VirtualInputColumnCollection;
for (int cols = 0; cols < vColumns.Count; cols++)
{
wrap.IDTSConnectionManagerFlatFileColumn100 col = fileConnection.Columns.Add();
// If this is the last column, set the delimiter to CRLF.
// Otherwise keep the delimiter as ",".
if (cols == vColumns.Count - 1)
{
col.ColumnDelimiter = "\r\n";
}
else
{
if (ColumnDelimeter == "\\t" || ColumnDelimeter == "Tab {t}")
{
col.ColumnDelimiter = "\t";
}
else
{
col.ColumnDelimiter = ColumnDelimeter;
}
col.ColumnDelimiter = "\t";
}
col.ColumnType = "Delimited";
col.DataType = vColumns[cols].DataType;
col.DataPrecision = vColumns[cols].Precision;
col.DataScale = vColumns[cols].Scale;
wrap.IDTSName100 name = col as wrap.IDTSName100;
name.Name = vColumns[cols].Name;
}
}
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 SourceView(Variables vars, IServiceProvider sp, IDTSVirtualInputColumnCollection100 inputs)
{
_sp = sp;
_vars = vars;
_inputs = inputs;
_tmpParams = new List <HTTPParameter>();
_tmpHeaders = new List <HTTPParameter>();
InitializeComponent();
foreach (IDTSVirtualInputColumn100 i in inputs)
{
inputLaneCb.Items.Add(i.Name);
}
// Preselect the item with index = 0
if (_inputs.Count > 0)
{
inputLaneCb.SelectedIndex = 0;
}
}
public IDictionary <int, int> PropagateInputColumns(IDTSExternalMetadataColumnCollection100 exColumns)
{
Dictionary <int, int> converted = new Dictionary <int, int>();
IDTSComponentMetaData100 comp = 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 (IDTSExternalMetadataColumn100 exColumn in exColumns)
{
int vColumnID = FindVirtualInputColumnId(vColumns, exColumn.Name);
if (vColumnID != 0)
{
//do type conversion
IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(vColumnID);
if (exColumn.DataType != vColumn.DataType ||
vColumn.Length != exColumn.Length ||
exColumn.Precision != vColumn.Precision ||
exColumn.Scale != vColumn.Scale)
{
dcomp.SetUsageType(input.ID, vInput, vColumnID, DTSUsageType.UT_READONLY);
IDTSOutputColumn100 oColumn = dcomp.InsertOutputColumnAt(output.ID, 0, exColumn.Name, String.Empty);
//IDTSOutputColumn100 oColumn = output.OutputColumnCollection.New();
//oColumn.Name = exColumn.Name;
oColumn.SetDataTypeProperties(exColumn.DataType, exColumn.Length, exColumn.Precision, exColumn.Scale, exColumn.CodePage);
oColumn.ExternalMetadataColumnID = 0;
oColumn.MappedColumnID = 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;
dcomp.SetOutputColumnProperty(
output.ID,
oColumn.ID,
"SourceInputColumnLineageID",
vColumnID);
dcomp.SetOutputColumnProperty(
output.ID,
oColumn.ID,
"FastParse",
false);
//set of derived columns
converted.Add(exColumn.ID, oColumn.LineageID);
}
//else
//{
// m_converted.Add(exColumn.ID, 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.");
}
}
return(converted);
}
/// <summary>
/// The mappingGridView aims at enabling the user to define column mappings
/// from source to target (the MERGE command is created automatically in the background).
/// This method populates this table. The source columns are populated using
/// the InputColumnCollection of the input.
/// The target columns are populated using ExternalMetadataColumnCollection.
/// Before the target columns are populated, ReinitializeMetadata() is called
/// to set up ExternalMetadataColumnCollection.
/// </summary>
private void populateMappingGridView()
{
// The table-name should have been just set
// Therefore, call ReinitializeMetadata to setup the external metadata columns
// Which will be used to display the destination columns
// and to set up the column mapping
CManagedComponentWrapper pipelineComponent = this.component_.Instantiate();
pipelineComponent.AcquireConnections(null);
pipelineComponent.ReinitializeMetaData();
pipelineComponent.ReleaseConnections();
needsNewMetaData_ = false;
IDTSInput100 input = this.component_.InputCollection[0];
IDTSVirtualInput100 virtualInput = input.GetVirtualInput();
IDTSVirtualInputColumnCollection100 virtualColumns = virtualInput.VirtualInputColumnCollection;
IDTSExternalMetadataColumnCollection100 externalColumns = input.ExternalMetadataColumnCollection;
// Initialize data structures for creating the MERGE statement
joinClauses = new List <string>();
updateClauses = new List <string>();
mappedExternalColumnNames = new List <string>();
// First, clear all rows in the table
this.mappingGridView.Rows.Clear();
mapColumnNameToLineageID_ = new Dictionary <string, int>();
mapExternalColumnNameToID_ = new Dictionary <string, int>();
// Now, create a list of external metadata column names
List <string> externalColumnNames = new List <string>();
foreach (IDTSExternalMetadataColumn100 externalColumn in externalColumns)
{
externalColumnNames.Add(externalColumn.Name);
mapExternalColumnNameToID_.Add(externalColumn.Name, externalColumn.ID);
}
this.TargetColumn.Items.Clear();
this.TargetColumn.Items.AddRange(externalColumnNames.ToArray());
// Create a list of actions (join or update) that could be performed on each attribute
string[] actions = new string[] { "Join", "Update" };
List <string> actionNames = new List <string>();
foreach (string action in actions)
{
actionNames.Add(action);
}
this.JoinOrUpdateColumn.Items.Clear();
this.JoinOrUpdateColumn.Items.AddRange(actionNames.ToArray());
foreach (IDTSVirtualInputColumn100 virtualColumn in virtualColumns)
{
DataGridViewRow row = new DataGridViewRow();
row.CreateCells(mappingGridView,
virtualColumn.UsageType != DTSUsageType.UT_IGNORED,
virtualColumn.Name);
mappingGridView.Rows.Add(row);
// also, cache the lineage id of this virtual column
mapColumnNameToLineageID_.Add(virtualColumn.Name, virtualColumn.LineageID);
}
// Advanced section
// Build the first two lines of the merge statement
IDTSCustomProperty100 propTableName = component_.CustomPropertyCollection[TABLE_OR_VIEW_NAME];
string tableName = (string)propTableName.Value;
String mergeStatementPrefix =
String.Format("MERGE INTO {0} AS TARGET \n USING INPUT-BUFFER AS SOURCE", tableName);
mergeStatementLabel.Text = mergeStatementPrefix;
}
internal BipsProxyIDTSVirtualInputColumnCollection100(IDTSVirtualInputColumnCollection100 innerObject)
{
_innerObject = innerObject;
}
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 void RebuildMappings(IDTSVirtualInputColumnCollection100 virtualInputColumns, IDTSInputColumnCollection100 inputColumns)
{
Hashtable mappingsInput = new Hashtable();
List <object[]> mappingsWithoutInputs = new List <object[]>();
List <object[]> newMappings = new List <object[]>();
//Speichern der bisherigen Mappings in 2 Listen (1x mit Input-Mapping, 1x ohne)
for (int i = 0; i < this.MappingRowCount; i++)
{
object[] row = this.GetMappingRow(i);
if (row[Constants.MAPPING_IDX_INPUT_COL_NAME] != null &&
row[Constants.MAPPING_IDX_INPUT_COL_NAME].ToString() != "")
{
mappingsInput.Add(row[Constants.MAPPING_IDX_INPUT_COLUMN_ID], row);
}
else
{
mappingsWithoutInputs.Add(row);
}
}
//Generieren von neuen MappingRows anhand der InputColumns
foreach (IDTSInputColumn100 inputCol in inputColumns)
{
object[] row;
if (mappingsInput.Contains(inputCol.ID))
{
row = (object[])mappingsInput[inputCol.ID];
row[Constants.MAPPING_IDX_INPUT_COL_NAME] = inputCol.Name;
row[Constants.MAPPING_IDX_DATATYPE_INPUT] = SqlCreator.GetSQLServerDataTypeFromInput(inputCol);//inputCol.DataType.ToString();
}
else
{
row = new object[Constants.MAPPING_COUNT];
row[Constants.MAPPING_IDX_USE_INSERT] = true;
row[Constants.MAPPING_IDX_USE_UPDATE] = true;
row[Constants.MAPPING_IDX_KEY] = false;
row[Constants.MAPPING_IDX_INPUT_COL_NAME] = inputCol.Name;
row[Constants.MAPPING_IDX_OUTPUT_COL_NAME] = Constants.MAPPING_NOT_ASSIGNED;
row[Constants.MAPPING_IDX_DEFAULT] = "";
row[Constants.MAPPING_IDX_DATATYPE_INPUT] = SqlCreator.GetSQLServerDataTypeFromInput(inputCol);//inputCol.DataType.ToString();
row[Constants.MAPPING_IDX_INPUT_COLUMN_ID] = inputCol.ID;
}
newMappings.Add(row);
}
//Aufbauen der neuen Mapping Properties
this.RemoveMappings();
foreach (object[] row in newMappings)
{
this.AddMappingProperty(row);
}
foreach (object[] row in mappingsWithoutInputs)
{
this.AddMappingProperty(row);
}
}
// 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);
}
