Create Dinamic Excel SpreadSheets with SSIS

Hello everyone,

I have a big problem, for me :)

I have a stored procedure from SQL, the SP returns a table  with a some records.

I want to export this records in one Excel File, but each record must be in one spreadsheet.

For example:

the SP returns table

     name | job | age

      jon    | A    | 24

      anna| B     | 25

And i want the excel file to contain 2 SpreadSheet, one with firs record(jon A 24) and second SpreadSheet with(anna, B, 25)

Tommorow maybe the SP returns 3 record and the Excel file mut to contain 3 SpreadSheet

This is my problem, how can I handle this issue

Thanks everyone .

Is the data that you will get in the same format every day?  Meaning is the structure of the source data remaining the same?  It is just the number or rows that vary?

Keith

only the numers of rows is diffrent

I have found a way to do what you're after.  I'm going to put together a blog post with the details and I'll add a link here when it's done.

It's a tricky one to say the least!

Keith

thank a lot, I'm gonna wait for your post

Here's what I came up with - I hope it helps!

http://www.bidn.com/blogs/KeithHyer/bidn-blog/2512/creating-excel-worksheets-dynamically-in-ssis

Keith Hyer

Thank you very very much

It was a great  lesson for me.

Thanks again and have a nice coding:)

Hy Keith, me again:)

Today I faced with another problem for this case, something is changed in my logical approach.
If my SQL SP returns a table which contains:
Name | Age | Job
Jhon    25    IT
Jhon    34    ENG
Anna    21    HR
Anna    24    IT
Mary    20    MFG

I want the SSIS tu create one excel file with sheets for each name founded in result from SP. In this case the excel file should contain 3 sheet
1 with 2 recodrs John, 1 with 2 records Anna and 1 with 1 record Mary.

It is that possible ?

Today when I wanted to implement your solution I founded something new from my SP, the SP was returned multiple records with the same name and was corect, because maybe we have diffrent people with the same name in diffrent Job but in excel file we need to have in the same sheet the people with the same name.

Thanks for you support.

I believe that one could do that.   I'll assume that you'll stay with the same method I demonstrated previously.  So we'll still be processing "row-by-row".  The thing to be aware of with this is that as the number of rows increases - performance may be aversely affected using this method.

In this ( untested ) package, I've used the same principles as before, but where a sheet was created per row previously, I've implied that now the processes will work per name.  The "trick" to this one is adding a 2nd "nested" Foreach Loop Container inside the first one.  This is done so that after the worksheet is created for each name, the rows for that name are loaded into the variables and ( as before ) then "piped" to the Excel destination within the data flow task.

That is where I would start.  Give it a shot and let us know how it goes.  If it doesn't work out / you have issues - let me know and I'll put together another blog post expanding the original to handle multiple rows per sheet in the destination.

Good luck!

Keith

*edit:  I had missed the SQL step to get the filtered data "per name" in the picture.   Corrected above.

PS -

If you want to start thinking about a "more" set-based method, consider using the first part of the package to create the destination worksheets in your Excel destination.  Then creating a separate foreach loop to grab "sets" of data and directly piping each set to it's destination sheet.  You'd still be looping, but instead of "row-by-row" for population, it would be set-per-row - which is step in the right direction for a larger result set. 

Thanks for your the speed with which you gave the answer. Now I'm gonna  test our solution and will return to answer. For now all the best

Hi, I have one question,

I'm running on Win 7 32b, and I have instaled SQL 2005 server with SQL Server Business Intelligence Development Studio 2005 and Visual Studio 2008.

When I want to use Script Component in Properties window don't appears all options like your Script Component print screen shown in your blog post

Your Script Component :

My Script Component

Thanks

Hi Keith,

Happy to see that you already developed the code for Dynamic Excel file creation. Please help me too.

My requirement

I have 5 temp tables in database. I want to export the data from SQL tables to EXCEL file (dynamically). I am extremely new to SSIS. By doing some trial and error methods could learn a bit.But could not get that much expertise in reaching my goal.

Specifications:
Windows Server 2003 R2 Enterprise x64 bit , Service Pack 2
SQL SERVER 2008 R2 Management Studio
Visual Studio 2008 

Hy Keith,

Sorry for delay, I was in business trip for some days and now I'm back to work.

I tried to reach the goal for my problem but unfornutately I don't have a luck. Maybe something is missing in my knowledge.

I tried your ideea posted in Blog and works very well, but now with the new reasoning I don't find the way

Thank you anticipate, if you have time and goodness to update the blog for the new approch.

Thanks