/// <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); }