Avatar of malraff
malraff
Flag for Ireland asked on

SQL 2005: Integration services

hi all
very much new to integration services so just looking some info to get me moving..

we create a forecast which looks forward 53 weeks.

eg
StockCode  29/08/2011      05/09/2011      12/09/2011
            A                0                      0                        0
            B              102                     94                      94


what i want to be able to do is take this excel document, process it and store it in an sql table

my thinking is Integration services is my tool..

so i created an oledb source to read excel 2007 documents and i am them faced with the process of mapping external columns to output columns, and then i realise - how do i map 29/08/2011 to an output column!?

so i look and see options in integration services such as data flow transformations..

assuming the first week will always start in the same column and always be 53 weeks long,
is there something here that will help me process what is an external column into a field value of an output column?

eg

External data                                                                 output
StockCode    29/08/2011     12/09/2011                            StockCode        week                  Qty
     A                     50                    45                                         A                29/08/2011          50
                                                                                                 A                 12/09/2011          45  

any help/advice appreciated

cheers

mal
Microsoft SQL Server 2005Microsoft SQL Server

Avatar of undefined
Last Comment
malraff

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
SThaya

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SThaya

Hi,

  Set the Unpivot Property as per the attached Screen shot  UnpivotData
malraff

ASKER
just what i am playing with !
i am outputting to a txt file but dont see how to order the columns !? is that why you have the data conversion?

05/09/2011,0.43717315096833526,622072
29/08/2011,0.47186943279121885,622072
05/09/2011,94.132521119475911,60051001
29/08/2011,101.60335612895808,60051001
05/09/2011,470.66260559737952,60051002
29/08/2011,508.01678064479046,60051002
05/09/2011,564.79512671685541,60051003
29/08/2011,609.62013677374853,60051003
05/09/2011,470.66260559737952,60051004
29/08/2011,508.01678064479046,60051004
SThaya

Hi,

  i need to  convert my data  from Unicode string to String ... thats why i used . if its required you can use .


can you please provide the sample data and sample output file?
Your help has saved me hundreds of hours of internet surfing.
fblack61
malraff

ASKER
dont know if its why you used it but it has let me sort the file :)

malraff

ASKER
malraff

ASKER
output without conversion
op4.txt
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
malraff

ASKER
hmm i see how this will cause issues

the next time i have a forecast file - the dates will be different eg rolled 1 week forward - hence all my mappings will be off!
malraff

ASKER
cheers for help SThaya - i needa rethink this i doubt !