C# SSIS Data Flow Component - Creating custom input columns

Question

I'm attempting to create my first data flow component that will take an input address run it through a PAF api and spit out the correctly formatted address and the original input columns from a file source or db table.

I need to supply the source input address in a particular format, so I need the user to map the source input columns to specific custom input columns. I was able to create custom input columns using the input.ExternalMetadataColumnCollection and this worked. However I have now been told that I need to provide the option of including all available columns in the source file as output columns now.

I have tried creating additional input columns which appear in the Column Mapping tab but I don't all all the source input columns available. The below is what I have tried so far. Any advice would be greatly appreciated. So my question is how do I add additional custom input columns as well as including all source columns?

            IDTSExternalMetadataColumnCollection100 externalInput = input.ExternalMetadataColumnCollection;
        externalInput.IsUsed = true;

        IDTSExternalMetadataColumn100 externalInputColumn = externalInput.New();
        externalInputColumn.Name = constInputAddressLineOne;
        externalInputColumn.DataType = DataType.DT_WSTR;

        externalInputColumn = externalInput.New();
        externalInputColumn.Name = constInputAddressLineTwo;
        externalInputColumn.DataType = DataType.DT_WSTR;

To restate, your component will always add N new columns to the output collection but you might need to preserve the original columns as well? — billinkc
I need to preserve the original source input columns and there values. I need to ensure that some of the input columns are mapped to custom input columns. i.e. like source input columns addressLine1, addressLine2, addressLine3 are mapped to custom input columns AddressLineOne, AddressLineTwo, AddrssLineThree. I need to do this because the address has to be in a particular format for the paf api to work correctly i.e. HouseNo before postcode. — zeencat
Going out of the component, you'd have the original addressLine1 column (lineageId 100) but you'd also have your new column AddressLineOne (lineageId 200) which is generated from your component and contains the corrected address. It's been some time since I've written custom components so I'm trying to make sure I understand the problem. — billinkc
Yes, I need the user to map the addressLine1 column to the new column AddressLineOne. I found that I could generate my own columns using the input.ExternalMetaDataColumnCollection but I lost the input columns generated by the source. And when I trie to just add some new input columns using the IDTSInputColumnCollection the source columns were lost. — zeencat
+1 simply because I couldn't find out how to specify custom input columns anywhere else on the internet! — djskinner

zeencat zeencat · Accepted Answer · 2012-09-26T09:49:18

I found a way of doing it. If you can suggest a better way I'm all ears. I just need to set my custom input columns names to read only. I used the following.

 public override void OnInputPathAttached(int inputID)
    {
        IDTSInput100 input = ComponentMetaData.InputCollection[0];

        IDTSVirtualInput100 vInput = input.GetVirtualInput();

        IDTSExternalMetadataColumnCollection100 externalColumnCollection = input.ExternalMetadataColumnCollection;
        IDTSExternalMetadataColumn100 externalColumn;                

        foreach (IDTSVirtualInputColumn100 vCol in vInput.VirtualInputColumnCollection)
        {            
            externalColumn = externalColumnCollection.New();
            externalColumn.Name = vCol.Name;
            externalColumn.DataType = vCol.DataType;               
        }
    }

C# SSIS Data Flow Component - Creating custom input columns

1 Answers