Data flow component with number of output columns depending on input parameter. SSIS custom data flow component

Question

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?

Filip Popović · Accepted Answer · 2011-11-08 11:52:24Z

2

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.

answered Nov 8, 2011 at 11:52

Filip Popović

2,6553 gold badges19 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nche Over a year ago

ok, I've edited my question to show what I am trying unsucessfully at design time atm. Thanks for your help

nche · Accepted Answer · 2011-11-08 15:17:07Z

1

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);

edited Nov 8, 2011 at 15:17

answered Nov 8, 2011 at 13:15

nche

1,1023 gold badges15 silver badges32 bronze badges

Comments

piers7 · Accepted Answer · 2013-08-13 01:27:27Z

0

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

answered Aug 13, 2013 at 1:27

piers7

4,44438 silver badges49 bronze badges

Collectives™ on Stack Overflow

Data flow component with number of output columns depending on input parameter. SSIS custom data flow component

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related