Post reply

Exporting Multiple Tables to dynamically changing Excel Location

  • May 31, 2011 at 10:59 pm

    #142612

    Hello

    i have 15 companies n each company have 10 tables from bucket 1 to bucket 10 , so total 150 tables,

    so i want to export this tables, 10 tables of each company to one Excel workbook with 10 Different Worksheets

    so in this case Excel File location would be remain same

    but then if i go to second companies 10 tables then excel file also would change

    so if i use export wizard it would let me do only for one company

    so that way i have to make 15 Export Wizard or SSIS package but

    i want to only make One SSIS package which can pick up the table name to Export and then put it in Source connection string and then export it to the Excel

    so i am trying to use for each loop but i dont know what variables to use or what correct methods to to this multiple export of tables to excel

    please give me any example or

    Please let me know your suggestions

  • June 1, 2011 at 2:31 am

    #1332403

    are the tables the same for each company?

  • June 1, 2011 at 10:42 am

    #1332610

    its actually like suppose there is Microsoft then microsoft company has tables from bucket 1 to bucket 10

    named bucket 1_ microsoft to bucket 10_microsoft which will go to microsoft.xls excel file

    so same thing with other companies which has tables from bucket 1 to bucket 10 n it should export to that file ,

  • June 2, 2011 at 1:50 pm

    #1333367

    Multiple sheets is kind of a pain. Actually, Excel is kind of a pain. Depending on your version of SSIS and how you set it up, it could use the ACE engine(which I consider better) or the Jet Engine(which I consider worse)...but anyways, that is probably more information then you need.

    As is typical, there are multiple ways to skin a cat. I would use the CustomProperty on the excel destination task called AccesMode. If you set this to OpenRowset From Variable you are presented with another proptery called OpenRowsetVariable, this is the sheet name (make sure to add a dollar sign to the end of any sheet name in excel, so Sheet1 becomes Sheet1$) You can then create your file in a loop and change this variable on each iteration of the loop.

  • June 2, 2011 at 8:14 pm

    #1333500

    Actually i am trying to export from SQL server to Excel

    and openrowset command is not allowed to my login in my environment so i cant use that

    so i am trying to use variable in SSIS but i dont know that much abt how to use Variable picking up the name of table n then transport it to particular excel

  • June 3, 2011 at 7:58 am

    #1333695

    You shouldn't need openrowset enabled on the server. I am talking about openrowset on the excel connection, not the database connection. The excel connection is actually a "database" connection to excel, which is why I started talking about the engine that you use. You can use openrowset on the excel file to specify the sheet name, which it seems like would fulfill your requirement.

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

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