SSIS How to load a single member with multiple rows.

user_8472579 0
Viewed 224

Trying to configure an SSIS package to load an excel file. Normally each row corresponds to a certain measure.

On this particular file however, a single row is vertically merged and corresponds to multiple measures.

Here's an example.

example

Is there anyway I could duplicate their name and memberid for each row inside of the data flow task? At the moment, I'm removing nulls, so it's only loading one measure per member.

etl ssis excel sql-server
1 Answers

I recommend using a script component to achieve that:

Assuming that you need to duplicate the first 5 columns, and the columns names are Column0 ... Column4

  1. Add a Script Component with 5 output Columns: (outColumn0 ... outColumn4)
  2. Inside the script code, create 5 variables.
  3. On each row check if the first columns are not null, if so store their values in the 5 variables.And assign the values to the output Columns.
  4. Next row, if the columns are empty then assign the variables value to the output columns. else repeat the step 3.
user_70312300
Related