May 24, 2011 at 2:01 pm
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
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
May 24, 2011 at 2:35 pm
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
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.
The absence of evidence is not evidence of absence.
Martin Rees
You can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply