Data flow component with number of output columns

问题:

I am developing a custom data flow component. One of the custom properties I define contains a string that has information regarding the number of output columns, to be precise, it contains the names of the columns (could be 4, could be 5 but could also be 50).

The number of output columns is therefore variable. I can't figure out how to (or even if it is possible) to have a component with a number of output columns that varies based on the input.

Does anyone have an idea of how to procede? Thanks

UPDATE

Ok. So at design time I look into my custom properties and create the columns required. In which method should I do this?

I tried this in ProvideComponentProperties:

    this.fields_ = this.ComponentMetaData.CustomPropertyCollection["Fields"].Value.ToString().Split(new Char[] { ',' });

    foreach (string _field in fields_)
    {
        IDTSOutputColumn100 _outputCol = ComponentMetaData.OutputCollection[0].OutputColumnCollection.New();
        _outputCol.Name = _field;
        _outputCol.SetDataTypeProperties(DataType.DT_STR, 20, 0, 0, 1252);
    }

Basically fields_ splits up a string that's like this:

PRVT_PLACE,OPT_IMPLIED_VOLATILITY_MID,OPT_IMPLIED_VOLATILITY_YEST

And for each field I want to create an output column.

I then drop my component and set the fields_ property but I get the following message:

The name for "output column" "(93)" is blank and names cannot contain blanks.

Only one column is created with a blank name instead of the 4 for the example above...

What am I doing wrong?

回答1:

SSIS requires metadata not to change during run-time. This means that you can play with metadata as much as You want during design-time, but once package is executing: source, destination (external metadata: db, file, ...) and pipeline metadata cannot be changed. It is safety mechanism to make sure that package will do the work as expected.

Of course, there are changes in metadata that results in warning only. For instance, oledb destination will notice the change in column data type from DT_I2 to DT_I4 (smallint -> int in SQL Server) but it will work. Some other components, like SQL Server destination does not allow any changes in metadata.

Summary: it is possible to create custom component that will change the number of output columns in design-time, but it is not possible to change number of columns in run-time or change column types.

回答2:

I have finally found a solution. It may not be the exact thing to do but it suits my need.

I overrode the OnOutputPathAttached method. In this method I look into my fields_ property and add the columns based on their number.

ComponentMetaData.OutputCollection[0].OutputColumnCollection.RemoveAll();
string[] fields = this.ComponentMetaData.CustomPropertyCollection["Fields"].Value.ToString().Split(new Char[] { ',' });
foreach (string _field in fields)
{
     IDTSOutputColumn100 _outputCol = ComponentMetaData.OutputCollection[0].OutputColumnCollection.New();
     _outputCol.Name = _field;
     _outputCol.SetDataTypeProperties(DataType.DT_STR, 20, 0, 0, 1252);
}

base.OnOutputPathAttached(outputID);

回答3:

Surely you should do this by overriding ReinitializeMetaData(). You'd only do this OnOutputPathAttached if you supported varying numbers of output columns (potentially with differing output columns on each).

I think.

See http://technet.microsoft.com/en-us/library/ms135954.aspx