0

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

Gabriel Castro Fernandes
  • 11
  • 4
  • Short answer: No, you cannot create columns at run time in SSIS. If the number of columns in your destination changes, you will need to refresh metadata. Alternatively, you can use a COMMAND destination to run an INSERT statement or a Stored Proc for each row, but that will kill your performance. – Raj More Dec 13 '16 at 18:10

3 Answers3

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.

billinkc
  • 59,250
  • 9
  • 102
  • 159
1

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.

Techfritters
  • 113
  • 1
  • 8
  • This answered my question that led me to this post. Which was how to add a column using a script component. Just need to add a column to Output0. In the Input0_ProcessInputRow method assign the needed value to the column. I specifically had to create a string that was too complicated to do in sql. – gwgeller Dec 13 '17 at 19:40
0

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,

Gabriel Castro Fernandes
  • 11
  • 4