December 10, 2007 at 11:59 am
Hi,
I've a problem with excel destination spread sheet.I've created a package which pulls the data from sql server and load it into excel sheet.The main thing Ive to do is I've to create different destination tables(work sheets) for different data.i.e.,The source is a sql query which pulls the data in groupwise with group by clause.So,I've to create individual work sheet for each group with that data.How it can be done.Please, advice me.
Thanks in advance.
December 10, 2007 at 12:45 pm
If you know the number of groups you have at design-time, you can use a conditional split into one data flow per group and create one "table" per data flow (when you create a table in Excel, it creates a worksheet).
If you do not know the number of groups, you need to break this into a loop. Use an ADO recordset as your loop source and select the distinct values of your grouped columns for your loop (loop once per group). Within the loop container, use an executeSQL task to create a table in your excel workbook for the group. You can do this by using expressions or a variable for your create table statement ad use the value from your loop container. Within your data flow, filter your data set and specify your destination using the variable from your loop container.
Now, that may not have made sense. If it does not, let me know and I will make a quick sample project and create some screen prints for you.
December 10, 2007 at 12:50 pm
Thank you for the reply.
Can u create a sample project for that.Because I'm a newbie in ssis.I'm struggling with that for past 4 days.
Thank you.
December 10, 2007 at 2:01 pm
The document is rather rough, but I hope it helps. This is getting into some advanced SSIS concepts so without any experience it may be hard to follow.
Take note of a couple of things - the variable scope matters. With variables evaluated as expressions they get evaluated when they come into scope so if you declare them at too high of a scope they end up set as the default value. Also, I did not do any of the work of clearing out your Excel workbook. The best way I have found for this is to have a template workbook somewhere and overwrite the workbook you write to with an empty or blank workbook using the file system task. This works much better than delete scripts.
Again, let me know if you have trouble following anything. Don't feel bad about having trouble with this - SSIS is complicated. I would recommend you get a book and take a class ASAP if you intend to use it a lot.
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply