CODEWITHSUNDEEP
ssis
Grace
Grace

Reputation: 2598

SSIS with different table structures

I have a flat file source from Excel that has a structure like this:

**People**  Day1           Day2            Day3            Day4
Person1     someValue      ...
Person2     
Person3

And i would like the package to put this information in a database with standard columns 'Person', 'Day', 'Value'. Does anybody know how to do this - at the moment because the days are going along the top, the package is assuming these are seperate data columns when they are not really and the mapping is not working.

Upvotes: 1

Views: 204

Answers (2)

bobs
bobs

Reputation: 22224

I think you might want to look into the unpivot data flow transformations in SSIS. You will be able to use it to transform the data to the format you're looking for.

You can get more information here.

Upvotes: 0

Demitrius Nelon
Demitrius Nelon

Reputation: 1248

I'm not sure if this is what you're looking for, but this might work...

I created a spreadsheet with "people" in A1, "day1" in B1 "day2" in C1 Starting in A2 and going down: person1 person2 person3

Starting in B2 and going down: 1 2 3

Starting in C2 and going down: 4 5 6

then in a different column (I used G) I put the label "People" in G1, "Day" in H1 and "Value" in I1 then formulas in Row 2 G2=$A2 H2=$B$1 I2=$B2

I can then drag the formulas down to pivot for what you appear to be looking for.

It does have to be duplicated for each "day#" column, but if it's a one time load it shouldn't be too painful (depending on the number of columns). Otherwise the alternative is going to be temp tables that you can stage to do the pivot in the SSIS package.

Upvotes: 1

Related Questions