August 9, 2011 at 9:43 am
hi all
i am pretty new to integration services, but would appreciate some input to see what i want to do is possible.
Basically we have a forecast by week which is updated weekly - eg the first date entry will always be the monday of the current week - and i need a routine to get this into sql
first of i created an ole db source to read an excel 2007 file i then ran it through an unpivot transformation, then a conversion and finally outputted it to a txt file
which basically done this for me
input:
stockcode 1/11/11 2/12/11
A 50 56
output:
Stockcode date qty
A 1/11/11 50
A 2/12/11 56
which was just what i wanted - and all was going well until i realised the next file will have different dates as an input and hence my mappings will be off !
can anyone see a way of doing what i want? to use the same package to read the weekly files and upload them to sql as i have in the output above
any help / advice appreciated
cheers
mal
August 9, 2011 at 11:23 am
Are the dates the column headers?
If so, you should be able to query the table using OpenRowset, and then pivot it in T-SQL, instead of in an SSIS connection.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
August 9, 2011 at 12:48 pm
Don't even mention open rowset lol I prob have questions on every forum trying to make it work on this server- it simply hangs each time and returns nothing- I can't even kill the query of when I run it!
August 9, 2011 at 12:54 pm
Can you change the format of the spreadsheet?
Or perhaps add a new worksheet which formats a copy of the data nicely, ready for import? If you do it right, it shouldn't affect the users.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply