August 16, 2010 at 8:53 am
Hi All,
I'd be very grateful if you could give me some advice on the best way to export data to Excel.
I have a sql table which holds around 100k records. These are split into 10 category id's.
What I need to do is select X columns from this table where the category = '1' and then export that to excel as Category1.xls. I then need to iterate this to work through the other 9 categories so I'm left with 10 excel files.
Which in your expert opinions would be the best way to achieve this? In particular how to update a variable field after creating the first Excel output file.
Many, many thanks for any advice you can give me.
Wardy.
August 16, 2010 at 8:58 am
Wardy
If you only have ten categories then it's probably worth setting up ten separate file connections and ten separate dataflow tasks. If you don't want to do that, then you will need to derive the connection string property (if that's what it's called) of the file connection from a variable and change the value of that variable using a script task after each iteration.
John
August 16, 2010 at 9:10 am
Sorry John I forgot to mention that further categories would be added to the table so I'd prefer to create the Excel files dynamically.
August 17, 2010 at 1:23 am
Wardy
In that case, put all your file names in a table and use a For Each Loop task to loop through them.
John
August 17, 2010 at 5:29 am
Many thanks
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply