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

Improve this question
1
  • 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. Commented Dec 13, 2016 at 18:10

3 Answers 3

Reset to default
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.

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

Comments

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.

Improve this answer

1 Comment

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.
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,

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.