CODEWITHSUNDEEP
ssis
Bigby
Bigby

Reputation: 303

SSIS Column Combining

I'm trying to combine data from multiple excel columns into one column and want to avoid copying and pasting it manually over and over each time I receive a file.

The data will come in this format:

C1    C2     C3     Date1  Date2  Date3 ... DateN
text  text2  text3   ###    ###    ###       ###
text  text2  text3   ###    ###    ###       ###

The end goal is to have all the dates in a column by themselves and to have all the values in a column next to their respective dates to load into a MSS table like so:

C1    C2     C3      Results  Date
text  text2  text3   ###      Date1
text  text2  text3   ###      Date1
text  text2  text3   ###      Date2
text  text2  text3   ###      Date2
text  text2  text3   ###      Date3
text  text2  text3   ###      Date3

So far I'm drawing a blank on what could be done to automate this.

Upvotes: 0

Views: 65

Answers (1)

Ferdipux
Ferdipux

Reputation: 5246

You can use SSIS Unpivot Transformation; it does exactly what you need. The only caveat - number of Date columns has to be fixed before you design and run SSIS package; you cannot handle dynamic number of columns in SSIS with builtin transformations.

Upvotes: 3

Related Questions