Post reply

Excel to DB

  • September 8, 2008 at 5:45 am

    #131166

    hi,

    I want to insert Excel data into DB.

    and an Excel file includes more than 2 sheets. (sheet1 and sheet2 as the same format )

    I use foreach Container which has a Connection to an Excel (C:\\Excel_two.xls) in Control Flow to loop excel sheets.

    After getting sheet name, this will be inserted into a valiable I created (User::NS_in_foreach)

    I have one data flow in a Foreach Container.

    This is the Data Flow below, and I have Excel Connection again.

    Excel -> Data Transformation -> SQL Server

    In this Excel,

    Ole DB Connection Manager: C:\\Excel_two.xls

    Data Access Mode: Table or view

    Excel Sheet: sheet1$

    When I execute this package, It did insert sheet1 data into DB.

    but it inserts the same data twice.

    It looks like this package ignores sheet2.

    Dont I need to set Excel in data flow since it has done in Foreach Container ??

    Should I use some other data flow Sources instead of Excel Source?

    or

    Do I miss some other settings to read sheet2$ data??

    Could someone please tell me how to fix this ??

    Im so stuck .....

    thank you

  • September 8, 2008 at 1:30 pm

    #868696

    do it through dtswizard and then save this package.

    goto start --> run--> type dtswizard --- > select source ---> select destination --- > do as per gui ---> in the end save this package to file system

    now open this saved package and see.... thanks

  • September 8, 2008 at 9:44 pm

    #868835

    hi, bang.prashant

    thank you for your reply.

    but the excel file may have more than 2 sheets. sometimes 10 sheets.

    and each sheets data must be inserted into one same table.

    If I use this wizard, I have to set sheet, so I dont think It doest fit for my case,,

    do you have any idea how to pass the variable which I use to get sheet's name to Dataflow?

    thank you,

    tomoko

  • September 9, 2008 at 7:46 am

    #869048

    refer this

    http://bi-polar23.blogspot.com/2007/09/loading-multiple-excel-files-with-ssis.html

    and let me know

  • September 9, 2008 at 7:50 pm

    #869368

    hi, bang.prashant

    Thank you so much about the site above.

    Now it worked !!!!

    I didnt set default value of the variable, so when I tried to change

    the "table" to “Table name or view name variable” and select the variable I created, I couldnt change it.

    After I set defaut value to "sheet1$", everything is ok.

    again, thank you so much !!

    tomoko

Viewing 5 posts - 1 through 4 (of 4 total)

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