How I can create a column using script component i

问题:

I have the follow situation: I need create a project in SSIS to import some datas from csv to our system but for to do this I must read some columns, and one of this columns is "group" of values. Are values of planning horizon and this horizon can change each process, so some process can be 5 months and others 15 months.

The file (csv) will be filled with 21 columns always, but after (22, 23...) I don`t know if is 1, 2 or more columns (horizon).

And with this situation I can`t create columns in "Input and Columns" from Script Transformation Edit, I need create based on lenght of horizon.

So, my question if is possible create a column in run time, when I discovery the length of horizon.

Regards

回答1:

SSIS doesn't work that way. The number of columns is set at design time.

If you can set a reasonable upper limit - say 50 columns, you can read in the last "column" of data and then parse that, via Script Component, into those fields. Otherwise, you're looking at preprocessing the file to unpivot the variable width rows into a normalized set.

回答2:

You can do this in two different ways.

1. Add column(s) to a script component https://msdn.microsoft.com/en-us/library/ms188192.aspx

2. Add a derived column transformation and add a custom column with the appropriate expression.

回答3:

Tks for all answers. I changed my vision to create different. I use script tranformation to check :

1. how much columns I needed create;
2. open a connection and delete columns of horizon;
3. create again columns based in new horizon;

After I included a Execute Sql Task to call a procedure that to do all logic to fill the columns.

Regards,