SSIS Custom Component - Update output columns automatically based on selected input columns

Question

Hi,

I have the following code that creates output columns dynamically based on the input columns in a custom SSIS component:

// Create corresponding output columns dynamically
            IDTSOutput100 output = ComponentMetaData.OutputCollection[0];

            foreach (IDTSInputColumn100 inputcolumn in input.InputColumnCollection)
            {
                bool IsExist = false;
                foreach (IDTSOutputColumn100 OutputColumn in output.OutputColumnCollection)
                {
                    if (OutputColumn.Name == inputcolumn.Name)
                    {
                        IsExist = true;
                    }
                }

                if (!IsExist)
                {
                    IDTSOutputColumn100 outputcol = output.OutputColumnCollection.New();
                    outputcol.Name = inputcolumn.Name;
                    outputcol.Description = inputcolumn.Name;
                    outputcol.SetDataTypeProperties(inputcolumn.DataType, inputcolumn.Length, inputcolumn.Precision, inputcolumn.Scale, inputcolumn.CodePage);
                }
            }

This code works perfectly, but I want to update it so that if I unselect a column in the advanced editor of the component (in the input columns tab), then the corresponding output column is deleted too.

Does anyone know how to do this?

Thanks

Answer 1

I'd recommend browsing some of the projects on CodePlex to see how they handle it.  I grabbed the below from one of the projects there...can't remember which one, but I tend to call it in several places in my code.

 private void RemoveInvalidColumnData()
        {

            List<int> currentlineages = new List<int>();
            IDTSInput100 input = ComponentMetaData.InputCollection[0];

            for (int x = 0; x < input.InputColumnCollection.Count; x++)
            {
                IDTSInputColumn100 column = input.InputColumnCollection[x];
                currentlineages.Add(column.LineageID);
            }
            #region find columnData items that need to be removed
            List<ColumnData> removeColumnData = new List<ColumnData>();
            foreach (ColumnData d in inputColumnData)
            {
                int count = 0;
                foreach (int i in currentlineages)
                {
                    if (d.inputLineageID == i)
                        count++;
                }
                if (count != 1)
                {
                    removeColumnData.Add(d);
                }
            }
            #endregion

            #region remove invalid columnData items
            foreach (ColumnData r in removeColumnData)
            {
                //set orderIndex = orderIndex-1  to ensure the index doesn't fall outside acceptable range.
                inputColumnData.Remove(r);
                List<ColumnData> updatesRequired = new List<ColumnData>();
                updatesRequired.AddRange(inputColumnData.Where(t => t.orderIndex > r.orderIndex));
                foreach (ColumnData d in updatesRequired)
                {
                    inputColumnData.Where(t => t.inputLineageID == d.inputLineageID).FirstOrDefault().orderIndex = d.orderIndex - 1;
                    input.InputColumnCollection.GetInputColumnByLineageID(d.inputLineageID).CustomPropertyCollection[0].Value = d.orderIndex = 1;

                }

            }
            #endregion
        }

Answer 2

You can try to update your output columns in the Validate method. I think it is called when you press the OK button in the Advanced Editor .

---

SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Answer 3

COZYROC,

You should not update metadata in the Validate method.  Though it is called during the design phase (perhaps when the Advanced Editor OK button is pushed, it is also called just before execution.  Metadata should not be changed during that phase.

http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.dts.pipeline.pipelinecomponent.validate.aspx

"The Validate method is overridden by component developers to verify that the component is correctly configured. It is called repeatedly during package design, and one time before execution of the component. Components should not modify its ComponentMetaData during Validate."

Ben

MSFT

Answer 4

Hi Ben,

Can you provide specifics why you should not modify the metadata in the Validate method ? We are practicing this extensively and so far we haven't seen any issues.

---

SSIS Tasks Components Scripts Services | http://www.cozyroc.com/

Answer 5

Ben/BOL is right - you're not supposed to change metadata during a call to Validate.

Components are supposed to check the ValidateExternalMetadata flag during Validate. When the flag is true, they should check that the component's metadata is in sync with the external system. When the flag is false, they are supposed to skip this step.

If the component finds that its metadata is missing/out of date, it should return DTSValidationStatus.VS_NEEDSNEWMETADATA. When in the designer, this will trigger SSIS to call the component's ReinitializeMetaData. At runtime, I believe it just causes an error. ReinitializeMetaData is where you should define all of your component's metadata.

I believe we encourage the metadata definition to be done in a different step so the component can honor the ValidateExternalMetadata flag. Other than this, I can't think of any negative side effects of defining your metadata during Validate, so if everything seems to be working for you, then I wouldn't worry about it.

(I seem to remember us running into an issue when we made the dataflow column mapping improvements in 2012, but I think in that case the component was defining metadata when an input was attached, and that led to some weird side effects)

EDIT: Just tried it out... note that clicking the Refresh button in the Advanced Editor triggers the call to ReinitializeMetaData(). If all of your metadata is defined in Validate(), you won't get an update/refresh until you click OK and close the dialog.