How to edit a custom property on an external metadata column

I am using external metadata columns in a destination data component to match sharepoint fields to input columns. I want to add a flag that the user can set to specify if a column is a lookup column (indicates that the component should look for a list item with the specified input column value, and do an update if the item already exists). I added a custom boolean property to the medata column, but the property editor does not allow me to edit the column. It appears to be enabled, but when I choose a value other than the default, the editor immediately replaces the value I chose with the default.

How do I set the property so that it is editable in the property editor? Here is the code that I am using to create the property:

private void AddLookupProperty(IDTSExternalMetadataColumn90 externalColumn)

{

IDTSCustomProperty90 lookupProperty = null;

try

{

//Check to see if the property exists. If it does, don't do anything. If it doesn't, an exception will be thrown

lookupProperty = externalColumn.CustomPropertyCollection[LOOKUP_PROPERTY_NAME];

}

catch (System.Runtime.InteropServices.COMException ex)

{

//This is the exception that is thrown if the property does not exist

if (ex.ErrorCode == -1073676279)

{

lookupProperty = externalColumn.CustomPropertyCollection.New();

lookupProperty.Description = "When saving data, look for a row with this field, and if found, do an update instead of an insert.";

lookupProperty.Value = false;

lookupProperty.Name = LOOKUP_PROPERTY_NAME;

}

//Something unexpected happened

else

{

throw;

}

}

}

External metadata columns are not supposed to have editable properties. I do not completely understand your scenario. Why would you allow users of your component to be able to change this property?

Could you possibly put this property on an associated input column?

Thanks.

|||

It doesn't make sense in the input column, unfortunately. It's basically a way to create sort of a foreign key relationship to the destination, without actually creating a foreign key. When you save data to the destination, I want to check any columns in the destination that are marked as lookup columns, then if there are rows in the destination that match the value in the input buffer, I perform an update on those rows, otherwise I do an insert.

As a last resort, I was thinking that it may be possible to create another input to the destination that contained a list of input columns. Using variables may be another way to do this.

It would be cool if I could create a multi-choice variable at design time from the destination, but I don't think that is possible, so I may must use a semi-colon delimited list.

Eventually, I may need to do some custom UI to make this all easier, I suppose

|||

I see now why you need to edit external columns. You may want to submit request for this feature on the product feedback site or in the Kirk's thread on top of this forum.

For now, I would rather have a single custom property on the single input with delimited list of input columns than play with two inputs. The second input might bring you some additional headache.

Thanks.

|||

This is exactly what i needed too... I need to map an external input and a inside input that comes from an inside especified connection and table...

It's really important for me to have this done... If anyone have any idea how to add a custom input based not on a outside path but from an inside connection that would be great... using that i would be able to virtually map the lookup columns...

Best Regards,