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