Post reply

Loading the Data from different excel files to different excel sheets in one excel file

  • December 1, 2010 at 11:27 pm

    #231772

    Hi all,

    My requirement is like, i will have different excel files as source. i have to load these data into one excel file but into different sheets.

    Example: the data from first excel file should be loaded into first sheet and the data from second excel file should be loaded into second sheet and so on....................

    How can i accomplish this task in SSIS.........

    Will be waiting for your replies........

    Thanks in Advance

  • December 2, 2010 at 5:38 am

    This was removed by the editor as SPAM

  • December 3, 2010 at 3:04 am

    #1258268

    Do you have to use SSIS?

    Do you need to save the data in tables in a database?

    Why not use VBA in Excel to accomplish what you want?:-)

    It does not look to complicated.

    Thanks

    Gosta M

  • December 3, 2010 at 5:11 am

    #1258302

    If you don't know SSIS, the learning curve can be pretty rough. But there are a few things to remember:

    Data Flow Task

    Excel Source

    Excel Destination

    Those are the three objects you'll need. Now, you might be able to do all this in the same Data Flow Task, but you will need multiple sources and you may need multiple destinations. Or you may need multiple Data Flow Tasks with one source / destination each if SSIS tries to lock the Excel file. Unfortunately, the DFT doesn't have containers like the Control Flow, so it's either a parallel or single processing procedure inside it.

    Excel Source & Destination both give you the option of naming the Excel sheet you're pulling from. So it makes it really easy to transfer the data. The hard part is figuring out how many data flow tasks you'll need.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • December 3, 2010 at 5:11 am

    #1258303

    stewartc-708166 (12/2/2010)

    What version of excel source & destination?

    Version shouldn't matter to SSIS. Especially if SQL's been properly updated with service packs and what not. I believe MS has been updating the packs to include the ability for 2k5 SSIS to look at Office 2007, etc. anyway. And this question could be about SQL 2k8 since the 2k8 forum doesn't have a BI thread.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • December 3, 2010 at 5:19 am

    This was removed by the editor as SPAM

  • December 3, 2010 at 5:34 am

    #1258325

    stewartc-708166 (12/3/2010)

    There is a difference between the data providers to connect to Excel pre 2007 vs post 2007.

    Ah. Good to know. Thanks for clarifying.

    Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

  • December 4, 2010 at 2:47 pm

    #1258781

    Is there an article that shows how to do this for Office 2007?

  • December 5, 2010 at 12:04 am

    #1258796

    Version of excel is 2007

  • December 5, 2010 at 12:12 am

    #1258797

    our requirement is to use SSIS only. Here in the source we are able to select the different excel sheets, but when we are mapping to the destination, only one column(first column in the excel sheet) is mapping. If we select new(instead of selecting the sheet name) in the destination, we are able to create the new sheet with all the columns that are coming through the flow. In this case the first three sheets(default sheets) in the destination excel file is empty. Another problem is how to create the new sheets dynamically ........

  • December 5, 2010 at 12:16 am

    #1258798

    The number of sources will not be constant for each run.............so i think we can't use multiple sources or multiple data flow tasks.............

  • December 5, 2010 at 2:29 am

    #1258808

    "our requirement is to use SSIS only"

    Can you give me a reason for this requirement?

    I do work with SSIS and Office automation , vb.net, VBA etc.

    The case you describe almost sounds like using a bulldozer to

    cut the lawn. SSIS and Office is a bit like bringing a dog and a cat

    together.

    Gosta M

  • December 5, 2010 at 2:57 am

    #1258809

    I need to agree here, SSIS for this is using a sledgehammer as a screwdriver.

    Activeworkbook.worksheets(1).move ("Targetfile", vbLastSheet) is the syntax you want, if memory serves. It's been about 10 years, so take that with a grain of salt.

    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
    Twitter: @AnyWayDBA

  • December 5, 2010 at 11:38 pm

    #1258896

    As the Excel sources are different every then you will not be able to bind the columns correctly as a data source. Not even the most complicated data flow or code logic will account for the vaguearies of the human mind. Hey I should get a tee shirt for that. 🙂

    CodeOn

    😛

  • December 5, 2010 at 11:57 pm

    This was removed by the editor as SPAM

Viewing 15 posts - 1 through 15 (of 15 total)

You must be logged in to reply to this topic. Login to reply