Post reply

Dynamic columns and export to excel

  • May 24, 2011 at 2:01 pm

    #240171

    Hello All,

    I have a dynamic sql crosstab query that will return a variable number of column. I'd like to use this to export an excel file using an SSIS package.

    SSIS seems to want to know the column names in advance.

    Any creative solution to this problem would be most appreciated.

    Thanks!

  • May 24, 2011 at 2:24 pm

    #1329388

    Not easy at all in SSIS.

    Solution 1

    Put the query in Excel and bypass SSIS completely.

    Solution 2

    Check out third-party components such as this one, which might give you the dynamic columns you need.

    Solution 3

    In SSIS, use a script task to do the whole job for you.

  • May 24, 2011 at 2:35 pm

    #1329400

    Thanks Phil,

    Option 1 seems possible. It would be a little more complicated than it seems since i was hoping to also dynamically create worksheets. I may go done this road but always I always feel like I'm doing something wrong when I start VBA coding.

    Can you elaborate on 3? Would I be using .net to dynamically build excel files?

  • May 24, 2011 at 2:42 pm

    #1329408

    When you have the desired resultset in memory, use .NET to write to an Excel spreadsheet.

    For the flexibility which you require, you may need to install Excel on your server - then you can open an Excel application object and do anything you want to it in code.

    Here's[/url] a random Googled link that does something similar.

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

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