//Dissallow DT_NTEXT, DT_TEXT and DT_IMAGE data types. public override bool CheckColumnForInputValidity(IDTSVirtualInput vInput, IDTSInput input, FormInputColumn icol) { bool validForInput = false; var dt = icol.DTSVirtualColumn.DataType; if (dt == DataType.DT_NTEXT || dt == DataType.DT_TEXT || dt == DataType.DT_IMAGE) { validForInput = false; } else { if (icol.DTSInputColumn != null) { //IDTSCustomProperty nullCol = icol.DTSInputColumn.CustomPropertyCollection[Resources.LookupErrorAggIsNullColumnName]; //bool isNullCol = (bool)nullCol.Value; IDTSCustomProperty keyCol = icol.DTSInputColumn.CustomPropertyCollection[Resources.LookupErrorAggIsKeyColumnName]; bool isKeyCol = (bool)keyCol.Value; if (!isKeyCol) { DesignTimeComponent.SetUsageType(input.ID, vInput, icol.LineageID, DTSUsageType.UT_IGNORED); icol.DTSInputColumn = null; } } validForInput = true; } return(validForInput); }
/// <summary> /// Provides SSIS Component Properties /// </summary> public override void ProvideComponentProperties() { this.ComponentMetaData.Version = PipelineUtils.GetPipelineComponentVersion(this); this.ComponentMetaData.Name = Resources.ColumnsToXMLTransformationName; this.ComponentMetaData.Description = Resources.ColumnsToXMLTransformationDescription; this.ComponentMetaData.ContactInfo = Resources.TransformationContactInfo; // Reset the component. base.RemoveAllInputsOutputsAndCustomProperties(); //call base method to insert input and output base.ProvideComponentProperties(); //Set Input properties IDTSInput input = ComponentMetaData.InputCollection[0]; input.Name = Resources.ColumnsToXmlInputName; // Set Output properties IDTSOutput output = ComponentMetaData.OutputCollection[0]; output.Name = Resources.ColumnsToXmlOutputName; output.SynchronousInputID = input.ID; //Add Custom Properties for the component AddComponentCustomProperties(); }
private void form_SetInputColumn(object sender, SetInputColumnArgs args) { Debug.Assert(args.VirtualColumn != null, "Invalid arguments passed from the UI"); this.ClearErrors(); try { // Grab the used input collection. This component can only have one... IDTSInput input = this.ComponentMetadata.InputCollection[0]; // Get the virtual column from the args... IDTSVirtualInputColumn virtualInputColumn = args.VirtualColumn.Tag as IDTSVirtualInputColumn; if (virtualInputColumn == null) { throw new ApplicationException(Properties.Resources.UIisInconsistentState); } // Get the lineageId, so we can use it to enable this column as an input column... int lineageId = virtualInputColumn.LineageID; IDTSInputColumn inputColumn = this.DesigntimeComponent.SetUsageType(input.ID, this.VirtualInput, lineageId, DTSUsageType.UT_READONLY); // return the new column back to the GUI to stick into a Tag... args.GeneratedColumns.InputColumn = new DataFlowElement(inputColumn.Name, inputColumn); } catch (Exception ex) { this.ReportErrors(ex); args.CancelAction = true; } }
private void form_DeleteInputColumn(object sender, SetInputColumnArgs args) { Debug.Assert(args.VirtualColumn != null, "Invalid arguments passed from the UI"); this.ClearErrors(); try { // Grab the used input columns from the Component. IDTSInput input = this.ComponentMetadata.InputCollection[0]; // Get the Virtual column from the args. IDTSVirtualInputColumn virtualInputColumn = args.VirtualColumn.Tag as IDTSVirtualInputColumn; if (virtualInputColumn == null) { throw new ApplicationException(Properties.Resources.UIisInconsistentState); } // Get the lineageId, so we can use it to disable this column as an input column... int lineageId = virtualInputColumn.LineageID; this.DesigntimeComponent.SetUsageType(input.ID, this.VirtualInput, lineageId, DTSUsageType.UT_IGNORED); } catch (Exception ex) { this.ReportErrors(ex); args.CancelAction = true; } }
private void ReplaceInput(MainPipe mainPipe, IDTSInput input, Dataset dataset) { var placeholderSource = mainPipe.ComponentMetaDataCollection.New(); placeholderSource.ComponentClassID = typeof(SsisUnitSource).AssemblyQualifiedName; CManagedComponentWrapper wrapper = placeholderSource.Instantiate(); wrapper.ProvideComponentProperties(); wrapper.SetComponentProperty("TestData", GetDataSet(dataset)); #if DEBUG for (int i = 0; i < placeholderSource.OutputCollection[0].OutputColumnCollection.Count; i++) { System.Diagnostics.Debug.Print(placeholderSource.OutputCollection[0].OutputColumnCollection[i].Name); } #endif var path = Helper.FindPath(mainPipe, input); mainPipe.PathCollection.RemoveObjectByID(path.ID); path = mainPipe.PathCollection.New(); path.AttachPathAndPropagateNotifications(placeholderSource.OutputCollection[0], input); // TODO: Remap IDs? - Failing downstream because of invalid column references. // Could remap ids, or clone the output column ids from the original source - that will mean changing the source component. }
/// <summary> /// Get Maximum Input Sort Order /// </summary> /// <returns>Maximu Sort Order of Input Columns</returns> public static int GetMaxInputColumnsSortOrder(IDTSInput input) { int maxOrder = int.MinValue; foreach (IDTSInputColumn col in input.InputColumnCollection) { foreach (IDTSCustomProperty prop in col.CustomPropertyCollection) { if (prop.Name == Resources.InputSortOrderPropertyName) { int order = prop.Value != null ? (int)prop.Value : 0; if (order > maxOrder) { maxOrder = order; } break; } } } if (maxOrder == int.MinValue) { maxOrder = 0; } return(maxOrder); }
public override object Execute(Package package, DtsContainer container) { string remainingPath; var taskHost = Helper.FindExecutable(package, Properties[PropInput].Value, out remainingPath) as TaskHost; if (taskHost == null) { throw new ArgumentException("Input property did not match a valid component input.", "package"); } var mainPipe = taskHost.InnerObject as MainPipe; if (mainPipe == null) { throw new ArgumentException("Input property did not match a valid component input.", "package"); } IDTSInput input = Helper.FindComponentInput(mainPipe, remainingPath); if (input == null) { throw new ArgumentException("Input property did not match a valid component input.", "package"); } ReplaceInput(mainPipe, input, TestSuite.Datasets[Properties[PropDataSet].Value]); return(null); }
public void SelectLookupInputColumn(System.Windows.Forms.CheckedListBox clbInputColumns, System.Windows.Forms.ListBox lbSelectedItems, int checkedIndex, System.Windows.Forms.CheckState state, int nullColumnLineageId) { FormInputColumn icol = clbInputColumns.Items[checkedIndex] as FormInputColumn; IDTSInput input = ComponentMetadata.InputCollection[0]; if (icol != null) { if (state == CheckState.Checked) { DesignTimeComponent.SetUsageType(input.ID, VirtualInput, icol.LineageID, DTSUsageType.UT_READONLY); IDTSInputColumn inputCol = input.InputColumnCollection.GetInputColumnByLineageID(icol.LineageID); IDTSCustomProperty keyCol = inputCol.CustomPropertyCollection[Resources.LookupErrorAggIsKeyColumnName]; keyCol.Value = true; icol.DTSInputColumn = inputCol; if (lbSelectedItems != null && !lbSelectedItems.Items.Contains(icol)) { int sortOrder = 0; if (lbSelectedItems.Items.Count > 0) { FormInputColumn lastCol = lbSelectedItems.Items[lbSelectedItems.Items.Count - 1] as FormInputColumn; sortOrder = lastCol.SortOrder; } icol.SortOrder = sortOrder + 1; lbSelectedItems.Items.Add(icol); } } else { bool isInput = false; if (icol.DTSInputColumn != null) { IDTSCustomProperty prop = icol.DTSInputColumn.CustomPropertyCollection[Resources.LookupErrorAggIsNullColumnName]; isInput = (bool)prop.Value; IDTSCustomProperty keyCol = icol.DTSInputColumn.CustomPropertyCollection[Resources.LookupErrorAggIsKeyColumnName]; keyCol.Value = false; } if (!isInput) { DesignTimeComponent.SetUsageType(input.ID, VirtualInput, icol.LineageID, DTSUsageType.UT_IGNORED); icol.DTSInputColumn = null; } if (lbSelectedItems != null && lbSelectedItems.Items.Contains(icol)) { lbSelectedItems.Items.Remove(icol); } } } }
// public static object GetPropertyValue(Package pkg, string propertyPath) // { // return null; // } // \package.variables[myvariable].Value // \Package\Sequence Container\Script Task.Properties[Description] public static IDTSPath FindPath(MainPipe mainPipe, IDTSInput input) { foreach (IDTSPath path in mainPipe.PathCollection) { if (path.EndPoint.ID == input.ID) { return(path); } } return(null); }
public InputWrapper FindInput(string inputName) { Pipe.IDTSInput100 ssisInput = FindSSISInput(inputName); if (ssisInput != null) { return(new InputWrapper(ssisInput)); } else { throw new KeyNotFoundException("Can't find input with name " + inputName); } }
/// <summary> /// Gets InputColumns of Input at selected inde of InputCollection encapsulated in the FormInputColumn class /// </summary> /// <param name="index">Index of the Input to get InputColumns</param> /// <returns>List of FormInputColumn</returns> public virtual List <FormInputColumn> GetFormInputColumns(int index) { List <FormInputColumn> inputCols = new List <FormInputColumn>(); IDTSInput input = GetInput(index); IDTSVirtualInput vInput = GetVirtualInput(index); if (input != null && VirtualInput != null) { for (int i = 0; i < VirtualInput.VirtualInputColumnCollection.Count; i++) //foreach (IDTSVirtualInputColumn vcol in VirtualInput.VirtualInputColumnCollection) { IDTSVirtualInputColumn vcol = VirtualInput.VirtualInputColumnCollection[i]; FormInputColumn icol = new FormInputColumn(vcol, i); if (vcol.UsageType != DTSUsageType.UT_IGNORED) { IDTSInputColumn inputCol = input.InputColumnCollection.GetInputColumnByLineageID(vcol.LineageID); icol.DTSInputColumn = inputCol; } bool isValidForInput = CheckColumnForInputValidity(vInput, input, icol); if (isValidForInput) { inputCols.Add(icol); } } inputCols.Sort((a, b) => a.Name.CompareTo(b.Name)); for (int i = 0; i < inputCols.Count; i++) { if (i >= 0 && i < inputCols.Count - 1 && inputCols[i].Name == inputCols[i + 1].Name) { inputCols[i].DisplayName = string.Format("{0}.{1}", inputCols[i].SourceComponent, inputCols[i].Name); } else if (i > 0 && i < inputCols.Count && inputCols[i].Name == inputCols[i - 1].Name) { inputCols[i].DisplayName = string.Format("{0}.{1}", inputCols[i].SourceComponent, inputCols[i].Name); } } inputCols.Sort((a, b) => a.Index.CompareTo(b.Index)); } return(inputCols); }
/// <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; } }
/// <summary> /// Gets VirtulaInput of the Input of the DataFlow Component at the Index position in the Inputs Collection. /// It ensures that the same IDTSVirtualInput object is retured during each call and ensures proper GUI functionality /// </summary> /// <param name="index">Index of the Input to retrive VistualInput</param> /// <returns>IDTSVirtualINput of Input at sprovided Index position</returns> public IDTSVirtualInput GetVirtualInput(int index) { while (virtualInputs.Count < ComponentMetadata.InputCollection.Count) { virtualInputs.Add(null); } IDTSInput input = GetInput(index); if (virtualInputs[index] == null && input != null) { virtualInputs[index] = input.GetVirtualInput(); } return(virtualInputs[index]); }
protected void AddInputColumnForInput(InputColumn column, string inputName) { GetAllVirtualInputs(); Pipe.IDTSVirtualInput100 virtualInput = VirtualInputs.Where(vi => vi.Description == inputName).First(); Pipe.IDTSInput100 input = SSISInputs.Where(i => i.Name == inputName).First(); for (int i = 0; i < VirtualInputsColumnCollections[virtualInput].Count; i++) { if (column.Name == VirtualInputsColumnCollections[virtualInput][i].Name) { ComponentWrapper.SetUsageType(input.ID, virtualInput, VirtualInputsColumnCollections[virtualInput][i].LineageID, (Pipe.DTSUsageType)column.UsageType); } } ; }
//TODO /* * /// <summary> * /// Get the columns that are public * /// </summary> * /// <param name="sharepointUrl"></param> * /// <param name="listName"></param> * /// <returns></returns> * private List<SharePointUtilityOnline.DataObject.ColumnData> * GetAccessibleSharePointColumns(string sharepointUrl, string listName, string viewName) * { * List<SharePointUtilityOnline.DataObject.ColumnData> columnList = * ListServiceUtility.GetFields(new Uri(sharepointUrl), _credentials, listName, viewName); * * // Pull out the ID Field because we want this to be first in the list, and the other columns * // will keep their order that SharePoint sends them. * var idField = * from c in columnList * where (c.Name == "ID" || c.Name == "FsObjType") * select c; * * var accessibleColumns = * from c in columnList * where (!c.IsHidden && !c.IsReadOnly) * select c; * * return idField.Union(accessibleColumns).ToList(); * } */ /// <summary> /// Connects to SharePoint and gets any columns on the target /// </summary> /// <param name="input"></param> /// <param name="sharepointUrl"></param> /// <param name="listName"></param> private void CreateExternalMetaDataColumns( IDTSInput input, Dictionary <string, string> existingColumnData) { input.ExternalMetadataColumnCollection.IsUsed = true; var inputColumns = (from col in input.InputColumnCollection.Cast <IDTSInputColumn>() select col).ToList(); foreach (var colItem in inputColumns) { var dtsColumnMeta = input.ExternalMetadataColumnCollection.New(); dtsColumnMeta.Name = colItem.Name; dtsColumnMeta.DataType = colItem.DataType; dtsColumnMeta.Description = colItem.Description; dtsColumnMeta.Length = 0; dtsColumnMeta.Precision = 0; dtsColumnMeta.Scale = 0; } }
/// <summary> /// Selects an InputColumn (to become available in the input buffer) /// </summary> /// <param name="clbInputColumns">CheckedListbox with InputColumns</param> /// <param name="lbSelectedItems">ListBox with Selected InputColumns</param> /// <param name="checkedIndex">Index of the Item being checked</param> /// <param name="state">CheckState of the item being checkd</param> public virtual void SelectInputColumn(CheckedListBox clbInputColumns, ListBox lbSelectedItems, int checkedIndex, CheckState state) { FormInputColumn icol = clbInputColumns.Items[checkedIndex] as FormInputColumn; IDTSInput input = ComponentMetadata.InputCollection[0]; if (icol != null) { if (state == CheckState.Checked) { DesignTimeComponent.SetUsageType(input.ID, VirtualInput, icol.LineageID, DTSUsageType.UT_READONLY); IDTSInputColumn inputCol = input.InputColumnCollection.GetInputColumnByLineageID(icol.LineageID); icol.DTSInputColumn = inputCol; if (lbSelectedItems != null && !lbSelectedItems.Items.Contains(icol)) { int sortOrder = 0; if (lbSelectedItems.Items.Count > 0) { FormInputColumn lastCol = lbSelectedItems.Items[lbSelectedItems.Items.Count - 1] as FormInputColumn; sortOrder = lastCol.SortOrder; } icol.SortOrder = sortOrder + 1; lbSelectedItems.Items.Add(icol); } } else { DesignTimeComponent.SetUsageType(input.ID, VirtualInput, icol.LineageID, DTSUsageType.UT_IGNORED); icol.DTSInputColumn = null; if (lbSelectedItems != null && lbSelectedItems.Items.Contains(icol)) { lbSelectedItems.Items.Remove(icol); } } } }
/// <summary> /// Called repeatedly when the component is edited in the designer, and once at the beginning of execution. /// Verifies the following: /// 1. Check that there are no outputs /// 2. Check that there is only one input /// 3. Check that all upstream columns are present. /// </summary> /// <returns>The status of the validation</returns> public override DTSValidationStatus Validate() { bool cancel = false; if (ComponentMetaData.InputCollection.Count != 1) { ComponentMetaData.FireError(0, ComponentMetaData.Name, "The input collection count is not 1.", String.Empty, 0, out cancel); return(DTSValidationStatus.VS_ISCORRUPT); } if (ComponentMetaData.OutputCollection.Count != 0) { ComponentMetaData.FireError(0, ComponentMetaData.Name, "The output collection count is not 0.", String.Empty, 0, out cancel); return(DTSValidationStatus.VS_ISCORRUPT); } IDTSInput input = ComponentMetaData.InputCollection[0]; IDTSVirtualInput vInput = input.GetVirtualInput(); if (input.HasSideEffects == false) { ComponentMetaData.FireError(0, ComponentMetaData.Name, "The input does not have HasSideEffects set.", String.Empty, 0, out cancel); return(DTSValidationStatus.VS_ISCORRUPT); } foreach (IDTSInputColumn inputColumn in input.InputColumnCollection) { try { IDTSVirtualInputColumn vColumn = vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(inputColumn.LineageID); } catch { ComponentMetaData.FireError(0, ComponentMetaData.Name, "The input column " + inputColumn.IdentificationString + " does not match a column in the upstream output.", String.Empty, 0, out cancel); areInputColumnsValid = false; return(DTSValidationStatus.VS_NEEDSNEWMETADATA); } } //return base.Validate(); return(DTSValidationStatus.VS_ISVALID); }
///// <summary> ///// Get Maximum Input Sort Order ///// </summary> ///// <returns>Maximu Sort Order of Input Columns</returns> //public static int GetMaxInputColumnsSortOrder(IDTSInput input) //{ // int maxOrder = 0; // foreach (IDTSInputColumn col in input.InputColumnCollection) // { // foreach (IDTSCustomProperty prop in col.CustomPropertyCollection) // { // if (prop.Name == Resources.InputSortOrderPropertyName) // { // int order = prop.Value != null ? (int)prop.Value : 0; // if (order > maxOrder) // maxOrder = order; // break; // } // } // } // return maxOrder; //} public static void UpdateSortOrder(IDTSInput input, int inputColumnID, int val) { IDTSInputColumn currentColumn = input.InputColumnCollection.GetObjectByID(inputColumnID); IDTSCustomProperty currentProp = currentColumn.CustomPropertyCollection[Resources.InputSortOrderPropertyName]; int currentValue = (int)currentProp.Value; foreach (IDTSInputColumn col in input.InputColumnCollection) { if (col.ID == inputColumnID) { continue; } IDTSCustomProperty prop = col.CustomPropertyCollection[Resources.InputSortOrderPropertyName]; if (prop != null && (int)prop.Value == val) { prop.Value = currentValue; //UpdateSortOrder(inputID, col.ID, val + 1); } } }
private void btnOk_Click(object sender, EventArgs e) { if (properties.NullColumnLineageId != -1) { IDTSInput input = UIHelper.GetInput(0); IDTSVirtualInput vInput = UIHelper.GetVirtualInput(0); IDTSVirtualInputColumn vCol = vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(properties.NullColumnLineageId);; IDTSInputColumn col; if (vCol.UsageType != DTSUsageType.UT_IGNORED) { col = input.InputColumnCollection.GetInputColumnByLineageID(vCol.LineageID); } else { col = UIHelper.DesignTimeComponent.SetUsageType(input.ID, vInput, properties.NullColumnLineageId, DTSUsageType.UT_READONLY); } IDTSCustomProperty nullCol = col.CustomPropertyCollection[Resources.LookupErrorAggIsNullColumnName]; nullCol.Value = true; } this.Close(); }
private void HashColumnsTransformationForm_Load(object sender, EventArgs e) { IDTSInput input = UIHelper.GetInput(0); if (input == null || input.IsAttached == false) { inputIsAttached = false; } HashOutputColumn.InputIsAttached = inputIsAttached; UIHelper.FormInitialize(null, null, trvOutputColumns, HashOutputColumn.CreateInstance, o_PropertyChanged); IDTSCustomProperty parallelProcessing = UIHelper.ComponentMetadata.CustomPropertyCollection[Resources.HashTransformationParallelProcessingPropertyName]; HashComponentProperties properties = new HashComponentProperties(parallelProcessing); propComponent.SelectedObject = properties; if (inputIsAttached == false) { MessageBox.Show("Hash Columns Transformation has no input attached. Columns selection will be disabled", "Input not attached", MessageBoxButtons.OK, MessageBoxIcon.Exclamation); } }
/// <summary> /// Connects to SharePoint and gets any columns on the target /// </summary> /// <param name="input"></param> /// <param name="sharepointUrl"></param> /// <param name="listName"></param> private void CreateExternalMetaDataColumns( IDTSInput input, string sharepointUrl, string listName, string viewName, Dictionary <string, string> existingColumnData) { // No need to load if the Url is bad. if ((sharepointUrl == null) || (sharepointUrl.Length == 0)) { return; } // Need a list to continue if ((listName == null) || (listName.Length == 0)) { return; } input.ExternalMetadataColumnCollection.IsUsed = true; // If the list has changed, then we do not want any of the exisiting column data to // influence it (provides a way to actually reset the names if needed) if (input.Description != listName) { existingColumnData.Clear(); input.Description = listName; } try { List <SharePointUtility.DataObject.ColumnData> accessibleColumns = GetAccessibleSharePointColumns(sharepointUrl, listName, viewName); foreach (var column in accessibleColumns) { // Setup the primary column details from the List var dtsColumnMeta = input.ExternalMetadataColumnCollection.New(); if (existingColumnData.ContainsKey(column.Name)) { dtsColumnMeta.Name = existingColumnData[column.Name]; } else { dtsColumnMeta.Name = column.FriendlyName; } dtsColumnMeta.Description = column.DisplayName; dtsColumnMeta.Length = 0; dtsColumnMeta.Precision = 0; dtsColumnMeta.Scale = 0; if ("Boolean|AllDayEvent|Attachments|CrossProjectLink|Recurrence".Contains(column.SharePointType)) { dtsColumnMeta.DataType = DataType.DT_BOOL; } else if (column.SharePointType == "DateTime") { dtsColumnMeta.DataType = DataType.DT_DBTIMESTAMP; } else if ("Number|Currency".Contains(column.SharePointType)) { // Max = 100,000,000,000.00000 dtsColumnMeta.DataType = DataType.DT_R8; } else if ("Counter|Integer".Contains(column.SharePointType)) { dtsColumnMeta.DataType = DataType.DT_I4; } else if ("Guid".Contains(column.SharePointType)) { dtsColumnMeta.DataType = DataType.DT_GUID; } else { if (column.MaxLength == -1) { dtsColumnMeta.DataType = DataType.DT_NTEXT; dtsColumnMeta.Length = 0; } else { dtsColumnMeta.DataType = DataType.DT_WSTR; dtsColumnMeta.Length = column.MaxLength; } } var fieldNameMeta = dtsColumnMeta.CustomPropertyCollection.New(); fieldNameMeta.Name = "Id"; fieldNameMeta.Description = "SharePoint ID"; fieldNameMeta.Value = column.Name; // Map any columns found with the same name in the input var foundCol = (from col in input.InputColumnCollection.Cast <IDTSInputColumn>() where col.Name == dtsColumnMeta.Name select col).SingleOrDefault(); if (foundCol != null) { foundCol.ExternalMetadataColumnID = dtsColumnMeta.ID; } } } catch (SharePointUnhandledException) { // Exception happened, so clear the columns, which will invalidate this object. input.ExternalMetadataColumnCollection.RemoveAll(); throw; } }
/// <summary> /// PreExecute Phase for initialization of internal runtime structures /// </summary> public override void PreExecute() { base.PreExecute(); IDTSInput input = ComponentMetaData.InputCollection[0]; for (int i = 0; i < input.InputColumnCollection.Count; i++) { IDTSInputColumn column = input.InputColumnCollection[i]; } IDTSOutput output = ComponentMetaData.OutputCollection[0]; rowNumberColumns = new List <RowNumberColumn>(); //Iterate thorough OutputColumns collection and generate and prepare RowNumberColumns foreach (IDTSOutputColumn col in output.OutputColumnCollection) { if (col.CustomPropertyCollection.Count == 2) { RowNumberColumn numCol = new RowNumberColumn(); numCol.Index = BufferManager.FindColumnByLineageID(input.Buffer, col.LineageID); numCol.DataType = col.DataType; foreach (IDTSCustomProperty prop in col.CustomPropertyCollection) { switch (prop.Name) { case "IncrementBy": if (col.DataType == DataType.DT_DATE || col.DataType == DataType.DT_DBDATE) { string pv = prop.Value.ToString(); char ch = pv[pv.Length - 1]; switch (ch) { case 'd': numCol.IncrementType = IncrementByType.Day; break; case 'w': numCol.IncrementType = IncrementByType.Week; break; case 'm': numCol.IncrementType = IncrementByType.Month; break; case 'y': numCol.IncrementType = IncrementByType.Year; break; } numCol.IncrementBy = int.Parse(pv.Substring(0, pv.Length - 1)); } else { numCol.IncrementBy = prop.Value; numCol.IncrementType = IncrementByType.Value; } break; case "InitialValue": numCol.InitialValue = prop.Value; break; default: break; } } rowNumberColumns.Add(numCol); } } }
/// <summary> /// Validate HashColumnsTransform metadata /// </summary> /// <returns></returns> public override DTSValidationStatus Validate() { //Check that we have only one input if (ComponentMetaData.InputCollection.Count != 1) { FireComponentMetadataError(0, Resources.ErrorIncorrectNumberOfInputs); return(DTSValidationStatus.VS_NEEDSNEWMETADATA); } //Check that we have only one output if (ComponentMetaData.OutputCollection.Count != 1) { FireComponentMetadataError(0, Resources.ErrorIncorrectNumberOfOutputs); return(DTSValidationStatus.VS_NEEDSNEWMETADATA); } IDTSInput input = ComponentMetaData.InputCollection[0]; IDTSVirtualInput vInput = input.GetVirtualInput(); //Check Input columns that they exists foreach (IDTSInputColumn column in input.InputColumnCollection) { try { IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(column.LineageID); } catch { inputColumnsValid = false; FireComponentMetadataError(0, string.Format(Resources.ErrorInputColumnNotInUpstreamComponent, column.IdentificationString)); return(DTSValidationStatus.VS_NEEDSNEWMETADATA); } } IDTSOutput output = ComponentMetaData.OutputCollection[0]; List <IDTSOutputColumn> redundantColumns = new List <IDTSOutputColumn>(output.OutputColumnCollection.Count); bool rowNumberColumnExists = false; foreach (IDTSOutputColumn outputColumn in output.OutputColumnCollection) { bool isRendundant = true; foreach (IDTSCustomProperty prop in outputColumn.CustomPropertyCollection) { if (prop.Name == Resources.RowNumberIncrementByPropertyName) { isRendundant = false; rowNumberColumnExists = true; } //TODO: Here will be checks for proper properties settings } //add redundand column to redundand list if (isRendundant) { redundantColumns.Add(outputColumn); } } //remove redundand output columns foreach (IDTSOutputColumn100 col in redundantColumns) { output.OutputColumnCollection.RemoveObjectByID(col.ID); } if (!rowNumberColumnExists) { rowNumberColumnExists = true; output = ComponentMetaData.OutputCollection[0]; IDTSOutputColumn rowNumberColumn = output.OutputColumnCollection.New(); rowNumberColumn.Name = Resources.RowNumberDefaultColumnName; rowNumberColumn.Description = Resources.RowNumberDefaultColumnDescription; SetRowNumberColumnProperties(rowNumberColumn); } return(DTSValidationStatus.VS_ISVALID); }
/// <summary> /// PreExecute Phase for initialization of internal runtime structures /// </summary> public override void PreExecute() { bool fireAgain = true; rowsProcessed = 0; ComponentMetaData.FireInformation(0, this.ComponentMetaData.Name, "Pre-Execute phase is beginning.", string.Empty, 0, ref fireAgain); IDTSInput input = ComponentMetaData.InputCollection[0]; inputBufferColumns = new List <InputBufferColumnInfo>(input.InputColumnCollection.Count); for (int i = 0; i < input.InputColumnCollection.Count; i++) { IDTSInputColumn column = input.InputColumnCollection[i]; inputBufferColumns.Add(new InputBufferColumnInfo(BufferManager.FindColumnByLineageID(input.Buffer, column.LineageID), column.Name, column.ID, column.LineageID, 0, column.DataType, column.Length, column.Precision, column.Scale)); } outputColumns = new List <XmlColumn>(); IDTSOutput output = ComponentMetaData.OutputCollection[0]; foreach (IDTSOutputColumn col in output.OutputColumnCollection) { SaveOptions saveOptions = SaveOptions.None; bool serializeDataType = false; bool serializeLineage = false; string sourceID = string.Empty; string sourceName = string.Empty; List <int> cols = null; foreach (IDTSCustomProperty prop in col.CustomPropertyCollection) { if (prop.Name == Resources.XmlSaveOptionPropertyName) { saveOptions = (SaveOptions)prop.Value; } else if (prop.Name == Resources.XmlSerializeDataTypeName) { serializeDataType = (bool)prop.Value; } else if (prop.Name == Resources.XmlSerializeLineageName) { serializeLineage = (bool)prop.Value; } else if (prop.Name == Resources.XmlInputColumnsPropertyname) { string colsStr = prop.Value.ToString(); var colLineages = InputColumns.ParseInputLineages(colsStr); cols = new List <int>(colLineages.Count); foreach (int lineageID in colLineages) { int idx = inputBufferColumns.FindIndex(ibci => ibci.LineageID == lineageID); cols.Add(idx); } } else if (prop.Name == Resources.XmlSourceIdPropertyName) { sourceName = prop.Value.ToString(); } else if (prop.Name == Resources.XmlSourceNamePropertyName) { sourceID = prop.Value.ToString(); } } int index = BufferManager.FindColumnByLineageID(input.Buffer, col.LineageID); outputColumns.Add(new XmlColumn(index, col.Name, col.DataType, saveOptions, serializeLineage, serializeDataType, cols, sourceID, sourceName, col.Length)); } }
//TODO /* /// <summary> /// Get the columns that are public /// </summary> /// <param name="sharepointUrl"></param> /// <param name="listName"></param> /// <returns></returns> private List<SharePointUtilityOnline.DataObject.ColumnData> GetAccessibleSharePointColumns(string sharepointUrl, string listName, string viewName) { List<SharePointUtilityOnline.DataObject.ColumnData> columnList = ListServiceUtility.GetFields(new Uri(sharepointUrl), _credentials, listName, viewName); // Pull out the ID Field because we want this to be first in the list, and the other columns // will keep their order that SharePoint sends them. var idField = from c in columnList where (c.Name == "ID" || c.Name == "FsObjType") select c; var accessibleColumns = from c in columnList where (!c.IsHidden && !c.IsReadOnly) select c; return idField.Union(accessibleColumns).ToList(); } */ /// <summary> /// Connects to SharePoint and gets any columns on the target /// </summary> /// <param name="input"></param> /// <param name="sharepointUrl"></param> /// <param name="listName"></param> private void CreateExternalMetaDataColumns( IDTSInput input,Dictionary<string, string> existingColumnData) { input.ExternalMetadataColumnCollection.IsUsed = true; var inputColumns = (from col in input.InputColumnCollection.Cast<IDTSInputColumn>() select col).ToList(); foreach (var colItem in inputColumns) { var dtsColumnMeta = input.ExternalMetadataColumnCollection.New(); dtsColumnMeta.Name = colItem.Name; dtsColumnMeta.DataType = colItem.DataType; dtsColumnMeta.Description = colItem.Description; dtsColumnMeta.Length = 0; dtsColumnMeta.Precision = 0; dtsColumnMeta.Scale = 0; } }
/// <summary> /// Validate HashColumnsTransform metadata /// </summary> /// <returns></returns> public override DTSValidationStatus Validate() { if (ComponentMetaData.InputCollection.Count != 1) { FireComponentMetadataError(0, Resources.ErrorIncorrectNumberOfInputs); return(DTSValidationStatus.VS_NEEDSNEWMETADATA); } if (ComponentMetaData.OutputCollection.Count != 1) { FireComponentMetadataError(0, Resources.ErrorIncorrectNumberOfOutputs); return(DTSValidationStatus.VS_NEEDSNEWMETADATA); } IDTSInput input = ComponentMetaData.InputCollection[0]; IDTSVirtualInput vInput = input.GetVirtualInput(); foreach (IDTSInputColumn column in input.InputColumnCollection) { try { IDTSVirtualInputColumn100 vColumn = vInput.VirtualInputColumnCollection.GetVirtualInputColumnByLineageID(column.LineageID); } catch { FireComponentMetadataError(0, string.Format(Resources.ErrorInputColumnNotInUpstreamComponent, column.IdentificationString)); inputColumnsValid = false; return(DTSValidationStatus.VS_NEEDSNEWMETADATA); } } IDTSOutput output = ComponentMetaData.OutputCollection[0]; List <IDTSOutputColumn> redundantColumns = new List <IDTSOutputColumn>(output.OutputColumnCollection.Count); bool xmlColumnExists = false; List <int> missingLineages = new List <int>(); List <int> missingInputLineages = new List <int>(); bool missingError = false; foreach (IDTSOutputColumn outputColumn in output.OutputColumnCollection) { bool isRendundant = true; bool isInputColumn = false; foreach (IDTSInputColumn inputColumn in input.InputColumnCollection) { if (inputColumn.Name == outputColumn.Name) { isRendundant = false; isInputColumn = true; break; } } //Check if XML Column; if (!isInputColumn) { foreach (IDTSCustomProperty prop in outputColumn.CustomPropertyCollection) { if (prop.Name == Resources.XmlInputColumnsPropertyname) { isRendundant = false; xmlColumnExists = true; var lineages = InputColumns.ParseInputLineages(prop.Value.ToString()); foreach (IDTSInputColumn icol in input.InputColumnCollection) { int l = icol.LineageID; lineages.Remove(l); if (lineages.Count == 0) { break; } } if (lineages.Count > 0) { foreach (int l in lineages) { if (!missingInputLineages.Contains(l)) { missingInputLineages.Add(l); } } foreach (IDTSVirtualInputColumn vcol in vInput.VirtualInputColumnCollection) { int l = vcol.LineageID; lineages.Remove(l); if (lineages.Count == 0) { break; } } if (lineages.Count > 0) { foreach (int l in lineages) { if (!missingLineages.Contains(l)) { missingLineages.Add(l); } } } } } if (prop.Name == Resources.XmlSaveOptionPropertyName) { isRendundant = false; xmlColumnExists = true; if (!Enum.IsDefined(typeof(SaveOptions), prop.Value)) { FireComponentMetadataError(0, string.Format(Resources.ErrorInvalidSaveOption, outputColumn.Name)); return(DTSValidationStatus.VS_NEEDSNEWMETADATA); } else { if (outputColumn.DataType != DataType.DT_NTEXT && outputColumn.DataType != DataType.DT_WSTR) { FireComponentMetadataError(0, string.Format(Resources.ErrorInvalidDataType, outputColumn.Name)); return(DTSValidationStatus.VS_NEEDSNEWMETADATA); } } } } } //add redundand column to redundand list if (isRendundant) { redundantColumns.Add(outputColumn); } } if (missingLineages.Count > 0) { string ml = string.Join(", ", missingLineages.ConvertAll <string>(l => l.ToString()).ToArray()); FireComponentMetadataError(0, string.Format("Output columns are referencing input column lineages which do not exists: {0}", ml)); missingError = true; } if (missingInputLineages.Count > 0) { string ml = string.Join(", ", missingInputLineages.ConvertAll <string>(l => l.ToString()).ToArray()); FireComponentMetadataError(0, string.Format("Output columns are referencing input column lineages which are not selected as Input columns: {0}", ml)); missingError = true; } if (missingError) { return(DTSValidationStatus.VS_NEEDSNEWMETADATA); } //remove redundand output columns foreach (IDTSOutputColumn col in redundantColumns) { output.OutputColumnCollection.RemoveObjectByID(col.ID); } //If XmlColumn does not exists, create a default one if (!xmlColumnExists) { IDTSOutputColumn xmlCol = output.OutputColumnCollection.New(); xmlCol.Name = Resources.XmlColumnDefaultName; xmlCol.Description = Resources.XmlColumnDefaultDesccription; SetXmlColumnProperties(xmlCol); } return(DTSValidationStatus.VS_ISVALID); }
/// <summary> /// Upgrades the component from earlier versions /// </summary> /// <param name="pipelineVersion"></param> public override void PerformUpgrade(int pipelineVersion) { DtsPipelineComponentAttribute componentAttribute = (DtsPipelineComponentAttribute)Attribute.GetCustomAttribute(this.GetType(), typeof(DtsPipelineComponentAttribute), false); int currentVersion = componentAttribute.CurrentVersion; // Get the attributes for the SSIS Package int metadataVersion = ComponentMetaData.Version; if (currentVersion < ComponentMetaData.Version) { throw new Exception(Properties.Resources.ErrorWrongRuntimeVersion); } else if (currentVersion > ComponentMetaData.Version) { IDTSInput input = ComponentMetaData.InputCollection[0]; List <int> propsToRemove = new List <int>(); List <KeyValuePair <int, int> > colLienagesSort = new List <KeyValuePair <int, int> >(input.InputColumnCollection.Count); string sourceID = string.Empty; string sourceName = string.Empty; try { IDTSCustomProperty prop = ComponentMetaData.CustomPropertyCollection[Resources.ColumnsToXmlSourceNameProperty]; if (prop != null) { sourceName = prop.Value.ToString(); ComponentMetaData.CustomPropertyCollection.RemoveObjectByID(prop.ID); } } catch { } try { IDTSCustomProperty prop = ComponentMetaData.CustomPropertyCollection[Resources.ColumnsToXmlSourceIDProperty]; if (prop != null) { sourceID = prop.Value.ToString(); ComponentMetaData.CustomPropertyCollection.RemoveObjectByID(prop.ID); } } catch { } foreach (IDTSInputColumn col in input.InputColumnCollection) { int sortOrder = int.MaxValue; IDTSCustomProperty prop = null; propsToRemove = new List <int>(); foreach (IDTSCustomProperty p in col.CustomPropertyCollection) { if (p.Name == Resources.InputSortOrderPropertyName) { sortOrder = (int)p.Value; propsToRemove.Add(p.ID); break; } } colLienagesSort.Add(new KeyValuePair <int, int>(col.LineageID, sortOrder)); foreach (int id in propsToRemove) { col.CustomPropertyCollection.RemoveObjectByID(id); } } colLienagesSort.Sort((a, b) => a.Value.CompareTo(b.Value)); IDTSOutput output = ComponentMetaData.OutputCollection[0]; foreach (IDTSOutputColumn col in output.OutputColumnCollection) { SetPropertyType setProp = SetPropertyType.All; bool additionalPropertyExists = false; bool setDataType = !(col.DataType == DataType.DT_NTEXT || (col.DataType == DataType.DT_WSTR && col.Length <= 4000)); foreach (IDTSCustomProperty prop in col.CustomPropertyCollection) { if (prop.Name == Resources.XmlSaveOptionPropertyName) { setProp ^= SetPropertyType.XmlSaveOptions; } else if (prop.Name == Resources.XmlSerializeLineageName) { setProp ^= SetPropertyType.XmlSerializeLineage; } else if (prop.Name == Resources.XmlSerializeDataTypeName) { setProp ^= SetPropertyType.XmlSerializeDataType; } else if (prop.Name == Resources.XmlInputColumnsPropertyname) { setProp ^= SetPropertyType.XmlInputColumns; } else if (prop.Name == Resources.XmlSourceIdPropertyName) { setProp ^= SetPropertyType.XMlSourceID; } else if (prop.Name == Resources.XmlSourceNamePropertyName) { setProp ^= SetPropertyType.XmlSourceName; } else { additionalPropertyExists = true; } } if (setProp != SetPropertyType.None || additionalPropertyExists || setDataType) { SetXmlColumnProperties(col, setProp, setDataType); } if ((setProp & SetPropertyType.XmlInputColumns) == SetPropertyType.XmlInputColumns && colLienagesSort.Count > 0) { string lineages = InputColumns.BuildInputLineagesString(colLienagesSort.ConvertAll <int>(kvp => kvp.Key)); col.CustomPropertyCollection[Resources.XmlInputColumnsPropertyname].Value = lineages; } if ((setProp & SetPropertyType.XMlSourceID) == SetPropertyType.XMlSourceID) { col.CustomPropertyCollection[Resources.XmlSourceIdPropertyName].Value = sourceID; } if ((setProp & SetPropertyType.XmlSourceName) == SetPropertyType.XmlSourceName) { col.CustomPropertyCollection[Resources.XmlSourceNamePropertyName].Value = sourceName; } } ComponentMetaData.Version = currentVersion; } }
private void UpdateComponentData() { //Set History Looup Properties UIHelper.ComponentMetadata.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.NoMatchBehavior)].Value = _historyLookupProperties.NoMatchBehavior; UIHelper.ComponentMetadata.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.CacheType)].Value = _historyLookupProperties.CacheType; UIHelper.ComponentMetadata.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.NoMatchCacheSize)].Value = _historyLookupProperties.NoMatchCacheSize; UIHelper.ComponentMetadata.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.DefaultCacheSize)].Value = _historyLookupProperties.DefaultCacheSize; UIHelper.ComponentMetadata.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.KeyHashAlgorithm)].Value = _historyLookupProperties.KeyHashAlgorithm; switch (_historyLookupProperties.NoMatchBehavior) { case HistoryLookupTransformation.NoMatchBehavior.FailComponent: UIHelper.Input.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent; UIHelper.Input.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent; UIHelper.Input2.ErrorRowDisposition = DTSRowDisposition.RD_FailComponent; UIHelper.Input2.TruncationRowDisposition = DTSRowDisposition.RD_FailComponent; UIHelper.ComponentMetadata.UsesDispositions = false; break; case HistoryLookupTransformation.NoMatchBehavior.RedirectToErrorOutput: UIHelper.Input.ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow; UIHelper.Input.TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow; UIHelper.Input2.ErrorRowDisposition = DTSRowDisposition.RD_RedirectRow; UIHelper.Input2.TruncationRowDisposition = DTSRowDisposition.RD_RedirectRow; UIHelper.ComponentMetadata.UsesDispositions = true; break; case HistoryLookupTransformation.NoMatchBehavior.RedirectToNoMatchOutput: UIHelper.Input.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed; UIHelper.Input.TruncationRowDisposition = DTSRowDisposition.RD_NotUsed; UIHelper.Input2.ErrorRowDisposition = DTSRowDisposition.RD_NotUsed; UIHelper.Input2.TruncationRowDisposition = DTSRowDisposition.RD_NotUsed; UIHelper.ComponentMetadata.UsesDispositions = false; break; case HistoryLookupTransformation.NoMatchBehavior.IgnoreError: UIHelper.Input.ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure; UIHelper.Input.TruncationRowDisposition = DTSRowDisposition.RD_IgnoreFailure; UIHelper.Input2.ErrorRowDisposition = DTSRowDisposition.RD_IgnoreFailure; UIHelper.Input2.TruncationRowDisposition = DTSRowDisposition.RD_IgnoreFailure; UIHelper.ComponentMetadata.UsesDispositions = false; break; default: break; } //Lists to hold information about processed ooutput columns List <OutputColumnDetail> outputCols = new List <OutputColumnDetail>(); List <OutputColumnDetail> processedOutputCols = new List <OutputColumnDetail>(); List <int> oColsToRemove = new List <int>(); if (UIHelper.Output != null) { //Get Output Columns Detailsfrom Output Columns ListView foreach (ListViewItem lvi in lvOutputColumns.Items) { if (lvi.Checked) { outputCols.Add(new OutputColumnDetail((IDTSVirtualInputColumn)lvi.Tag, lvi.SubItems[1].Text, lvi.SubItems[1].Tag != null ? (int)lvi.SubItems[1].Tag : -1)); } } //Iterate through existing OutputColumns and try to update them foreach (IDTSOutputColumn oCol in UIHelper.Output.OutputColumnCollection) { OutputColumnDetail od = outputCols.Find(ocd => ocd.ID == oCol.ID); if (od != null) { outputCols.Remove(od); processedOutputCols.Add(od); oCol.Name = od.AliasName; oCol.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.SourceLineageID)].Value = od.VirtualInputColumn.LineageID; HistoryLookupTransformation.SetOutputColumnDataType(oCol, od.VirtualInputColumn); od.OutputLineageID = oCol.LineageID; } else { oColsToRemove.Add(oCol.ID); } } //Remove Not Existing Output Columns foreach (int id in oColsToRemove) { UIHelper.Output.OutputColumnCollection.RemoveObjectByID(id); } //Add new OutputColumns foreach (OutputColumnDetail od in outputCols) { processedOutputCols.Add(od); IDTSOutputColumn oCol = UIHelper.Output.OutputColumnCollection.New(); oCol.Name = od.AliasName; HistoryLookupTransformation.SetOutputColumnProperties(oCol); HistoryLookupTransformation.SetOutputColumnDataType(oCol, od.VirtualInputColumn); oCol.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.SourceLineageID)].Value = od.VirtualInputColumn.LineageID; od.OutputLineageID = oCol.LineageID; } } List <ColumnMappingDetail> mappings = new List <ColumnMappingDetail>(lvMappings.Items.Count); //Process DataInput if (UIHelper.VirtualInput != null) { //Iterete through mappings and store them in List for easy searching foreach (ListViewItem lvi in lvMappings.Items) { mappings.Add(new ColumnMappingDetail(((ConvertMetadataInputColumn)lvi.Tag).LineageID, ((ConvertMetadataInputColumn)lvi.SubItems[1].Tag).LineageID)); } //Iterate tghrough virtual input foreach (IDTSVirtualInputColumn vCol in UIHelper.VirtualInput.VirtualInputColumnCollection) { int lookupLineageID = 0; HistoryLookupTransformation.InputColumnUsageType usageType = HistoryLookupTransformation.InputColumnUsageType.None; //if column LineageID equalsto DataDateColumnLineageID in HistoryLookuProperties, then it was selected as Date Column if (vCol.LineageID == _historyLookupProperties.DataDateColumnLineageID) { usageType |= HistoryLookupTransformation.InputColumnUsageType.DateColumn; } //Try to lookup defined mapping ColumnMappingDetail cmd = mappings.Find(cm => cm.DataColumnLineageID == vCol.LineageID); //If mapping was found then it is lookup column if (cmd != null) { usageType |= HistoryLookupTransformation.InputColumnUsageType.LookupColumn; lookupLineageID = cmd.LookupColumnLineageID; } //If usageType is None then ignore column if (usageType == HistoryLookupTransformation.InputColumnUsageType.None) { UIHelper.DesignTimeComponent.SetUsageType(UIHelper.Input.ID, UIHelper.VirtualInput, vCol.LineageID, DTSUsageType.UT_IGNORED); } else { IDTSInput inp = UIHelper.ComponentMetadata.InputCollection[0]; IDTSInputColumn iCol = UIHelper.DesignTimeComponent.SetUsageType(UIHelper.Input.ID, UIHelper.VirtualInput, vCol.LineageID, DTSUsageType.UT_READONLY); HistoryLookupTransformation.SetDataInputColumnProperties(iCol); iCol.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.InputColumnUsageType)].Value = usageType; iCol.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.LookupKeyLineageID)].Value = lookupLineageID; } } } //Process HistoryLookupInput if (UIHelper.VirtualInput2 != null) { foreach (IDTSVirtualInputColumn vCol in UIHelper.VirtualInput2.VirtualInputColumnCollection) { int oColLineageID = 0; HistoryLookupTransformation.DateComparison dateComparison = HistoryLookupTransformation.DateComparison.None; HistoryLookupTransformation.InputColumnUsageType usageType = HistoryLookupTransformation.InputColumnUsageType.None; ColumnMappingDetail mapping = mappings.Find(m => m.LookupColumnLineageID == vCol.LineageID); if (mapping != null) { usageType |= HistoryLookupTransformation.InputColumnUsageType.LookupColumn; } //If it matches LooupDateFromLineageID then it is LookupFromDate if (vCol.LineageID == _historyLookupProperties.LookupDateFromLineageID) { usageType |= HistoryLookupTransformation.InputColumnUsageType.DateFromColumn; dateComparison = (HistoryLookupTransformation.DateComparison)_historyLookupProperties.LookupDateFromComparison; } //If it matches LookupDateToLineageID then it is LookupToDate if (vCol.LineageID == _historyLookupProperties.LookupDateToLineageID) { usageType |= HistoryLookupTransformation.InputColumnUsageType.DateToColumn; dateComparison = (HistoryLookupTransformation.DateComparison)_historyLookupProperties.LookupDateToComparison; } var ocol = processedOutputCols.Find(oc => oc.VirtualInputColumn.LineageID == vCol.LineageID); if (ocol != null) { usageType |= HistoryLookupTransformation.InputColumnUsageType.OutputColumn; oColLineageID = ocol.OutputLineageID; } //Uf UsageType is None then Ignore the input column if (usageType == HistoryLookupTransformation.InputColumnUsageType.None) { UIHelper.DesignTimeComponent.SetUsageType(UIHelper.Input2.ID, UIHelper.VirtualInput2, vCol.LineageID, DTSUsageType.UT_IGNORED); } else //Set the column as Input Column { IDTSInputColumn iCol = UIHelper.DesignTimeComponent.SetUsageType(UIHelper.Input2.ID, UIHelper.VirtualInput2, vCol.LineageID, DTSUsageType.UT_READONLY); HistoryLookupTransformation.SetLookupInputColumnProperties(iCol); iCol.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.InputColumnUsageType)].Value = usageType; iCol.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.OutputColumnLineageID)].Value = oColLineageID; iCol.CustomPropertyCollection[HistoryLookupTransformation.GetPropertyname(HistoryLookupTransformation.CustomProperties.DateComparison)].Value = dateComparison; } } } }
private static void HookupRawSource(MainPipe pipeline, MainPipe pipelineToReference, IDTSInputXX input, IDTSComponentMetaDataXX componentNextInPath, string sRawFilePath) { IDTSComponentMetaDataXX rawSourceComponent = pipeline.ComponentMetaDataCollection.New(); rawSourceComponent.ComponentClassID = "DTSAdapter.RawSource"; CManagedComponentWrapper inst = rawSourceComponent.Instantiate(); inst.ProvideComponentProperties(); inst.SetComponentProperty("FileName", sRawFilePath); try { inst.AcquireConnections(null); } catch (Exception ex) { throw new Exception("Unable to locate raw file at the following path which means that the previous test iteration did not run. Is the data flow task disabled?\r\n\r\n" + sRawFilePath, ex); } inst.ReinitializeMetaData(); inst.ReleaseConnections(); IDTSPathXX pathToReference = GetPathForInput(input, pipelineToReference.PathCollection); if (pathToReference == null) throw new Exception("Couldn't find path for input " + input.ID); if (pathToReference.StartPoint.IsSorted) { rawSourceComponent.OutputCollection[0].IsSorted = true; } Dictionary<string, List<IDTSVirtualInputColumnXX>> dictVirtualColumns = new Dictionary<string, List<IDTSVirtualInputColumnXX>>(); IDTSVirtualInputColumnCollectionXX colCollection = pathToReference.EndPoint.GetVirtualInput().VirtualInputColumnCollection; foreach (IDTSVirtualInputColumnXX sourceColToReference in colCollection) { string sColName = sourceColToReference.Name; if (dictVirtualColumns.ContainsKey(sColName)) { dictVirtualColumns[sColName].Add(sourceColToReference); } else { List<IDTSVirtualInputColumnXX> list = new List<IDTSVirtualInputColumnXX>(); list.Add(sourceColToReference); dictVirtualColumns.Add(sColName, list); } } Dictionary<int, int> lineageIdReplacements = new Dictionary<int, int>(); foreach (IDTSOutputColumnXX outputCol in rawSourceComponent.OutputCollection[0].OutputColumnCollection) { string sColName = outputCol.Name; if (dictVirtualColumns.ContainsKey(sColName)) { foreach (IDTSVirtualInputColumnXX sourceColToReference in dictVirtualColumns[sColName]) { if (!lineageIdReplacements.ContainsKey(sourceColToReference.LineageID)) { lineageIdReplacements.Add(sourceColToReference.LineageID, outputCol.LineageID); if (sourceColToReference.SortKeyPosition != 0) outputCol.SortKeyPosition = sourceColToReference.SortKeyPosition; } } } //foreach (IDTSVirtualInputColumnXX sourceColToReference in colCollection) //{ // if (sourceColToReference.Name == outputCol.Name && !lineageIdReplacements.ContainsKey(sourceColToReference.LineageID)) // { // lineageIdReplacements.Add(sourceColToReference.LineageID, outputCol.LineageID); // if (sourceColToReference.SortKeyPosition != 0) // outputCol.SortKeyPosition = sourceColToReference.SortKeyPosition; // } //} } if (!ContainsID(pipeline, input.ID)) { //look for an unattached input and use it instead //it's hopefully a fair assumption that if deleting the path to an input resulted in the input automatically being deleted, then there's nothing special about that input and any other can be used. //this is the case with the Union All transform for which this is a workaround bool bFoundAlt = false; foreach (IDTSInputXX inputAlt in componentNextInPath.InputCollection) { if (!inputAlt.IsAttached) { input = inputAlt; bFoundAlt = true; break; } } if (!bFoundAlt) { //create a new input string sNewInputName = "BIDS_Helper_Input_" + input.ID; input = componentNextInPath.InputCollection.New(); input.Name = sNewInputName; } } LineageIdReplacer.ReplaceLineageIDs(pipeline, lineageIdReplacements); IDTSPathXX path = pipeline.PathCollection.New(); path.AttachPathAndPropagateNotifications(rawSourceComponent.OutputCollection[0], input); rawSourceComponent.Name = RAW_SOURCE_COMPONENT_NAME_PREFIX + pathToReference.StartPoint.ID.ToString(); }
private static IDTSPathXX GetPathForInput(IDTSInputXX input, IDTSPathCollectionXX paths) { foreach (IDTSPathXX path in paths) { if (path.EndPoint.ID == input.ID) return path; } return null; }
/// <summary> /// Connects to SharePoint and gets any columns on the target /// </summary> /// <param name="input"></param> /// <param name="sharepointUrl"></param> /// <param name="listName"></param> private void CreateExternalMetaDataColumns( IDTSInput input, string sharepointUrl, string listName, string viewName, Dictionary<string, string> existingColumnData) { // No need to load if the Url is bad. if ((sharepointUrl == null) || (sharepointUrl.Length == 0)) return; // Need a list to continue if ((listName == null) || (listName.Length == 0)) return; input.ExternalMetadataColumnCollection.IsUsed = true; // If the list has changed, then we do not want any of the exisiting column data to // influence it (provides a way to actually reset the names if needed) if (input.Description != listName) { existingColumnData.Clear(); input.Description = listName; } try { List<SharePointUtility.DataObject.ColumnData> accessibleColumns = GetAccessibleSharePointColumns(sharepointUrl, listName, viewName); // Group the friendly names which are duplicated var dupeNames = from n in accessibleColumns group n by n.FriendlyName into g where g.Count() > 1 select g.Key; foreach (var column in accessibleColumns) { // Setup the primary column details from the List var dtsColumnMeta = input.ExternalMetadataColumnCollection.New(); if (existingColumnData.ContainsKey(column.Name)) { dtsColumnMeta.Name = existingColumnData[column.Name]; } else if (dupeNames.Contains(column.FriendlyName)) { // Add the more descriptive name after the duplicate names dtsColumnMeta.Name = column.FriendlyName + " (" + column.Name + ")"; } else { dtsColumnMeta.Name = column.FriendlyName; } dtsColumnMeta.Description = column.DisplayName; dtsColumnMeta.Length = 0; dtsColumnMeta.Precision = 0; dtsColumnMeta.Scale = 0; if ("Boolean|AllDayEvent|Attachments|CrossProjectLink|Recurrence".Contains(column.SharePointType)) { dtsColumnMeta.DataType = DataType.DT_BOOL; } else if (column.SharePointType == "DateTime") { dtsColumnMeta.DataType = DataType.DT_DBTIMESTAMP; } else if ("Number|Currency".Contains(column.SharePointType)) { // Max = 100,000,000,000.00000 dtsColumnMeta.DataType = DataType.DT_R8; } else if ("Counter|Integer".Contains(column.SharePointType)) { dtsColumnMeta.DataType = DataType.DT_I4; } else if ("Guid".Contains(column.SharePointType)) { dtsColumnMeta.DataType = DataType.DT_GUID; } else { if (column.MaxLength == -1) { dtsColumnMeta.DataType = DataType.DT_NTEXT; dtsColumnMeta.Length = 0; } else { dtsColumnMeta.DataType = DataType.DT_WSTR; dtsColumnMeta.Length = column.MaxLength; } } var fieldNameMeta = dtsColumnMeta.CustomPropertyCollection.New(); fieldNameMeta.Name = "Id"; fieldNameMeta.Description = "SharePoint ID"; fieldNameMeta.Value = column.Name; // Map any columns found with the same name in the input var foundCol = (from col in input.InputColumnCollection.Cast<IDTSInputColumn>() where col.Name == dtsColumnMeta.Name select col).SingleOrDefault(); if (foundCol != null) { foundCol.ExternalMetadataColumnID = dtsColumnMeta.ID; } } } catch (SharePointUnhandledException) { // Exception happened, so clear the columns, which will invalidate this object. input.ExternalMetadataColumnCollection.RemoveAll(); throw; } }
public virtual bool CheckColumnForInputValidity(IDTSVirtualInput vInput, IDTSInput input, FormInputColumn icol) { return(CheckColumnForInputValidity(icol != null ? icol.DTSVirtualColumn : null)); }