SSIS Custom Column Property-CodePudding

I have a general question I'm hoping someone can answer about creating custom properties for use in a Data Flow Component.

Is it possible to create custom properties for use at the column level? I can create custom properties at the component level, no problem, but that does me no good.

I want to add two properties (Encrypt and Decrypt) to the Input Column metadata.

Say I have a collection of Input columns col1, col2, col3. As a developer, I would like to set col3's Encrypt value to true so, at run time, col3 gets encrypted before being loaded into a database.

I have successfully encrypted and decrypted using a custom component. Still, I used the values "e" and "d" in the column Description and then evaluated that Description during PreExecute. I set a state object based on the value of Description and add it to a collection that is processed during ProcessInput. I don't think using the Description is a good thing to do, and that is the need for the custom properties.

CodePudding user response：

Do SSIS columns have custom properties?

The answer is yes. SSIS columns are objects that inherit the IDTSColumn130 interface. As mentioned in the SSIS documentation, this interface contains a property called CustomPropertyCollection that contains the collection of IDTSCustomProperty100 objects added to the input by the component.

Some components add some custom property to the SSIS columns such as the Derived Column Transformation. As I know, a custom component called FriendlyExpression is used to store the expression in plain text form. But, there is no way to add custom properties in the Integration Services package designer (Visual Studio).

How to add Custom Properties?

I think the only way is to create packages programmatically and edit those values or develop a custom SSIS component that adds these properties at runtime.

This is an example of reading the custom properties of a Derived Column transformation using C#. (Reference)

foreach (IDTSInputColumn localIColumn in localInput.InputColumnCollection)
{
    if (localIColumn.CustomPropertyCollection.Count == 2)
    {
        repository.AddAttribute(componentRepositoryID, localInput.Name   " ["   localIColumn.Name   "] [ID: "   localIColumn.ID.ToString()   "]", "From ["   localIColumn.UpstreamComponentName   "] "   FormatColumnDescription(localIColumn.Name, localIColumn.DataType, localIColumn.Length, localIColumn.Precision, localIColumn.Scale)   " Expression "  
            localIColumn.CustomPropertyCollection["FriendlyExpression"].Value != null ? localIColumn.CustomPropertyCollection["FriendlyExpression"].Value.ToString() : "Not Available"
            );
    }
    else
    {
        repository.AddAttribute(componentRepositoryID, localInput.Name   " ["   localIColumn.Name   "] [ID: "   localIColumn.ID.ToString()   "]", "From ["   localIColumn.UpstreamComponentName   "] "   FormatColumnDescription(localIColumn.Name, localIColumn.DataType, localIColumn.Length, localIColumn.Precision, localIColumn.Scale)   " Expression (See Ouput Column)");
    }
    //repository.AddObject(localIColumn.Name, "", ColumnEnumerator.ObjectTypes.Column, componentRepositoryID);
}

Alternatives

You can store the columns metadata within an external data source (SQL, XML, ...) and load it at runtime. Or you can use the Description property as you mentioned in your question.