May 10, 2008 at 7:49 am
This is a new one to me and I haven't had much of a chance to play with it. Also, when I tried to use the Pivot task as a test, it complained about needing a pivot key which I couldn't find.
I have an excel sheet which has the following format:
StoreID SalesWk1 SalesWk2 SalesWk3
1 50.00 25.00 15.00
2 78.00 100.00 23.00
etc.
I've been asked if this sheet can be put into a SQL table of the following design:
StoreID, Weeknumber, Salestotal
1 1 50.00
1 2 25.00
1 3 15.00
2 1 78.00
...
etc.
Personally, I don't think this will be too terribly hard. I've only spent like 5 minutes trying to see if the PIVOT in Data Flow will work, but couldn't get it to work. The boss told me not to spend too much time on it. He just wants to know if this transformation can be done without manual manipulation of the spreadsheets. (I.E., "don't do any real work on this, Brandie").
Has anyone done this type transformation before? If so, did you have any problems or caveats I should know about? I'm just looking for verification on the task difficulty level before I tell the boss whether or not we can easily do it or the customer should fix the spreadsheets first.
Thanks in advance!
May 10, 2008 at 9:06 am
I can't offer any insight based on experience, but from your description it took about 10 minutes to get the Unpivot transformation working in SSIS.
[Excel source] --> [Unpivot] --> [Row count]
Unpivot: Selected Week1, Week2, Week3 as the input columns and StoreID as a Pass Through. For each input column the destination column was named 'Sales'. Then, named the The Pivot key value column to 'Period'.
May 12, 2008 at 5:04 am
DOH! I was looking at the wrong transformation task.
Thanks! That helps. @=)
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply